我将如何使用Rails 3和Arel联接到子选择(作用域)? [英] How would I join to a subselect (a scope) using Rails 3 and Arel?

问题描述

我需要将一个表联接到一个select/group-by查询(包括相同的表)，我想使用Arel来实现.

I need to join a table to the a select/group-by query (which includes the same table), and I'd like to do it using Arel.

我有一个:phenotypes表，它们是has_and_belongs_to_many :genes，它们本身是has_and_belongs_to_many :orthogroups.结果，表型和正交群之间的关系是多对多的.

I have a table of :phenotypes which are has_and_belongs_to_many :genes, which are themselves has_and_belongs_to_many :orthogroups. As a result, the relationship between phenotypes and orthogroups are many-to-many.

我有两个作用域(在正交组上)，它们获取与特定表型相关的所有正交组:

I have two scopes (on Orthogroup) which get all orthogroups associated with a specific phenotype:

  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)应该返回正交组ID表以及将它们与表型相关联的基因.

Thus, doing Orthogroup.with_associated_gene_ids_for_phenotype(48291) should return a table of orthogroup IDs and the genes which link them to the phenotypes.

那东西一切正常.

问题是我想获得orthogroups.*的其余部分并将其加入第二个作用域的结果，因此基因列表基本上就像我的Orthogroup ActiveRecord模型上的一个额外字段.

The issue is that I'd like to get the rest of orthogroups.* and join it to the results of the second scope, so that the list of genes is basically like an extra field on my Orthogroup ActiveRecord model.

大概是这样的:

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

现在，该查询似乎可以正常工作了.但是我宁愿找到一种方法，将Orthogroup表直接连接到自己的作用域中以获取那些基因.

Now, that query appears to work. But I'd much rather find a way to join the Orthogroup table directly to its own scope to get those genes.

也许使用SQL会更简单，但是Arel似乎应该有一种简单的方法.我发现了几个类似的问题，但似乎都没有答案.

Perhaps it'd be simpler to use SQL, but it seems like there should be an easy way with Arel. I've found several similar questions, but none seem to have answers.

我找到的最接近的解决方案是这样:

The closest solution I've found is this:

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在两个上下文中使用表"orthogroups"，这让我感到担心；但是，对结果进行抽查表明查询是正确的.

The outputted SQL uses the table "orthogroups" in two contexts, and this worried me; however, a spot check of results suggests the query is correct.

仍然，这不是我可能希望的优雅解决方案.不用笨拙的"inner join (...)"就能做到吗?

Still, this is not the elegant solution for which I might have hoped. Is it possible to do this without the awkward "inner join (...)"?

推荐答案

只是乍一看您的代码:您是否尝试过将方法和局部变量"orthogroups"重命名为不同的名称，因为它们之间存在相同的关系名字?

Just from an initial glance at your code: have you tried renaming the method and the local variable "orthogroups" to something different as you have a relationship by the same name?

这篇关于我将如何使用Rails 3和Arel联接到子选择(作用域)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！