Rails包含附条件不返回左表中的所有结果查询 [英] Rails includes query with conditions not returning all results from left table

问题描述

我有两个表，文章和图片。下面是从schema.rb的相关部分：

I have two tables, posts and images. Here is the relevant section from schema.rb:

create_table "posts", force: true do |t|
  t.string   "name"
  t.string   "body"
  t.datetime "created_at"
  t.datetime "updated_at"
end

create_table "images", force: true do |t|
  t.integer  "post_id"
  t.string   "service_name"
  t.string   "service_url"
  t.datetime "created_at"
  t.datetime "updated_at"
end

我想找到的所有帖子和图像表加入，与该图像的where子句。我还是希望所有的职位返回，即使他们没有匹配的where子句中的图像。

I want to find all the posts and join it with the images table, with a where clause on the images. I still want all of the posts to return, even if they don't have images that match the where clause.

我仍然希望此查询返回所有帖子，即使有与极致的服务名称的图片：

I still expect this query to return all the posts, even if there are no images with a service name of "acme":

Post.includes(:images).where("images.service_name" => "acme")

根据<一href="http://guides.rubyonrails.org/active_record_querying.html#specifying-conditions-on-eager-loaded-associations"相对=nofollow>导轨导向上查询，

如果，在这包括查询的情况下，有任何职位没有意见，所有的职位仍然会被加载。通过使用连接（内部连接），连接条件必须匹配，否则任何记录将被退回。

If, in the case of this includes query, there were no comments for any posts, all the posts would still be loaded. By using joins (an INNER JOIN), the join conditions must match, otherwise no records will be returned.

但是这不是我看到的行为。在没有图像的情况下，我越来越没有结果包从我的查询。

but that's not the behavior I'm seeing. In the case of no images, I'm getting no results pack from my query.

我AP preciate任何想法。

I appreciate any ideas.

推荐答案

如果您在右键的表后列中添加，其中条件一个 LEFT JOIN ，你强迫它像一个 INNER JOIN 。

If you add a WHERE condition on columns of the right table after a LEFT JOIN, you force it to act like an INNER JOIN.

拉​​动前pression成条件的 LEFT JOIN 。
据<一href="http://guides.rubyonrails.org/active_record_querying.html#specifying-conditions-on-eager-loaded-associations"相对=nofollow>手册页你引用自己：

Pull the expression up into the condition to the LEFT JOIN.
According to the manual page you quoted yourself:

推荐的方法是使用联接代替。

the recommended way is to use joins instead.

和

这会生成一个查询，它包含一个LEFT OUTER JOIN，而   联接方法将使用INNER JOIN功能，而不是生成一个。

This would generate a query which contains a LEFT OUTER JOIN whereas the joins method would generate one using the INNER JOIN function instead.

您可能会误解这句话。

此的应该做你想要的：

Post.joins('LEFT OUTER JOIN images ON images.post_id = posts.id
                                  AND images.service_name = $$acme$$')

我不是Ruby的专家，但这里得出此从手动。
不知道如何逃生单引号，所以我切换到以美元报价，而不是。

I am no Ruby expert, but deriving this from the manual here.
Not sure how to escape single quotes, so I switched to dollar-quoting instead.

这篇关于Rails包含附条件不返回左表中的所有结果查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！