Necesito unir una tabla a una consulta select / group-by (que incluye la misma tabla), y me gustaría hacerlo usando Arel.

Tengo una mesa de:phenotypes cuales sonhas_and_belongs_to_many :genes, que son ellos mismoshas_and_belongs_to_many :orthogroups. Como resultado, la relación entre los fenotipos y los ortogrupos son muchos a muchos.

Tengo dos ámbitos (en Orthogroup) que obtienen todos los ortogrupos asociados con un fenotipo 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")
  }

Por lo tanto, haciendoOrthogroup.with_associated_gene_ids_for_phenotype(48291) debería devolver una tabla de ID de ortogrupo y los genes que los vinculan a los fenotipos.

Todo eso funciona bien.

El problema es que me gustaría obtener el resto deorthogroups.* y unirlo a los resultados del segundo alcance, de modo que la lista de genes es básicamente como un campo adicional en mi modelo Orthogroup ActiveRecord.

Aproximadamente, algo como esto:

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);

Ahora, esa consulta parece funcionar. Pero preferiría encontrar una manera de unir la tabla Orthogroup directamente a su propio alcance para obtener esos genes.

Quizás sería más simple usar SQL, pero parece que debería haber una manera fácil con Arel. He encontrado varias preguntas similares, pero ninguna parece tener respuestas.

La solución más cercana que he encontrado es 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

El SQL generado utiliza la tabla "ortogrupos" en dos contextos, y esto me preocupó; sin embargo, una comprobación puntual de resultados sugiere que la consulta es correcta.

Aún así, esta no es la solución elegante que podría haber esperado. ¿Es posible hacer esto sin la incómoda"inner join (...)"?