Rails query through 2 different associated models

Question

I'm having a little trouble trying to get a query to work the way I want it, I'm not getting all the results I'm hoping for.

I have 3 models Post, Comment and Tag. Both the posts and the comments can contain tags, and both have a has_and_belongs_to_many relationship with tags. I want to be able to get all the posts that either have a specified tag or have comments with that tag, I've been doing it in the following scope on posts like so:

scope :tag, -> (tag_id) { joins(:tags, :comment_tags).where("tags_posts.tag_id = :tag_id OR comments_tags.tag_id = :tag_id", tag_id: tag_id) }

But that doesn't return all the posts, just a subset of them, seems like its only the ones regarding the comments, this is the query it generates:

SELECT COUNT(*) FROM "posts" 
INNER JOIN "tags_posts" ON "tags_posts"."post_id" = "posts"."id" 
INNER JOIN "tags" ON "tags"."id" = "tags_posts"."tag_id" 
INNER JOIN "comments" ON "comments"."post_id" = "posts"."id" 
INNER JOIN "comments_tags" ON "comments_tags"."comment_id" = "comments"."id" 
INNER JOIN "tags" "comment_tags_posts" ON "comment_tags_posts"."id" = "comments_tags"."tag_id" 
WHERE (tags_posts.tag_id = 1 OR comments_tags.tag_id = 1)

These are the models:

class Post < ActiveRecord::Base
  has_and_belongs_to_many :tags
  has_many :comment_tags, through: :comments, source: :tags
end

class Tag < ActiveRecord::Base
  has_and_belongs_to_many :posts
  has_and_belongs_to_many :comments
end

class Comment < ActiveRecord::Base
  belongs_to :post
  has_and_belongs_to_many :tags
end

Answer 1

I'm not certain whether you've already figured this out, but in case you haven't, here is a possible solution:

In plain SQL, mainly for illustration purposes:

SELECT 
    DISTINCT posts.*
FROM
    posts
        INNER JOIN
    tags_posts ON tags_posts.post_id = posts.id
        LEFT JOIN
    comments ON comments.post_id = posts.id
        LEFT JOIN
    comments_tags ON comments_tags.comment_id = comments.id
        INNER JOIN
    tags ON (tags.id = tags_posts.tag_id OR tags.id = comments_tags.tag_id)
WHERE tags.id = 1

The primary issue in your original version was that you were making an INNER JOIN with comments and comments_tags. As a result you were probably cutting out every Post which did not have any comments. So the solution is to LEFT JOIN everything related to the comments. And then, because we are left joining, we can INNER JOIN tags on either the tag posts or comment posts.

Converting to Active Record is not very pretty, but necessary:

Post.joins("INNER JOIN posts_tags ON posts_tags.post_id = posts.id")
    .joins("LEFT JOIN comments ON comments.post_id = posts.id")
    .joins("LEFT JOIN comments_tags ON comments_tags.comment_id = comments.id")
    .joins("INNER JOIN tags ON (posts_tags.tag_id = tags.id OR comments_tags.tag_id = tags.id)")
    .where(tags: {id: 1})
    .uniq

Note the necessity of DISTINCT and uniq, as you will get duplicates because of the LEFT JOIN.

Edit

In case there's some misunderstanding of the dataset or structure, this is an example of the data I used in my test to create the above query.

posts

+----+--------------------------+
| id | text                     |
+----+--------------------------+
|  1 | Post about programming 1 |
|  2 | Post about programming 2 |
|  3 | Post about programming 3 |
|  4 | Post about cooking 1     |
|  5 | Post about cooking 2     |
+----+--------------------------+

tags

+----+-------------+
| id | name        |
+----+-------------+
|  1 | programming |
|  2 | cooking     |
|  3 | woodworking |
+----+-------------+

tags_posts

+--------+---------+
| tag_id | post_id |
+--------+---------+
|      1 |       1 |
|      1 |       2 |
|      1 |       3 |
|      2 |       4 |
|      2 |       5 |
+--------+---------+

comments

+----+----------------------------------------------+---------+
| id | comment_text                                 | post_id |
+----+----------------------------------------------+---------+
|  1 | comment - programming on programming post 1a |       1 |
|  2 | comment - programming on programming post 1b |       1 |
|  3 | comment - programming on programming post 2a |       2 |
|  4 | comment - cooking on programming post 3a     |       3 |
|  5 | comment - programming on cooking post 4a     |       4 |
|  6 | comment - cooking on cooking post 4b         |       4 |
|  7 | comment - cooking on cooking post 5a         |       5 |
+----+----------------------------------------------+---------+

comments_tags

+--------+------------+
| tag_id | comment_id |
+--------+------------+
|      1 |          1 |
|      1 |          2 |
|      1 |          3 |
|      1 |          5 |
|      2 |          4 |
|      2 |          6 |
|      2 |          7 |
+--------+------------+

If I want to search for "programming", the above query will yield this result set:

+----+--------------------------+
| id | text                     |
+----+--------------------------+
|  1 | Post about programming 1 |
|  2 | Post about programming 2 |
|  4 | Post about cooking 1     |
|  3 | Post about programming 3 |
+----+--------------------------+

since we have 3 posts specifically tagged with "programming", and one comment tagged as "programming" on a differently tagged post.

Answer 2

I am not sure to understand what's a yum, is it a post ?

From your SQL query it seems it will count only the yum that have both a specific tag AND comment with this specific tag. What you want is to count yum that have a specific tag OR comments with this specific tag.

I would do either 2 queries one to count the yum with specific tag + one to count the yum with specific commented tags and add them both to get the total or make one query with an UNION condition.

scope :yums_tagged, -> (tag_id) { joins(:tags).where("tags_yums.tag_id = :tag_id", tag_id: tag_id) }
scope :comments_taged, -> (tag_id) { joins(:comment_tags).where("comments_tags.tag_id = :tag_id", tag_id: tag_id) }