HABTM checking for match of latest 3

Question

Here's an interesting one for you folks...

I have a HABTM (has_and_belongs_to_many) relationship between "Dogs" and "Trips". My goal is to find two result sets: 1) Dogs that have been on at least 1 of the last 3 trips and call that @dogs_current 2) Dogs that have NOT been on any of the last 3 trips and call that @dogs_old

I found that I can find what the last 3 trips are by doing this in the Trip model:

  named_scope :last3, :order => 'date DESC', :limit => 3

But not sure how to use that list get 1 and 2. This hack works, but it seems ugly. There must be a better way! :)

@dogs_current = []
@dogs_old = []
@dogs.each do |dog| 
  if (Trip.last3 - dog.trips).size < 3 then
    @dogs_current << dog
  else
    @dogs_old << dog
  end
end

Any ideas? Thanks! -Cam

Ju Nogueira · Accepted Answer · 2010-05-06T16:34:43.033

0

class Trip < ActiveRecord::Base
   has_and_belongs_to_many :dogs
   named_scope :last3, :order => 'date DESC', :limit => 3
end

class Dog < ActiveRecord::Base
   has_and_belongs_to_many :trips
end

#IDs of last 3 trips
last_trips_ids = Trip.last3.collect(&:id)

# I'm assuming your join table is `dogs_trips`.
@dogs_current = Dog.all(:joins => :trips,
   :conditions => ["dogs_trips.trip_id IN (?)", last_trips_ids]).uniq

@dogs_old = Dog.all(:joins => :trips,
   :conditions => ["dogs_trips.trip_id NOT IN (?)", last_trips_ids]).uniq

I believe this is correct. At least worked here for me...

edited May 06 '10 at 16:34

answered May 05 '10 at 16:27

Ju Nogueira

8,435
2
29
33

This seems very close. It hit an error with dogs_trips.id which I had to change to dogs_trips.trip_id in the conditions section. The only problem with the result was that it gives me multiple values for each Dog for each trip they were on. If a dog was on all 3 of the last trips, they're returned 3 times. Perhaps I can add a "unique" param in there somewhere? – Cameron May 05 '10 at 17:20
Sorry, my mistake! I edited my answer. The duplicated results was because of the `joins`. – Ju Nogueira May 05 '10 at 17:28
After taking your last edits, it does work great for the dogs_current. However, the code looks the same for the dogs_old. Should it be something like "NOT IN (?)" for that? I tried this, but the result was mixed with dogs that WERE and WERE NOT in the last 3 trips. Any help appreciated! :) – Cameron May 06 '10 at 16:08
Yes, should be `NOT IN`, sorry for that! And I believe it should work :/ – Ju Nogueira May 06 '10 at 16:35

HABTM checking for match of latest 3

1 Answers1