How to left outer joins with conditions

Question

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?

Promisee

the rails is latest version – Promisee

For a better solution, see In Rails 6, how do I add a condition to a left-outer-joins finder? – Blankly

Answer 1

Because you have a general where condition, you can use includes. This will generate a LEFT OUTER JOIN query:

Action.includes(:actions_users).where(actions_users: { user_id: true })

Or if you are using Rails 5+, Active Record provides a finder method left_outer_joins:

Action.left_outer_joins(:actions_users).where(actions_users: { user_id: true })

Anallese

Just note that this doesn't add the condition to the LEFT JOIN, it adds it to the WHERE. They can sometimes produce very different results. In order to add the conditional to the LEFT JOIN I had to write a custom JOIN with .joins( "..." ). I also found another way where you can add it to the has_many association but I found that less intuitive. – Meagre

I downvoted because as the person above noted, it adds the condition to the where and not the AND – Lawrencelawrencium

Answer 2

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.

Adna

Answer 3

You can pass a string in join query and use the rails table naming conventions for this.

Action.joins("left outer join action_users on (action_users.id = actions.id and action_users.id = 1")).where('action_users.user_id = ? ', 1)

Acetylate

Very bad example, never pass values into query text itself. – Loginov

The joins should indeed use ? parameter substitution and the where afterwards needlessly destroys the desired effect of the outer join, but this is still the somewhat best answer: At least up to Rails 7 there are no Rails methods using hash parameters that would be used in the on of a join. – Adna

Answer 4

There is still no clean way to do that in Rails 7.1, but you could use ARel to make it work.

The first example to work directly in ActiveRelation, without switching to ARel:

  actions = Action.arel_table
  action_users = ActionUser.arel_table
  Action
    .joins(
      actions.create_join(
        action_users,
        actions.create_on(
          action_users[:action_id]
            .eq(actions[:id])
            .and(
              action_users[:user_id]
                .eq(user_id)
                .and(
                  actions[:user_id].eq(user_id)
                )
            )
        )
      ),
      Arel::Nodes::OuterJoin
    )

or another version directly in ARel:

  actions = Action.arel_table
  action_users = ActionUser.arel_table
  Action
    .joins(
      actions
        .join(
          action_users, 
          Arel::Nodes::OuterJoin
        )
        .on(
          action_users[:action_id]
            .eq(actions[:id])
            .and(
              action_users[:user_id]
                .eq(user_id)
                .and(
                  actions[:user_id].eq(user_id)
                )
            )
        )
        .join_sources
    )

Orsa

Answer 5

Action.left_outer_joins(:actions_users).where(user_id: 1)

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

Although you did not ask for it yet, ...

Action.left_outer_joins(:actions_users).where(actions_users: {status: 'active'})

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

Actinism

I don't see this behavior.... the AND clause of the left outer joins is missing, and the WHERE clause is present. – Portative