activerecord joins multiple time the same table

Question

I am doing a simple search looking like this (with 8 different params, but I will copy 2 just for the exemple)

 if params[:old] == "on"
   @events = @events.joins(:services).where(services: { name: "old" })
 end

 if params[:big] == "on"
   @events = @events.joins(:services).where(services: { name: "big" })
 end

The query works fine when I have only one params "on" and returns my events having the service in the param.

BUT if I have two params "on", even if my event has both services, it's not working anymore.

I've tested it in the console, and I can see that if I do a .joins(:services).where(services: { name: "big" }) on the element that was already joined before, it returns nothing.

I don't understand why.

The first @events (when one param) returns an active record relation with a few events inside.

Why can't I do another .joins on it?

I really don't understand what's wrong in this query and why it becomes empty as soon as it is joined twice.

Thanks a lot

Answer 1

The code you're using will translate to:

SELECT  "events".* FROM "events" INNER JOIN "services" ON "services"."event_id" = "events"."id" WHERE "services"."name" = ? AND "services"."name" = ? LIMIT ?  [["name", "big"], ["name", "old"], ["LIMIT", 11]]

This is why it returns zero record.

Here is the solution I can think of at the moment, not sure if it's the ideal, but yes it works and has been tested.

# event.rb
class Event < ApplicationRecord
  has_many :services
  has_many :old_services, -> { where(name: 'old') }, class_name: 'Service'
  has_many :big_services, -> { where(name: 'big') }, class_name: 'Service'
end


# service.rb
class Service < ApplicationRecord
  belongs_to :event
end

And your search method can be written this way:

if params[:old] == "on"
  @events = @events.joins(:old_services)
end

if params[:big] == "on"
  @events = @events.joins(:big_services)
end

@events = @events.distinct
# SELECT  DISTINCT "events".* FROM "events" INNER JOIN "services" ON "services"."event_id" = "events"."id" AND "services"."name" = ? INNER JOIN "services" "old_services_events" ON "old_services_events"."event_id" = "events"."id" AND "old_services_events"."name" = ? LIMIT ?  [["name", "big"], ["name", "old"], ["LIMIT", 11]]