rails where() sql query on array

Question

I'll explain this as best as possible. I have a query on user posts:

@selected_posts = Posts.where(:category => "Baseball")

I would like to write the following statement. Here it is in pseudo terms:

User.where(user has a post in @selected_posts)

Keep in mind that I have a many to many relationship setup so post.user is usable.

Any ideas?

/EDIT

@posts_matches = User.includes(@selected_posts).map{ |user|

      [user.company_name, user.posts.count, user.username]

    }.sort

Basically, I need the above to work so that it uses the users that HAVE posts in selected_posts and not EVERY user we have in our database.

Answer 1

Just use the following:

User.find(@selected_posts.map(&:user_id).uniq)

This takes the user ids from all the selected posts, turns them into an array, and removes any duplicates. Passing an array to user will just find all the users with matching ids. Problem solved.

To combine this with what you showed in your question, you could write:

@posts_matches = User.find(@selected_posts.map(&:user_id).uniq).map{ |user|
  [user.company_name, user.posts.size, user.username]
}

Use size to count a relation instead of count because Rails caches the size method and automatically won't look it up more than once. This is better for performance.

Not sure what you were trying to accomplish with Array#sort at the end of your query, but you could always do something like:

@users_with_posts_in_selected = User.find(@selected_posts.map(&:user_id).uniq).order('username DESC')

Answer 2

Try this:

user.posts.where("posts.category = ?", "Baseball")

Edit 1:

user.posts.where("posts.id IN (?)", @selected_posts)

Edit 2:

User.select("users.company_name, count(posts.id) userpost_count, user.username").
  joins(:posts).
  where("posts.id IN (?)", @selected_posts).
  order("users.company_name, userpost_count, user.username")

Answer 3

I don't understand your question but you can pass an array to the where method like this:

where(:id => @selected_posts.map(&:id))

and it will create a SQL query like WHERE id IN (1,2,3,4)

Answer 4

By virtue of your associations your selected posts already have the users:

@selected_posts = Posts.where("posts.category =?", "Baseball")
@users = @selected_posts.collect(&:user);

You'll probably want to remove duplicate users from @users.