An efficient way to cross union in ActiveRecord / Arel (Rails 4)?

Question

I am trying to build a rather complex ActiveRecord query with the intersection of two unions. My solution works, but there is a lot of redundancy in the generated SQL. Is there a more efficient way to do this?

There are People, Cats, Dogs, Books and Albums in my universe. (This is a toy version of the actual setup of the model, which includes more complex associations and therefore even more ugly SQL.)

class Person < ActiveRecord::Base
  has_many :cats
  has_many :dogs
  has_many :books
  has_many :albums

  ...
end

I want to get all the people named Jones who own 1) a pet named Wallace and 2) a book or album released in 1996. I want to be able to build the query as follows:

Person.named("Jones").with_pet_named("Wallace").with_book_or_album_released_in(1996)

The named jones part is simple:

scope :named, ->(name) { where(name: name) }

. , , Arel ( , : http://danshultz.imtqy.com/talks/mastering_activerecord_arel/#/)

class Person
  ...

  def self.with_pet_named(name)
    with_cat_named = joins(:cats).where(cats: {name: name})
    with_dog_named = joins(:dogs).where(dogs: {name: name})
    with_cat_or_dog_named = with_cat_named.union(with_dog_named)
    from(arel_table.create_table_alias(with_cat_or_dog_named, :people))
  end

  def self.with_book_or_album_released_in(year)
    with_book_released_in = joins(:books).where(books: {release_date: year})
    with_album_released_in = joins(:albums).where(albums: {release_date: year})
    with_book_or_album_released_in = with_book_released_in.union(with_album_released_in)
    from(arel_table.create_table_alias(with_book_or_album_released_in, :people))
  end
end

, . , SQL , .

Person.named("Jones").with_pet_named("Wallace").with_book_or_album_released_in(1996).
  to_sql

SELECT "people".* FROM
  ( SELECT "people".* FROM
    ( SELECT "people".* FROM "people"
        INNER JOIN "cats"
        ON "cats"."person_id" = "people"."id"
        WHERE "people"."name" = 'Jones' AND "cats"."name" = 'Wallace'
      UNION
      SELECT "people".* FROM "people"
        INNER JOIN "dogs"
        ON "dogs"."person_id" = "people"."id"
        WHERE "people"."name" = 'Jones' AND "dogs"."name" = 'Wallace'
    ) "people"
      INNER JOIN "books"
      ON "books"."person_id" = "people"."id"
      WHERE "people"."name" = 'Jones' AND "book"."release_date" = 1996
    UNION
    SELECT "people".* FROM
    ( SELECT "people".* FROM "people"
        INNER JOIN "cats"
        ON "cats"."person_id" = "people"."id"
        WHERE "people"."name" = 'Jones' AND "cats"."name" = 'Wallace'
      UNION
      SELECT "people".* FROM "people"
        INNER JOIN "dogs"
        ON "dogs"."person_id" = "people"."id"
        WHERE "people"."name" = 'Jones' AND "dogs"."name" = 'Wallace'
    ) "people"
      INNER JOIN "albums"
      ON "albums"."person_id" = "people"."id"
      WHERE "people"."name" = 'Jones' AND "album"."release_date" = 1996
  ) "people"
  WHERE "people"."name" = 'Jones'

, - :

SELECT "people".* FROM
  ( ( SELECT "people".* FROM "people"
        INNER JOIN "cats"
        ON "cats"."person_id" = "people"."id"
        WHERE "cats"."name" = 'Wallace'
      UNION
      SELECT "people".* FROM "people"
        INNER JOIN "dogs"
        ON "dogs"."person_id" = "people"."id"
        WHERE "dogs"."name" = 'Wallace'
    ) INTERSECT
    ( SELECT "people".* FROM "people"
        INNER JOIN "books"
        ON "books"."person_id" = "people"."id"
        WHERE "book"."release_date" = 1996
      UNION
      SELECT "people".* FROM "people"
        INNER JOIN "albums"
        ON "albums"."person_id" = "people"."id"
        WHERE "album"."release_date" = 1996
    )
  ) "people"
  WHERE "people"."name" = 'Jones'

, , , . SQL- ActiveRecord Arel ?

Answer 1

db, , , SQL, .

note: "" , .

class Person
  #…
  def self.with_pet_named(name)
    with_cat_named = joins(:cats).where(cats: {name: name})
    with_dog_named = joins(:dogs).where(dogs: {name: name})
    Arel::Nodes::Union.new(with_cat_named.ast, with_dog_named.ast)
  end

  def self.with_book_or_album_released_in(year)
    with_book_released_in = joins(:books).where(books: {release_date: year})
    with_album_released_in = joins(:albums).where(albums: {release_date: year})
    Arel::Nodes::Union.new(with_book_released_in.ast, with_album_released_in.ast)
  end
  #…
end

inter = Arel::Nodes::Intersect.new(
  Person.with_pet_named('Wallace'),
  Person.with_book_or_album_released_in(1996)
)
p = Arel::Nodes::TableAlias.new(inter, :p)
Person.from(p).where(p[:name].eq('Jones')).to_sql