ActiveRecord: can't use `pluck` after `where`

Question

I have an app that has a number of Post models, each of which belongs_to a User model. When these posts are published, a PublishedPost model is created that belongs_to the relevant Post model.

I'm trying to build an ActiveRecord query to find published posts that match a user name, then get the ids of those published posts, but I'm getting an error when I try to use the pluck method after eager-loading my associations and searching them with the where method.

Here's (part of) my controller:

class PublishedPostsController < ApplicationController

  def index
    ar_query = PublishedPost.order("published_posts.created_at DESC")

      if params[:searchQuery].present?
        search_query = params[:searchQuery]
        ar_query = ar_query.includes(:post => :user)
                           .where("users.name like ?", "%#{search_query}%")
      end

    @found_ids = ar_query.pluck(:id)

  ...

  end

end

When the pluck method is called, I get this:

ActiveRecord::StatementInvalid: Mysql2::Error: Unknown column 'users.name' in 'where clause': SELECT id FROM `published_posts`  WHERE (users.name like '%Andrew%') ORDER BY published_posts.created_at DESC

I can get the results I'm looking for with

@found_ids = ar_query.select(:id).map{|r| r.id}

but I'd rather use pluck as it seems like the cleaner way to go. I can't figure out why it's not working, though. Any ideas?

Answer 1

Your query wouldn't work as it is written, even without the pluck call.

Reason being, your WHERE clause includes literal SQL referencing the users table which Rails doesn't notice and decides to use multiple queries and join in memory ( .preload() ) instead of joining in the database level ( .eager_load() ):

SELECT * from published_posts WHERE users.name like "pattern" ORDER BY published_posts.created_at DESC
SELECT * from posts WHERE id IN ( a_list_of_all_post_ids_in_publised_posts_returned_above )
SELECT * from users WHERE id IN ( a_list_of_all_user_ids_in_posts_returned_above )

The first of the 3 queries fails and it is the error you get.

To force Rails use a JOIN here, you should either use the explicit .eager_load() instead of .includes(), or add a .references() clause.

Other than that, what @Geoff answered stands, you don't really need to .includes() here, but rather a .joins().

Answer 2

If you got here by searching "rails pluck ambiguous column", you may want to know you can just replace query.pluck(:id) with:

query.pluck("table_name.id")

Answer 3

You need to and should do joins instead of includes here.

The two functions are pretty similar except that the data from joins is not returned in the result of the query whereas the data in an includes is.

In that respect, includes and pluck are kind of antithetical. One says to return me all the data you possibly can, whereas the other says to only give me only this one little bit.

Since you only want a small amount of the data, you want to do joins. (Strangely select which also seems somewhat antithetical still works, but you would need to remove the ambiguity over id in this case.)

Try it out in the console and you'll see that includes causes a query that looks kind of like this: SELECT "posts"."id" as t0_ro, "posts"."text" as t0_r1, "users"."id" as t1_r0, "users"."name" as t1_r1 ... When you tack on a pluck statement all those crazy tx_ry columns go away and are replaced by whatever you specified.

I hope that helps, but if not maybe this RailsCast can. It is explained around the 5 minute mark.

http://railscasts.com/episodes/181-include-vs-joins