AREL JOIN SUBQUERY

arel_with_joined_subquery.rb

# 
class Article     
  has_many :nomenclatures, through: :nomenclature_placements, source: :nomenclature
end

class NomenclaturePlacement < ApplicationRecord
  belongs_to :nomenclature, class_name: "Mx::Nomenclature",
                            foreign_key: :mx_nomenclature_id,
                            touch: true
  belongs_to :placement, polymorphic: true
end

# we are trying to find articles which shares most nomenclatures with current article(id: 2)
# ordered by nomenclatures intersection size and other things
# mx_articles is table for Single Table Inheritance so some extra checks needed

target_sql = <<~SQL
  SELECT * FROM "mx_articles" 
  LEFT OUTER JOIN (
    SELECT "mx_nomenclature_placements"."placement_id", 
          COUNT("mx_nomenclature_placements"."id") AS common_noms_count 
    FROM "mx_nomenclature_placements" 
    WHERE "mx_nomenclature_placements"."mx_nomenclature_id" IN (
      SELECT "mx_nomenclature_placements"."mx_nomenclature_id" 
      FROM "mx_nomenclature_placements" 
      WHERE "mx_nomenclature_placements"."placement_id" = 289 
            AND "mx_nomenclature_placements"."placement_type" = 'Mx::ArticleBase'
    ) 
    AND "mx_nomenclature_placements"."placement_type" = 'Mx::ArticleBase' 
    GROUP BY "mx_nomenclature_placements"."placement_id"
  ) n_counts 
  ON "mx_articles"."id" = "n_counts"."placement_id" 
  WHERE "mx_articles"."type" = 'Mx::Article' 
        AND "mx_articles"."id" != 289 
  ORDER BY "n_counts"."common_noms_count" DESC NULLS LAST, 
          "mx_articles"."published_at" DESC NULLS LAST, 
          "mx_articles"."mx_issue_id" DESC NULLS LAST 
  LIMIT 12         
SQL


attr_reader :article
@article = Mx::Article.first

# if you do not need ActiveRecord Relation, 
Mx::Article.joins(arel_nomenclatures_recomendations_join.join_sources)
           .order(*ordering)
           .limit(12)


# You can append anything
Mx::Article.published
           .includes(cover_placement: :file, authors: { cover_placement: :file })
           .joins(arel_nomenclatures_recomendations_join.join_sources)
           .order(*ordering)
           .limit(12)
       

def arel_nomenclatures_recomendations_join
  arel_table_articles.join(arel_common_nomenclature_counts.as("n_counts"), Arel::Nodes::OuterJoin)
                     .on(arel_table_articles[:id].eq(arel_table_n_counts[:placement_id]))
end

def ordering
  # until we go to rails 6.1 which have `.null_last` we must do string insertion (https://github.com/rails/rails/pull/38131)
  [
    arel_table_n_counts[:common_noms_count].desc.to_sql + " NULLS LAST",
    arel_table_articles[:published_at].desc.to_sql + " NULLS LAST",
    arel_table_articles[:mx_issue_id].desc.to_sql + " NULLS LAST"
  ]
end

def arel_common_nomenclature_counts
  @arel_common_nomenclature_counts ||= arel_table_placements.project(arel_table_placements[:placement_id], arel_table_placements[:id].count.as("common_noms_count"))
                                                            .group(arel_table_placements[:placement_id])
                                                            .where(arel_table_placements[:mx_nomenclature_id].in(arel_article_nomenclatures_ids)
                                                              .and(arel_table_placements[:placement_type].eq(new_placement.placement_type)))
end

def arel_article_nomenclatures_ids
  arel_table_placements.project(arel_table_placements[:mx_nomenclature_id])
                       .where(arel_table_placements[:placement_id].eq(article.id)
                          .and(arel_table_placements[:placement_type].eq(new_placement.placement_type)))
end

def arel_table_articles
  @arel_table_articles  ||= article.class.arel_table
end

def arel_table_placements
  @arel_table_placements ||= new_placement.class.arel_table
end

def arel_table_n_counts
  @arel_table_n_counts ||= Arel::Table.new(arel_common_nomenclature_counts).alias(:n_counts) # needed to use it in JOIN
end

def new_placement
  @new_placement ||= article.nomenclature_placements.build()
end