14

In my scenario, I have a BlogPost model that has_and_belongs_to_many :categories. I want to filter out Blog Posts that belong to specific Categories, while still allowing for uncategorized Blog Posts (hence the need for a LEFT OUTER JOIN).

I expected this to work:

BlogPost.active.includes(:categories).where.not(categories: { id: [1, 2, 3] })

But it doesn't filter correctly because it puts the conditions at the end of the query, outside the LEFT OUTER JOIN (see this SO question / answer for the reason: SQL join: where clause vs. on clause)

This works, but it is ugly:

BlogPost.active.joins("LEFT OUTER JOIN blog_posts_categories
      ON blog_posts_categories.blog_post_id = blog_posts.id
      AND blog_posts_categories.category_id NOT IN(1, 2, 3)")

Is there an ActiveRecord-friendly way to add conditionals to the ON clause of a LEFT OUTER JOIN without manually typing it out the way I did?

Community
  • 1
  • 1
ncherro
  • 2,604
  • 2
  • 24
  • 20
  • 1
    If you want to filter out the blog posts, why are you using a LEFT JOIN or `includes`? Why not a standard JOIN (`joins`)? – PinnyM Feb 14 '14 at 18:28
  • @PinnyM a standard JOIN won't work for me because I need the query to return BlogPosts that may not be tagged with any Categories – ncherro Feb 14 '14 at 18:31
  • @ncherro it will take automatically once you mention the primary key and foreign key in association. – Sabyasachi Ghosh Jul 14 '16 at 12:39

2 Answers2

1

I think this might work.

BlogPost.active.joins(:categories).merge(Category.where.not(categories: { id: [1,2,3] })

and add it together with a scope that gets all the blogposts without a category. Another thought would be to instead use a has_many through relationship, and actually create a model for BlogPostCategory as you can add intermediate scopes on it.

Sean
  • 983
  • 5
  • 13
  • Thanks, but your first suggestion produces an INNER JOIN, which doesn't work for me. I hadn't thought about converting it to a `has_many_through` relationship. I'll reply again if that works – ncherro Feb 14 '14 at 22:44
  • 1
    There is also [`left_outer_joins`](https://apidock.com/rails/v5.2.3/ActiveRecord/QueryMethods/left_outer_joins) on is: `ActiveRecord::QueryMethods`, so maybe you're looking for `BlogPost.active.left_outer_joins(:categories).merge(Category.where.not(categories: { id: [1,2,3] })`? – Michał Jun 30 '20 at 12:52
0

I'm not thoroughly familiar with ActiveRecord syntax, but can you extend the filter to include rows where category_id IS NULL? That will satisfy your requirement.

Martin Schapendonk
  • 12,893
  • 3
  • 19
  • 24