SQL Rails 包含条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16348333/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rails includes with conditions
提问by gusa
Is is possible in Rails > 3.2 to add conditions to the join statement generated by the includesmethod?
在 Rails > 3.2 中是否可以向includes方法生成的 join 语句添加条件？
Let's say I have two models, Person and Note. Each person has many notes and each note belong to one person. Each note has an attribute important.
假设我有两个模型，Person 和 Note。每个人有许多笔记，每个笔记属于一个人。每个音符都有一个属性important。
I want to find all the people preloading only the notes that are important. In SQL that will be:
我想找到所有预加载重要笔记的人。在 SQL 中，这将是：
SELECT *
FROM people
LEFT JOIN notes ON notes.person_id = people.id AND notes.important = 't'
In Rails, the only similar way to do that is using includes(note: joinswon't preload notes) like this:
在 Rails 中，唯一类似的方法是使用includes（注意：joins不会预加载笔记），如下所示：
Person.includes(:notes).where(:important, true)
However, that will generate the following SQL query which returns a different result set:
但是，这将生成以下 SQL 查询，该查询返回不同的结果集：
SELECT *
FROM people
LEFT JOIN notes ON notes.person_id = people.id
WHERE notes.important = 't'
Please, notice that the first resultset includes all the people and the second one only the people associated to important notes.
请注意，第一个结果集包含所有人，第二个结果集仅包含与重要笔记相关联的人。
Also notice that :conditions are deprecated since 3.1.
另请注意 :conditions 自 3.1 起已弃用。
回答by Leo Correa
According to this guide Active Record Querying
You can specify conditions on includes for eager loading like this
您可以像这样为预先加载指定包含条件
Person.includes(:notes).where("notes.important", true)
It recommends to use joinsanyway.
joins还是建议使用。
A workaround for this would be to create another association like this
对此的解决方法是创建另一个这样的关联
class Person < ActiveRecord::Base
has_many :important_notes, :class_name => 'Note',
:conditions => ['important = ?', true]
end
You would then be able to do this
然后你就可以做到这一点
Person.find(:all, include: :important_notes)
回答by Graham Slick
Rails 5+ syntax:
Rails 5+ 语法：
Person.includes(:notes).where(notes: {important: true})
Nested:
嵌套：
Person.includes(notes: [:grades]).where(notes: {important: true, grades: {important: true})
回答by Daniel Loureiro
Rails 4.2+:
Rails 4.2+：
Option A - "preload": multiple selects, uses "id IN (...)"
选项 A - “预加载”：多选，使用“id IN (...)”
class Person < ActiveRecord::Base
has_many :notes
has_many :important_notes, -> { where(important: true) }, class_name: "Note"
end
Person.preload(:important_notes)
SQL:
查询语句：
SELECT "people".* FROM "people"
SELECT "notes".* FROM "notes" WHERE "notes"."important" = ? AND "notes"."person_id" IN (1, 2)
Option B - "eager_load": one huge select, uses "LEFT JOIN"
选项 B - “eager_load”：一个巨大的选择，使用“LEFT JOIN”
class Person < ActiveRecord::Base
has_many :notes
has_many :important_notes, -> { where(important: true) }, class_name: "Note"
end
Person.eager_load(:important_notes)
SQL:
查询语句：
SELECT "people"."id" AS t0_r0, "people"."name" AS t0_r1, "people"."created_at" AS t0_r2, "people"."updated_at" AS t0_r3, "notes"."id" AS t1_r0, "notes"."person_id" AS t1_r1, "notes"."important" AS t1_r2
FROM "people"
LEFT OUTER JOIN "notes" ON "notes"."person_id" = "people"."id" AND "notes"."important" = ?
回答by juliangonzalez
I was unable to use the includes with a condition like Leo Correa's answer. Insted I neeed to use:
我无法在条件如 Leo Correa 的回答中使用包含。Insted 我需要使用：
Lead.includes(:contacts).where("contacts.primary" =>true).first
or you can also
或者你也可以
Lead.includes(:contacts).where("contacts.primary" =>true).find(8877)
This last one will retrieve the Lead with id 8877 but will only include its primary contact
最后一个将检索 ID 为 8877 的潜在客户，但仅包括其主要联系人
回答by Yuki Inoue
Same was discussed in Japanese stackoverflow. Quite hacky, but following seems to work, at least on rails 5.
在日语 stackoverflow 中也讨论了同样的问题。相当hacky，但以下似乎有效，至少在rails 5上。
Person.eager_load(:notes).joins("AND notes.important = 't'")
One important aspect is that by this way, you can write arbitrary join condition. Down side is that you cannot use placeholder so you need to be careful when using params as the join condition.
一个重要的方面是，通过这种方式，您可以编写任意连接条件。不利的一面是您不能使用占位符，因此在使用 params 作为连接条件时需要小心。
回答by Yoko
For people interested, I tried this where a record attribute was false
对于感兴趣的人，我在记录属性为 false 的情况下尝试了此操作
Lead.includes(:contacts).where("contacts.primary" => false).first
and this doesn't work. Somehow for booleans only trueworks, so I turned it around to include where.not
这不起作用。不知何故，布尔值只true适用，所以我把它转过来包括where.not
Lead.includes(:contacts).where.not("contacts.primary" => true).first
This works perfectly
这完美地工作
回答by kirstu
One way is to write the LEFT JOIN clause yourself by using joins:
一种方法是使用连接自己编写 LEFT JOIN 子句：
Person.joins('LEFT JOIN "notes" ON "notes"."person_id" = "people.id" AND "notes"."important" IS "t"')
Not pretty, though.
不过不漂亮。
