0

I have a model 'User' that can have multiple 'Rides' (has_many :rides). Ride's table looks like this:

# Table name: rides
#
#  id                       :integer          not null, primary key
#  user_id                  :integer
#  title                    :text
#  description              :text

I want to create a scope in the Ride model called 'is_multiple_ride' which returns rides that have a user which owns multiple rides. The query is pretty much the opposite of distinct(:user_id)

The scope I have created looks like this:

scope :multiple_live_listings, -> {
   where(user_id: Ride.group(:user_id).having("COUNT(user_id) > 1").pluck(:user_id))
}

This scope works however it's inefficient when used with multiple chained scopes because it is querying the entire Ride table for rides(in order to pluck the IDs and then use them in the scope query) rather than checking only the rides in the current query.

If there is a more efficient way to do this please help out! I am using PostgreSQL 9.3.5 and Rails 3.2.2, cheers!

Eric Walsh
  • 3,035
  • 1
  • 16
  • 21
  • Testing on my db, Rails 4.1 only creates a subquery if you omit the `pluck` – adding a `pluck`, as you do, gives me two separate queries. Does this help make it more efficient? If not, what queries precisely are you chaining to see slow/inefficient results? – eirikir Sep 30 '15 at 05:14

1 Answers1

0

Given a user who has 3 rides, if you want the scope to return all 3 rides, you'll have to get away from the rails helpers a little bit (example below). If you just want a single ride belonging to a user with multiple rides, Muntasim's solution will work.

scope :multiple_live_listings, -> {
  where('rides.user_id IN (SELECT user_id FROM rides GROUP BY user_id HAVING COUNT(user_id) > 1)')
}
Alexa Y
  • 1,854
  • 1
  • 10
  • 13