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?

Wyattwyche

You want to avoid using find_by_sql? – Harber

@FabrizioBertoglio - yea ... want to leverage Rails clever way of mashing multiple joins from chained operations; hard coding the sql is more brittle. – Wyattwyche

I was able to learn more on joins from your post ! – Harber

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)

Parian

Thanks for the suggestions - am trying to avoid raw sql so I can leverage the beautiful chaining that Rails allows. I didn't know about includes -- learned something new! But that's not exactly what I am trying to do ... maybe it's not doable the way I want. – Wyattwyche

That's an interesting suggestion - thanks! However, It's not tenable for me as I am chaining many possible queries against (in this case) Table1. Appreciate you exploring the options. – Wyattwyche

Answer 2

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

Urbannal

That will have the effect of both inner joining and left outer joining table2. – Kate

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)
}

Depside

Answer 4

It's not the cleanest method, but I've gotten around this by adding a method to ActiveRecord called join_sql.

class ActiveRecord::Base
  def self.join_sql(scope=current_scope)
    sql = scope.to_sql
    join_index = sql.index(/(LEFT OUTER|INNER JOIN)/)
    if join_index
      join_end_index = sql.index(/ (WHERE|GROUP BY|ORDER BY)/) || -1
      sql[join_index..join_end_index]
    end
  end
end

Then, you can have use the ActiveRecord joins method with a string instead of symbols:

Table1.joins(:table2).joins(Table2.left_joins(:table3).join_sql)

Kate

Note, there may be cases I am missing here with regards to how I'm parsing out the join sql from the rest of the sql statement. Let me know if you come across other cases I am missing. – Kate