Rails 5：如何用OR组合不兼容的关联表查询语句

Great question! The pain point you're hitting is super common with ActiveRecord's or method—it's strict about matching query structures (like joins, selects, etc.) on both sides of the OR. Your current id IN (...) workaround works, but let's look at cleaner, more efficient options that scale better when you have 10+ cross-table conditions.

Why Your Current Approach Works (But Has Limits) #

Your existing code:

Project.where(projects[:created_at].gt(6.months.ago)).or(Project.where(id: Project.joins(:payments).select(:id)))

Generates an IN subquery, which is valid, but can get slow if the subquery returns a huge list of IDs. Plus, stacking 10+ such subqueries can make your SQL messy and harder to optimize.

Option 1: Use Arel to Build EXISTS Conditions (Recommended) #

Instead of fetching IDs for an IN clause, use Arel to create an EXISTS subquery. This is more efficient because databases use semi-joins for EXISTS—they stop searching as soon as they find a matching record, rather than fetching all IDs first.

Here's how to rewrite your example:

projects = Project.arel_table
payments = Payment.arel_table

# Build an EXISTS condition: "there exists a payment linked to this project"
has_payments = projects.create_on(
  payments[:project_id].eq(projects[:id])
).exists

# Combine your original condition with the EXISTS check using Arel's OR
Project.where(
  projects[:created_at].gt(6.months.ago).or(has_payments)
)

This generates SQL like:

SELECT "projects".* FROM "projects" 
WHERE ("projects"."created_at" > '2019-08-19 09:11:22.064298' 
       OR EXISTS (SELECT 1 FROM "payments" WHERE "payments"."project_id" = "projects"."id"))

For multiple cross-table conditions, you can chain more EXISTS checks with or()—clean, readable, and efficient.

Option 2: Join All Required Tables (If Conditions Need Columns From Joins) #

If your cross-table conditions need to filter on columns from the associated tables (not just existence), you can join all necessary tables first, then combine conditions with OR. Just remember to add distinct to avoid duplicate project records from multiple joins.

Example with two cross-table conditions:

projects = Project.arel_table
payments = Payment.arel_table
tasks = Task.arel_table # Assume another has_many :tasks association

Project.joins(:payments, :tasks)
       .where(
         projects[:created_at].gt(6.months.ago)
         .or(payments[:amount].gt(1000)) # Filter on payment amount
         .or(tasks[:status].eq('completed')) # Filter on task status
       )
       .distinct

This works because the query structure is consistent (all joins are applied to both sides of the OR), and distinct ensures you get unique projects even if they have multiple payments/tasks.

Option 3: Use CTEs for Complex Multi-Condition Scenarios #

If you have a ton of disjointed cross-table conditions, Common Table Expressions (CTEs) can help organize your query. You can define separate CTEs for each condition, then union their IDs to get the final set of projects.

Example:

projects = Project.arel_table

# Define CTEs for each condition
cte_recent = Project.where(projects[:created_at].gt(6.months.ago)).select(:id).as('recent_projects')
cte_has_payments = Project.joins(:payments).select(:id).as('paid_projects')
cte_completed_tasks = Project.joins(:tasks).where(tasks[:status].eq('completed')).select(:id).as('completed_task_projects')

# Union all CTE IDs and join back to projects
Project.from("(SELECT id FROM #{cte_recent.to_sql} 
               UNION 
               SELECT id FROM #{cte_has_payments.to_sql} 
               UNION 
               SELECT id FROM #{cte_completed_tasks.to_sql}) AS filtered_ids")
       .joins('INNER JOIN projects ON projects.id = filtered_ids.id')

CTEs make complex logic easier to read, and databases can optimize each CTE independently. This is a good choice if your conditions are too varied to fit into a single join or set of EXISTS checks.

Why include Is a Bad Idea Here #

You mentioned include has too much overhead—and you're right! include is for eager loading associated records (to avoid N+1 queries), but you don't need the actual payment/task data here. You just need to check existence or filter on their columns, so include adds unnecessary database calls and memory usage.

Final Recommendation #

For most cases, Option 1 (Arel EXISTS conditions) is the best balance of readability, performance, and scalability. It avoids the pitfalls of IN subqueries and keeps your code clean even with multiple cross-table conditions.

内容的提问来源于stack exchange，提问作者arunt