sql - Efficient way to do intersection of unions in ActiveRecord/Arel (Rails 4)? -

i trying construct pretty complex activerecord query involving intersection of 2 unions. solution works, there lots of redundancies in generated sql. there more efficient way it?

in universe there people, cats, dogs, books , albums. (this toy version of actual model setup, involves more complicated associations , uglier sql.)

class person < activerecord::base   has_many :cats   has_many :dogs   has_many :books   has_many :albums    ... end 

i want retrieve people named jones own 1) pet named wallace , 2) book or album released in 1996. want able construct query this:

person.named("jones").with_pet_named("wallace").with_book_or_album_released_in(1996) 

the "named jones" part easy:

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

the other 2 parts of query trickier. way i'm doing now, i'm using arel methods construct unions (with pattern borrowed talk: http://danshultz.github.io/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 

the problem arises when chain these scopes , methods together. correct people retrieved, sql looks less stellar.

person.named("jones").with_pet_named("wallace").with_book_or_album_released_in(1996).   to_sql 

returns

select "people".*   ( select "people".*     ( select "people".* "people"         inner join "cats"         on "cats"."person_id" = "people"."id"         "people"."name" = 'jones' , "cats"."name" = 'wallace'       union       select "people".* "people"         inner join "dogs"         on "dogs"."person_id" = "people"."id"         "people"."name" = 'jones' , "dogs"."name" = 'wallace'     ) "people"       inner join "books"       on "books"."person_id" = "people"."id"       "people"."name" = 'jones' , "book"."release_date" = 1996     union     select "people".*     ( select "people".* "people"         inner join "cats"         on "cats"."person_id" = "people"."id"         "people"."name" = 'jones' , "cats"."name" = 'wallace'       union       select "people".* "people"         inner join "dogs"         on "dogs"."person_id" = "people"."id"         "people"."name" = 'jones' , "dogs"."name" = 'wallace'     ) "people"       inner join "albums"       on "albums"."person_id" = "people"."id"       "people"."name" = 'jones' , "album"."release_date" = 1996   ) "people"   "people"."name" = 'jones' 

i think want more this:

select "people".*   ( ( select "people".* "people"         inner join "cats"         on "cats"."person_id" = "people"."id"         "cats"."name" = 'wallace'       union       select "people".* "people"         inner join "dogs"         on "dogs"."person_id" = "people"."id"         "dogs"."name" = 'wallace'     ) intersect     ( select "people".* "people"         inner join "books"         on "books"."person_id" = "people"."id"         "book"."release_date" = 1996       union       select "people".* "people"         inner join "albums"         on "albums"."person_id" = "people"."id"         "album"."release_date" = 1996     )   ) "people"   "people"."name" = 'jones' 

which, while unwieldy, @ least not redundant. there way neater sql query using activerecord , arel methods, , without changing models or associations?

without having db schema it's hard test this, think should closer sql desire.

note: 'solution' breaks method chaining available in code.

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