Мне нужно присоединить таблицу к запросу select/group-by (который включает ту же таблицу), и я хотел бы сделать это с помощью Arel.
У меня есть таблица :phenotypes
, которые являются has_and_belongs_to_many :genes
, которые сами являются has_and_belongs_to_many :orthogroups
. В результате отношения между фенотипами и ортогруппами являются многими ко многим.
У меня есть две области (в ортогруппе), которые получают все ортогруппы, связанные с определенным фенотипом:
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")
}
Таким образом, выполнение Orthogroup.with_associated_gene_ids_for_phenotype(48291)
должно вернуть таблицу идентификаторов ортогрупп и генов, которые связывают их с фенотипами.
Все это работает нормально.
Проблема в том, что я хотел бы получить остальную часть orthogroups.*
и соединить ее с результатами второго осциллографа, чтобы список генов был похож на дополнительное поле в моей модели Orthogroup ActiveRecord.
Примерно вот так:
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);
Теперь этот запрос работает. Но я бы предпочел найти способ присоединить таблицу ортогрупп непосредственно к ее собственной области, чтобы получить эти гены.
Возможно, было бы проще использовать SQL, но похоже, что с Arel должен быть простой способ. Я нашел несколько похожих вопросов, но ни один из них, похоже, не имеет ответов.
Самое близкое решение, которое я нашел, это:
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
Выведенный SQL использует таблицу «ортогруппы» в двух контекстах, и это беспокоило меня; однако выборочная проверка результатов показывает, что запрос правильный.
Тем не менее, это не то элегантное решение, на которое я мог бы надеяться. Можно ли это сделать без неуклюжего "inner join (...)"
?