1

Environment: Rails 3.2.22

Question:

Lets say I have the models Topics, Posts, and User.

Posts belongs to Topics User has many Posts

I want to make a query of Topic.all, but includes all posts associated to a user.

I've tried include and eager_load with a where condition for the user id, but only topics with a post which meets the condition are return.

What I want is all topics return and include only posts which match the user_id condition.

Viet
  • 3,257
  • 3
  • 28
  • 36

2 Answers2

1

After playing around with ActiveRecord I figured out how to do the query. It requires the left join as pointed out by @pshoukry, but it is missing two items.

  1. AND statement is required to include only posts for a specific user.
  2. An ActiveRecord method select needs to be appended at the end to include the fields you want.

To include all fields:

Topic.joins("LEFT JOIN posts ON posts.topic_id = topics.id AND posts.user_id = ?", user.id).select('topics.*, posts.*')

Now for the caveat. For those using Postgres and on Rails version 3.2.* there is a bug where the joined table will only return strings for ALL columns, disregarding the data type set. This issue is not present with Rails 4. There was an issue posted in the Rail's Github repo, but I can't seem to locate it. Since 3.2 is no longer supported they have no intention of fixing it.

Viet
  • 3,257
  • 3
  • 28
  • 36
0

Try using left join in your relation 

Topic.joins("LEFT JOIN posts ON topics.id = posts.topic_id")
pshoukry
  • 755
  • 5
  • 14
  • Thanks for the reply. I don't think this is the right query. I am looking to do a query of all Topics, but only include Posts matching the User's ID, or user_id in Posts. – Viet Sep 28 '15 at 23:27
  • Hi Viet yes you can add the where condition right after the query too – pshoukry Oct 04 '15 at 10:53