2

User has many posts.

class User
  has_many :posts
end

Post belongs to user.

class Post
  belongs_to :user
end

I'm trying to find all posts that are published by users that are admin.

I've tried:

Post.where(status: 'published').includes(:user).where(users: { admin: true })

I get: 

PG::AmbiguousColumn: ERROR:  column reference "admin" is ambiguous
strivedi183
  • 4,749
  • 2
  • 31
  • 38
neo
  • 4,078
  • 4
  • 25
  • 41

2 Answers2

9

You can hand an ActiveRecord::Relation to most scope methods to build complex queries. E.g.:

Post.where(status: 'published', user: User.where(admin: true)).includes(:user)

This should generate two queries; one will include a subquery for users with the admin condition, and the other will be to load the users (eager loading).

Note that you can simplify these queries quite a bit with scopes, which keep the nitty gritty details of your columns the responsibility of the models: 

class User < ApplicationRecord
  scope :admin, -> { where(admin: true) }
end

class Post < ApplicationRecord
  scope :published, -> { where(status: 'published') }
end

# posts from admin users, with eager loaded users
Post.published.where(user: User.admin).includes(:user)

Cheers!

coreyward
  • 77,547
  • 20
  • 137
  • 166
  • To be clear, this will generate a top-level query with a subquery, not two queries. The difference being that results of the subquery will be passed over to the top-level query on the database level which is still more performant than two separate queries from the application. – Nic Nilov Jul 14 '16 at 17:15
  • @NicNilov That's incorrect. The second query is due to the eager loading, as explained. Give it a whirl. – coreyward Jul 14 '16 at 20:14
  • Of course I did. `SELECT “posts”.* FROM “posts” WHERE "posts”.”status” = $1 AND "posts"."user_id" IN (SELECT "users"."id" FROM "users" WHERE "users”.”admin” = $2) [[“status”, “published”], [“admin”, "t"]]`. Have you tried it? – Nic Nilov Jul 14 '16 at 20:20
  • @NicNilov You missed the `.includes(:user)`. – coreyward Jul 14 '16 at 20:21
  • You're right, I missed it and your example works with two separate queries. More of a reason to use `.where('users.admin = ?', true)` resulting in a `JOIN`. – Nic Nilov Jul 14 '16 at 20:23
  • @NicNilov Oy vey. That's suboptimal. You'll now retrieve user data in duplicate unless each user only has a single post, and you could do it without subverting your ORM by using `eager_load`. By using `includes`, however, you allow Rails to optimize the query for you. http://stackoverflow.com/questions/11946311/whats-the-difference-between-includes-and-preload-in-an-activerecord-query – coreyward Jul 14 '16 at 20:30
  • Thanks for the link, it was educational, appreciate that. Suboptimal or not depends on the use case or on assumptions made. If millions of ids get returned from the first query, it will be an opportunity to observe the rails process hoarding memory as well as the database cache being busted for no good reason. Join gives an option to paginate. Also, Rails does not switch `includes` behaviour on its own. It **always** does two queries unless you do specific things, such as use `references` or `where` on the association. – Nic Nilov Jul 14 '16 at 21:38
0

Adding to more detailed @coreyward's answer, if you want to go without a subquery, you can specify users condition more explicitly:

Post.where(status: 'published').
  includes(:user).
  references(:user).
  where('users.admin = ?', true)

This will generate a proper LEFT OUTER JOIN query.

Nic Nilov
  • 5,056
  • 2
  • 22
  • 37