[SOLVED] How to left outer joins with conditions

How to left outer joins with conditions

I have this relation:

class Action < ApplicationRecord
   has_many :actions_users

I tried to make a query like:

select *
from actions left outer join actions_users
on actions_users.action_id = actions.id and actions_users.user_id = 1
where actions.user_id = 1

Meanwhile, in my experience, in all of the result that I tried,

select *
from actions left outer join actions_users
on actions_users.action_id = actions.id
where actions.user_id = 1 and actions_users.user_id = 1

the join condition code and general condition are in where function.

How can I work it out?

Solution

Up to Rails 7 there is no way to specify conditions directly on an OUTER JOIN, see the documentation for Specifying Conditions on the Joined Tables. The examples shown are suitable for INNER JOINs (as they use .where), but won't work for OUTER JOINs for the same reason.

You could try to specify the OUTER JOIN manually, but will run into problems passing parameters:

Action.joins("LEFT OUTER JOIN action_users ON (action_users.id = actions.id AND action_users.user_id = :user_id")

So you will need to do parameter substitution somewhat like this:

outer_join_sanitized = ApplicationRecord.sanitize_sql([
  "LEFT OUTER JOIN action_users ON (action_users.id = actions.id AND action_users.user_id = :user_id)", 
  { user_id: 22 }
])

And you could then use Actions.joins(outer_join_sanitized). At this point you might agree that just running with raw SQL from the start is the easier way to go.