Rails join and HABTM relationship

Question

In my application I have model Car which:

has_and_belongs_to_many :locations

Now I'm buidling searching and I want search Car which has given locations. In my view I have:

.row
  = horizontal_simple_form_for :cars, {url: cars_path, method: :get} do |f|
     = f.input :handover_location, label: I18n.t('.handover'), collection: Location.all.map{|hl| [hl.location_address, hl.id]}
     = f.input :return_location, label: I18n.t('.return') ,collection: Location.all.map{|rl| [rl.location_address, rl.id]}
     = f.submit class: 'btn btn-success'

and in my controller I filter results based on params:

@cars = Car.joins(:locations).where("locations.id= ? AND locations.id= ?", params[:cars][:handover_location], params[:cars][:return_location])

But this code does not work properly. Maybe I shouldn't use "locations.id" twice?

score 3 · Accepted Answer · answered May 14 '14 at 12:52

I'm going to assume your join table is called cars_locations. If you wanted to do this just in sql, you could join this table to itself

... cars_locations cl1 join cars_locations cl2 on e1.car_id = e2.car_id ...

... which would make a pseudo-table for the duration of the query with this structure:

cl1.id | cl1.car_id | cl1.location_id | cl2.id | cl2.car_id | cl2.location_id

then query this for the required location_id - this will give you entries that have the same car at both locations - let's say the ids of the pickup and return locations are 123 and 456:

select distinct(cl1.car_id) from cars_locations cl1 join cars_locations cl2 on cl1.car_id = cl2.car_id where (c11.location_id = 123 and cl2.location_id = 456) or (cl1.location_id = 123 and cl2.location_id = 456);

Now we know the sql, you can wrap it into a method of the Car class

#in the Car class
def self.cars_at_both_locations(location1, location2)
  self.find_by_sql("select * from cars where id in (select distinct(cl1.car_id) from cars_locations cl1 join cars_locations cl2 on cl1.car_id = cl2.car_id where (c11.location_id = #{location1.id} and cl2.location_id = #{location2.id}) or (cl1.location_id = #{location2.id} and cl2.location_id = #{location1.id}))")
end

This isn't the most efficient method, as joins on big tables start to get very slow. A quicker method would be

def self.cars_at_both_locations(location1, location2)
  self.find(location1.car_ids & location2.car_ids)
end

in this case we use & which is the "set intersection" operator (not to be confused with &&): ie it will return only values that are in both the arrays on either side of it.

BTW, `&` is only the "set intersection" operator when it's called on an array. It actually has other functionality depending on what type of object it is called on. See http://ablogaboutcode.com/2012/01/04/the-ampersand-operator-in-ruby/ — Max Williams, May 14 '14 at 12:57
Set intersection is a very clean approach. Didn't think of this — PaReeOhNos, May 14 '14 at 13:06
Yeah, i just thought of it after i'd written all of the crappy join table stuff :) — Max Williams, May 14 '14 at 13:07

PaReeOhNos · Answer 2 · 2014-05-14T13:04:16.387

You definitely shouldn't be using the locations.id twice in the where clause, as that is physically impossible. The resulting query from that will essentially try and find the location where it's id is both the handover location, AND the return location. So in essence, what you're asking for is something like

where 1 == 1 AND 1 == 2

Which needless to say, will always return nothing.

In theory if you just change the AND for an OR you'll get what you're after. This way, you'll be asking the database for any location that has an ID or either start_location OR handover_location

UPDATE

Re-read the question. It's a little tricker than I'd thought initially, so you'll probably need to do some processing on the results. As I've said, using the AND query the way you are is asking the database for something impossible, but using the OR as I originally said, will result in cars that have EITHER or the locations, not both. This could be done in raw SQL, but using Rails this is both awkward, and frowned upon, so here's another solution.

Query the data using the OR selector I originally proposed as this will reduce the data set considerably. Then manually go through it, and reject anything that doesn't have both locations:

locations = [params[:cars][:handover_location], params[:cars][:return_location]]
@cars = Car.joins(:locations).where("locations.id IN [?]")
@cars = @cars.reject { |c|  !(locations - c.location_ids).empty? }

So what this does, is query all cars that have either of the requested locations. Then it loops through those cars, and rejects any whose list of location id's does not contain both of the supplied IDS. Now the remaining cars are available at both locations :)

Thank's for help. But I must use and beacuase this 2 conditions must be met. So how can I do it in other way? When I change this AND to OR I have two the same cars objects in my search. But I can add uniq on the end of my code and eveything is fine but I don't know is that is a good solution. — Mateusz Urbański, May 14 '14 at 12:25
You have to use an OR though. AND produces is a condition that is physically impossible to meet. If you have a location with `id` 19, it cannot equal 19 AND 27, it can be one or the other. By using the AND keyword, you're asking the database to find the location that has both of those ID's, which cannot happen. Check my edit for possible solutions as this is a bit more complex than a single query. Using uniq wouldn't actually give you what you're after as I had misread the question — PaReeOhNos, May 14 '14 at 13:04

Rails join and HABTM relationship

2 Answers2