18

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.

Improve this question
2
  • 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. Commented Aug 1, 2011 at 16:32
  • I agree, I went with your way. Commented Aug 1, 2011 at 16:35

4 Answers 4

Reset to default
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")
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

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
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
Updated the answer take a look.
I have updated the answer to fix the syntax error. Take a look.
9

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')
Improve this answer

1 Comment

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

Improve this answer

2 Comments

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

Improve this answer

2 Comments

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?
See stackoverflow.com/questions/569694/… to count duplicates in an array

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.