Rails includes a query with conditions that do not return all results from the left table

Question

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 

I want to find all the messages and connect them to the image table with the where clause on the images. I still want all messages to be returned, even if they don't have images matching where conditions.

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

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

According to rails guides when requested ,

If there is a request in case of this question, there were no comments for any messages, all messages will still be downloaded. Using joins (INNER JOIN), the join conditions must match, otherwise the records will not be returned.

but this is not the behavior that I see. In the case of no images, I do not get the result package from my query.

I appreciate any ideas.

Answer 1

If you added a WHERE to the columns of the right table after the LEFT JOIN , you force it to act as an INNER JOIN .

Decision

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

recommended to use instead of this compound.

and

This will create a query containing the LEFT OUTER JOIN, while the join method will generate one using the INNER JOIN function.

You may have misunderstood this sentence.

This should do what you want:

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

I am not a Ruby expert, but I deduced this from the manual here .
Not sure how to avoid single quotes, so instead I switched to dollar quoting.