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?
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 JOIN
s (as they use .where
), but won't work for OUTER JOIN
s 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.