ActiveRecord .merge not working on two relations

Question

I have the following models in my app:

class Company < ActiveRecord::Base
  has_many :gallery_cards, dependent: :destroy
  has_many :photos, through: :gallery_cards
  has_many :direct_photos, class_name: 'Photo'
end

class Photo < ActiveRecord::Base
  belongs_to :gallery_card
  belongs_to :company
end

class GalleryCard < ActiveRecord::Base
  belongs_to :company
  has_many :photos
end

As you can see, Company has_many :photos, through: :gallery_cards and also has_many :photos. Photo has both a gallery_card_id and a company_id column.

What I want to be able to do is write a query like @company.photos that returns an ActiveRecord::Relation of all the company's photos. In my Company model, I currently have the method below, but that returns an array or ActiveRecord objects, rather than a relation.

def all_photos
   photos + direct_photos
end

I've tried using the .merge() method (see below), but that returns an empty relation. I think the reason is because the conditions that are used to select @company.photos and @company.direct_photos are different. This SO post explains it in more detail.

@company = Company.find(params[:id])
photos = @company.photos
direct_photos = @company.direct_photos
direct_photos.merge(photos) = []
photos.merge(direct_photos) = []

I've also tried numerous combinations of .joins and .includes without success.

this might be a candidate for a raw SQL query, but my SQL skills are rather basic.

For what it's worth, I revisited this and came up (with help) another query that grabs everything in one shot, rather than building an array of ids for a second query. This also includes the other join tables:

Photo.joins("
   LEFT OUTER JOIN companies ON photos.company_id = #{id}
   LEFT OUTER JOIN gallery_cards ON gallery_cards.id = photos.gallery_card_id
   LEFT OUTER JOIN quote_cards ON quote_cards.id = photos.quote_card_id
   LEFT OUTER JOIN team_cards ON team_cards.id = photos.team_card_id
   LEFT OUTER JOIN who_cards ON who_cards.id = photos.who_card_id
   LEFT OUTER JOIN wild_cards ON wild_cards.id = photos.wild_card_id"
   ).where("photos.company_id = #{id}
       OR gallery_cards.company_id = #{id}
       OR quote_cards.company_id = #{id}
       OR team_cards.company_id = #{id}
       OR who_cards.company_id = #{id}
       OR wild_cards.company_id = #{id}").uniq

Answer 1

ActiveRecord's merge returns the intersection not the union of the two queries – counterintuitively IMO.

To find the union, you need to use OR, for which ActiveRecord has poor built-in support. So I think you're correct that its best to write the conditions in SQL:

def all_photos
  Photo.joins("LEFT OUTER JOIN gallery_cards ON gallery_cards.id = photos.gallery_card_id")
    .where("photos.company_id = :id OR gallery_cards.company_id = :id", id: id)
end

ETA The query associates the gallery_cards to photos with a LEFT OUTER JOIN, which preserves those photo rows without associated gallery card rows. You can then query based on either photos columns or on associated gallery_cards columns – in this case, company_id from either table.

You can leverage ActiveRecord scope chaining to join and query from additional tables:

def all_photos
  Photo.joins("LEFT OUTER JOIN gallery_cards ON gallery_cards.id = photos.gallery_card_id")
    .joins("LEFT OUTER JOIN quote_cards ON quote_cards.id = photos.quote_card_id")
    .where("photos.company_id = :id OR gallery_cards.company_id = :id OR quote_cards.company_id = :id", id: id)
end