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.

While Kandada's answer is a great way to solve your problem using hand-crafted SQL, there are times when ruby is called for (such as if you want to do things with the information retrieved in steps along the way of building your query). Please see my answer below for a way to retrieve the information you're looking for using more ruby and less sql. — Andrew, Aug 01 '11 at 16:32

Harish Shetty · Answer 1 · 2011-08-01T15:58:34.773

51

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")

edited Aug 01 '11 at 15:58

answered Aug 01 '11 at 15:01

Harish Shetty

64,083
21
152
198

Well, this is a dynamic page so I would need to use the `@selected_posts` variable since the category is going to change often. Any ideas on that? Thanks for your help – stewart715 Aug 01 '11 at 15:05
thanks - this is close, but I need to generate an array of the users that have posts inside of that query (see edit) not sure how I would implement your code with what I have – stewart715 Aug 01 '11 at 15:28
Updated the answer take a look. – Harish Shetty Aug 01 '11 at 15:45
I have updated the answer to fix the syntax error. Take a look. – Harish Shetty Aug 01 '11 at 15:58

Andrew · Accepted Answer · 2011-08-01T15:47:09.367

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')

In general, I'd recommend against using `map` in situations like this because you're forcing ruby to do things that the database could do - databases are usually WAY faster. when you call `map(&:user_id)`, you're reifying the object into an activerecord object. Use `pluck(:user_id).uniq`, and activerecord will work out a nice query that the database can optimize — Peter Klipfel, Apr 17 '17 at 20:09

jhlllnd · Answer 3 · 2016-01-23T17:10:08.247

6

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)

edited Jan 23 '16 at 17:10

answered Aug 01 '11 at 15:35

jhlllnd

716
6
10

Sorry about the confusion. Basically, `@selected_posts` represents a query on `Posts` - for example, but not necessarily: `@selected_posts = Posts.where(:category => "Baseball")` I simply want to make my above @posts_matches code run on only the users that have posts selected by `@selected_posts` and not run every single user in the database. – stewart715 Aug 01 '11 at 15:38
1

Missing the `&` symbol in the map method - this initiates a block and allows the successful return of an array of IDs: `where(:id => @selected_posts.map(&:id))` – JellyFishBoy May 31 '14 at 12:54

score 1 · Answer 4 · answered Aug 01 '11 at 15:37

1

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.

answered Aug 01 '11 at 15:37

Jeff Paquette

7,089
2
31
40

Thank you. This is helpful, but I'm not sure how I would print `@users` in my view. Because right now I'm getting -> Mike, Mike, Mike, Mike...how could I get Mike (4) instead? – stewart715 Aug 01 '11 at 15:41
See http://stackoverflow.com/questions/569694/count-duplicate-elements-in-ruby-array to count duplicates in an array – Jeff Paquette Aug 01 '11 at 15:45

rails where() sql query on array

4 Answers4

Linked