Using `joins()` for first an INNER JOIN then a LEFT JOIN of the next table

Question

I am trying to avoid string interpolating my joins in Rails because I've noticed a decrease in flexibility when chaining queriers together. That is, I feel that joins(:table1) is much more flexible than joins('inner join table1 on table1.id = this_table.table1_id').

What I would like to accomplish is:

FROM table1
INNER JOIN table2 on table2.id = table1.table2_id
LEFT JOIN table3 on table3.id = table2.table3_id

inner join all

However, I can't figure out how to do it using Rails parlance:

Table1.joins(table2: :table3)

Results in an INNER join on the final table.

FROM table1
INNER JOIN table2 on table2.id = table1.table2_id
INNER JOIN table3 on table3.id = table2.table3_id

left joins all

If I use left_outer_joins...

Table1.left_joins(table2: :table3)

Results in LEFT joins on both tables (unwanted).

FROM table1
LEFT JOIN table2 on table2.id = table1.table2_id
LEFT JOIN table3 on table3.id = table2.table3_id

how to mix joins ?

Can't seem to chain joins without specify the relations ... that is, these don't work:

Table1.joins(:table2).left_join(table2: :table3)
Table1.joins(:table2).left_join(:table3)

Is there any way to do this the way I want?

Answer 1

Here are 3 options, but the 1st option should be the best.

Option #1:
You can reorder your Joins as below, I have the same scenario and I tried the below and it worked. What you are doing here is using the Table2 at the beginning, so it can be join with Table1 and can be left_outer_join with Table3:

Table2.joins(:table1).left_outer_joins(:table3)

Option #2:
You can use Raw Sql in Rails as below:

Table1.joins(:table2).joins('LEFT OUTER JOIN table3  ON  table3.id = table2.table3_id')

Option #3:
Another way to get the data of Table3 data, is to use includes as below, but it will fire 3 queries:

Table1.joins(:table2).includes(table2: :table3)

Answer 2

Table1.joins(:table2).left_joins(table2: :table3) work for me. (Using Rails 6.0.3.2 & Postgres)

Answer 3

As far as I know, there is not a "user-facing" syntax to do so with #left_outer_joins (and I wasn't able to get it working using #merge either), but you can achieve it as follows:

left_join_table3 = Table1
  .left_joins(:table2 => :table3)
  .arel.join_sources[1]

Table1.joins(:table2, left_join_table3)

# Generates the following SQL (for belongs_to associations):
#
# SELECT "table1".* FROM "table1"
# INNER JOIN "table2" ON "table2"."id" = "table1"."table2_id"
# LEFT OUTER JOIN "table3" ON "table3"."id" = "table2"."table3_id"

For re-use, i.e., in a scope, if you leave out the constant, I believe that the aliases will be consistent, which it sounds like you are trying to achieve.

scope :my_scope, -> {
  left_join_table3 = left_joins(:table2 => :table3).arel.join_sources[1]
  joins(:table2, left_join_table3)
}