英文:
Rails include returns filtered relations instead of all relations
问题
我使用了 includes
而不是 join
,因为它运行得更快,但是该语句返回的关联不包括我正在寻找的所有数据。它返回所有左侧数据,但只返回与查询匹配的右侧数据。希望下面的示例可以帮助澄清问题以及我试图实现的目标。
join
似乎从数据和 Rails 关联的角度来看确实做到了我要的事情,但执行了大量的查询,速度较慢。
设置和示例
class Species < ActiveRecord::Base
has_many :species_types, foreign_key: 'species_id', primary_key: "id"
end
class SpeciesTypes < ActiveRecord::Base
belongs_to :species, :foreign_key => "id", :primary_key => "species_id"
end
create_table "species", force: :cascade do |t|
t.bigint "id"
t.string "identifier"
end
create_table "species_types", force: :cascade do |t|
t.bigint "species_id"
t.bigint "type_id"
t.string "name"
end
用于可视化下面查询的表数据
Species
id | identifier |
---|---|
1 | furry |
2 | sleek |
3 | hairy |
4 | shiny |
5 | reflective |
6 | rough |
7 | rubbery |
SpeciesTypes
species_id | type_id | identifier |
---|---|---|
1 | 1 | hairy |
1 | 2 | metalic |
2 | 3 | skin |
3 | 1 | hairy |
4 | 2 | metalic |
4 | 3 | skin |
5 | 3 | skin |
5 | 3 | skin |
6 | 2 | metalic |
7 | 2 | metalic |
我知道 SpeciesTypes.type_id
,我想要获取所有具有该类型的 Species
,包括它们所有的 SpeciesTypes
。
使用 includes
`species = Species.includes(:species_types).where(:species_types => {:type_id => 1})`
这确实返回了所有具有匹配 SpeciesType
的 Species
。但是,它不是返回所有 Species
以及它们的所有 SpeciesTypes
,而是只返回与 :type_id
参数匹配的 SpeciesTypes
。因此,在这种情况下,您无法从 Species
对象中引用所有 SpeciesTypes
(species[0].species_types
)。虽然它不返回预期的内容,但它有道理为什么限制到匹配的 type_id
。
从上述查询的响应中获取的 Species
irb()$ species = Species.includes(:species_types).where(:species_types => {:type_id => 1})
irb()$ species[0].species_types
[#<SpeciesTypes:0x0000ffff9ad73490
species_id: 1,
type_id: 1,
identifier: hairy>]
我希望得到的是这样的:
irb()$ species = Species.includes(:species_types).where(:species_types => {:type_id => 1})
irb()$ species[0].species_types
[#<SpeciesTypes:0x0000ffff9ad73490
species_id: 1,
type_id: 1,
identifier: hairy>,
<SpeciesTypes:0x0000ffff9ad73490
species_id: 1,
type_id: 2,
identifier: metalic>,
]
使用 joins
这是返回我所期望的内容(使用 join
而不是 includes
),但查询速度要慢得多。我认为我可能漏掉了一些明显的东西(或者不明显但根本的东西)。
species = Species.joins(:species_types).where(:species_types => {:type_id => 3})
上面的查询返回了我期望的值,但查询速度较慢。
是否可以更新 includes
查询以返回具有匹配已知 :type_id
的所有 Species
以及所有类型?
英文:
I'm using an includes
instead of a join
because it runs faster but the statement is returning an association that doesn't include all of the data I'm looking for. It returns all of the left data, but only the right data that matches the query. Hopefully the examples below help clarify the problem and what I'm trying to achieve.
The join
does seem to do what I'm after from a data and rails association perspective but executes a ton of queries and is much slower.
Setup and examples
class Species < ActiveRecord::Base
has_many :species_types, foreign_key: 'species_id', primary_key: "id"
end
class SpeciesTypes < ActiveRecord::Base
belongs_to :species, :foreign_key => "id", :primary_key => "species_id"
end
create_table "species", force: :cascade do |t|
t.bigint "id"
t.string "identifier"
end
create_table "species_types", force: :cascade do |t|
t.bigint "species_id"
t.bigint "type_id"
t.string "name"
end
Table data to help visualize the queries below
Species
id | identifier |
---|---|
1 | furry |
2 | sleek |
3 | hairy |
4 | shiny |
5 | reflective |
6 | rough |
7 | rubbery |
SpeciesTypes
species_id | type_id | identifier |
---|---|---|
1 | 1 | hairy |
1 | 2 | metalic |
2 | 3 | skin |
3 | 1 | hairy |
4 | 2 | metalic |
4 | 3 | skin |
5 | 3 | skin |
5 | 3 | skin |
6 | 2 | metalic |
7 | 2 | metalic |
I know the SpeciesTypes.type_id
, and I'm looking to get all Species
that have that type, including all of their SpeciesTypes.
Using includes
`species = Species.includes(:species_types).where(:species_types => {:type_id => 1})`
This does return all Species with a matching SpeciesType. However, instead of returning all Species with all SpeciesType it return all Species with only the SpeciesType that match the :type_id
parameter. So, in this case you cannot reference all SpeciesTypes from the Species object (species[0].species_types
). Does not return what was expected, although it makes sense why it does limit to the matched type_id.
Response from above query for Species
irb()$ species = Species.includes(:species_types).where(:species_types => {:type_id => 1})
irb()$ species[0].species_types
[#<SpeciesTypes:0x0000ffff9ad73490
species_id: 1,
type_id: 1,
identifier: hairy>]
I'm looking for this:
irb()$ species = Species.includes(:species_types).where(:species_types => {:type_id => 1})
irb()$ species[0].species_types
[#<SpeciesTypes:0x0000ffff9ad73490
species_id: 1,
type_id: 1,
identifier: hairy>,
<SpeciesTypes:0x0000ffff9ad73490
species_id: 1,
type_id: 2,
identifier: metalic>,
]
Using joins
This is returning what I'm after (using join
instead of includes
) however the query is much much slower. I think I'm missing something obvious (or not obvious but fundamental)
species = Species.joins(:species_types).where(:species_types => {:type_id => 3})
The above returns the values that I expect but is a much slower query.
Can the includes
query be updated to return all Species with all types that match the known :type_id
?
答案1
得分: 1
以下是翻译好的部分:
"While its pretty natural to think that Species.includes(:species_types).where(:species_types => {:type_id => 3})
would load all the species and just eager load the species_types that match the where clause thats not how ActiveRecord and SQL actually works."
尽管认为Species.includes(:species_types).where(:species_types => {:type_id => 3})
会加载所有物种并仅急切加载与where
子句匹配的species_types
,但这不是ActiveRecord和SQL的实际工作方式。
"What this generates in terms of a query something like:"
关于查询生成的内容如下:
"SELECT species.name AS t0_c1, species_types.id AS t1_c1 ..."
"LEFT OUTER JOIN species_types, t1"
"ON species_types.specie_id = species.id"
"WHERE species_types.type_id = ?"
当您使用includes
并引用其他表时,它会委托给.eager_load
,这将在单个数据库查询中加载两个表。
在这里,where
子句应用于整个查询,而不仅仅是连接的关联。请记住,此查询对每个species_types
行返回一行(其中物种表的数据重复)。
如果您想要仅加载与条件匹配的记录,您需要将限制条件放入JOIN
子句中:
"SELECT species.name AS t0_c1, ..."
"LEFT OUTER JOIN species_types, t1"
"ON species_types.specie_id = species.id AND species_types.type_id = ?"
不幸的是,ActiveRecord关联不提供这样做的方法。
解决问题的最简单方法很可能是从另一端查询:
"Type.find(1)"
".specie_types.where(specie: specie)"
".joins"不是答案
您不能只用joins
替换includes
,因为它们执行的操作不同。
joins
只是向查询添加INNER LEFT JOIN
,但实际上不会从连接的表中选择任何列。它用于基于连接的表过滤关联或选择聚合,而不是防止N+1查询。
在这种情况下,最有可能的是第一个查询本身不太慢,而是在迭代specie_types
时创建了N+1查询,因为关联没有急切加载/预加载。
includes
执行的是OUTER LEFT JOIN
,将加载相关记录,具体取决于如何使用它,可以使用一次或两次查询。
英文:
While its pretty natural to think that Species.includes(:species_types).where(:species_types => {:type_id => 3})
would load all the species and just eager load the species_types that match the where clause thats not how ActiveRecord and SQL actually works.
What this generates in terms of a query something like:
SELECT species.name AS t0_c1, species_types.id AS t1_c1 ...
LEFT OUTER JOIN species_types, t1
ON species_types.specie_id = species.id
WHERE species_types.type_id = ?
When you use includes
and reference the other table it delegates to .eager_load
which loads both tables in a single database query.
The where clause here applies to the entire query and not just the joined assocation. Remember that this query returns a row for every species_types
row (with duplicate data for the species table).
If you wanted to load just the records that match the condition you would need to put the restriction into the JOIN clause:
SELECT species.name AS t0_c1, ...
LEFT OUTER JOIN species_types, t1
ON species_types.specie_id = species.id AND species_types.type_id = ?
Unfortunately ActiveRecord associations do not provide a way to do that.
The easiest solution to the problem is most likely to just query from the other end:
Type.find(1)
.specie_types.where(specie: specie)
.joins
is not the answer
You can't just replace includes
with joins
as they do very things.
joins
just adds an INNER LEFT JOIN
to the query but doesn't actually select any columns from the joined table. Its used to filter the assocation based on the joined table or to select aggregates. Not to prevent N+1 queries.
In this case it's most likely not the first query itself thats slower - rather you're creating a N+1 query when you iterate through specie_types
as the assocation is not eager loaded / preloaded.
includes
does an OUTER LEFT JOIN
and will load the assocatiated records either in one or two queries depending on how its used.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论