Preciso associar uma tabela a uma consulta select / group-by (que inclui a mesma tabela) e gostaria de fazê-lo usando o Arel.

Eu tenho uma mesa de:phenotypes que sãohas_and_belongs_to_many :genes, eles mesmoshas_and_belongs_to_many :orthogroups. Como resultado, a relação entre fenótipos e ortogrupos é de muitos para muitos.

Eu tenho dois escopos (no Orthogroup) que obtêm todos os ortogrupos associados a um fenótipo específico:

  scope :with_phenotype, lambda { |phenotype_id|
    where("observations.phenotype_id = ?", phenotype_id).
      joins("inner join orthologies on (orthologies.orthogroup_id = orthogroups.id) inner join observations on (observations.gene_id = orthologies.gene_id)")
  }

  scope :with_associated_gene_ids_for_phenotype, lambda { |phenotype_id|
    with_phenotype(phenotype_id).
      select("orthogroups.id, array_agg(distinct observations.gene_id) as associated_gene_ids").
      group("orthogroups.id")
  }

Assim, fazendoOrthogroup.with_associated_gene_ids_for_phenotype(48291) deve retornar uma tabela de IDs de grupos ortodônticos e os genes que os vinculam aos fenótipos.

Tudo isso funciona bem.

A questão é que eu gostaria de obter o restoorthogroups.* e junte-o aos resultados do segundo escopo, para que a lista de genes seja basicamente um campo extra no meu modelo Orthogroup ActiveRecord.

Aproximadamente, algo como isto:

SELECT   o1.*, o_genes.associated_gene_ids
FROM     orthogroups o1
INNER JOIN (
  SELECT    o2.id, array_agg(DISTINCT obs.gene_id) AS associated_gene_ids
  FROM orthogroups o2
  INNER JOIN orthologies ortho ON (ortho.orthogroup_id = o2.id)
  INNER JOIN observations obs ON (ortho.gene_id = obs.gene_id)
  WHERE obs.phenotype_id = ? GROUP BY o2.id
) AS o_genes
ON (o1.id = o_genes.id);

Agora, essa consulta parece funcionar. Mas prefiro encontrar uma maneira de associar a tabela Orthogroup diretamente ao seu próprio escopo para obter esses genes.

Talvez seja mais simples usar o SQL, mas parece que deve haver uma maneira fácil com o Arel. Encontrei várias perguntas semelhantes, mas nenhuma parece ter respostas.

A solução mais próxima que encontrei é esta:

def self.orthogroups phenotype_id
  Orthogroup.select("orthogroups.*, o_genes.associated_gene_ids").
    joins(Arel.sql("inner join (" + Orthogroup.with_associated_gene_ids_for_phenotype(phenotype_id).to_sql + ") AS o_genes ON (o_genes.id = orthogroups.id)"))
end

O SQL emitido usa a tabela "orthogroups" em dois contextos, e isso me preocupou; no entanto, uma verificação pontual dos resultados sugere que a consulta está correta.

Ainda assim, essa não é a solução elegante que eu poderia ter esperado. É possível fazer isso sem o embaraço"inner join (...)"?