arel_complex_query.rb

<<-SQL
SELECT photos.* FROM "photos" 
INNER JOIN "subcategories" ON "photos"."subcategory_id" = "subcategories"."id" 
INNER JOIN "categories" ON "subcategories"."category_id" = "categories"."id" 
LEFT OUTER JOIN "market_category_exclusions" ON "categories"."id" = "market_category_exclusions"."category_id" 
AND "market_category_exclusions"."market" = 'cortinas' 
WHERE "market_category_exclusions"."id" IS NULL;
SQL


# em ruby Arel, dentro de um active record model
  scope :included_on_market, -> (market) {
    photos_table             = Arel::Table.new(:photos)
    categories_table         = Arel::Table.new(:categories)
    subcategories_table      = Arel::Table.new(:subcategories)
    markets_exclusions_table = Arel::Table.new(:market_category_exclusions)

    photos_table
    .join(subcategories_table)
    .on(photos_table[:subcategory_id].eq(subcategories_table[:id]))
    .join(categories_table)
    .on(subcategories_table[:category_id].eq(categories_table[:id]))
    .join(markets_exclusions_table, Arel::Nodes::OuterJoin)
    .on(
       categories_table[:id]
       .eq(markets_exclusions_table[:category_id])
       .and(markets_exclusions_table[:market].eq(market))
    )
    .join_sources
    .yield_self { |join| joins(join) }
    .where(markets_exclusions_table[:id].eq(nil))
  }