ActiveRecord .joins() and use a .where() on the joined model with 2 "IN" conditions joined by an "OR"

Question

Problem

I have a Post and a Comment and I want to select posts and use a .joins() and a .where() on the Comment that contains an OR and has 2 IN conditions.

I want something that generates this:

SELECT * FROM posts
INNER JOIN comments ON comments.post_id = posts.id
WHERE comments.id IN (1,2,3) OR comments.user_id IN (4,5,6)

I would use the .or() method but it cannot take a hash.

Post.joins(Comment)
  .where({ comments: { id: [1, 2, 3] } })
  .or({ comments: { user_id: [4, 5, 6] } })      # <-- raises exception

Possible Solution

I simplified this for readability. In reality I need this to work across database adapters so I'd use Comment.connection.quote_table_name and Comment.connection.quote_column_name to correctly quote the table and column names.

ids = [1,2,3]
user_ids = [4,5,6]

clause = ""
clause += Comment.sanitize_sql_for_conditions(["comments.id IN (?)", ids]) if ids.any?
clause += " OR " if ids.any? and user_ids.any?
clause += Comment.sanitize_sql_for_conditions(["comments.user_id IN (?)", user_ids]) if user_ids.any?

Post.joins(Comment).where(clause)

Question

This works but it seems like there should be a better way... is there?

Answer 1

I assume you have a comments relation on your Post class with has_many :comments, Rails is clever enough to know that when you uses .where with the relation name, then you are thinking about the id of each comment, then you can simply write the ids.

To use the OR you must use the same class wich will be used to contruct the main query, it like a "subquery" inside the or, like follow.

Please try with the next code:

Post.joins(:comments)
  .where(comments: [1, 2, 3])
  .or(Post.where('comments.user_id IN ?', [4, 5, 6]))

RAILS OR: https://zaiste.net/rails_5_or_operator_active_record/
JOINS: https://apidock.com/rails/ActiveRecord/QueryMethods/joins

EDIT:

Due the known issue referencing on this answer, you should use raw SQL like follow.

Post.joins(:comments)
  .where('comments.id in ? OR comments.user_id in ?', [1, 2, 3], [4, 5, 6])

Each ? inside the raw sql will be replaced with the parameter passed to .where from left to right in same order.