Rails relation ordering?

Question

So I want to translate this SQL query into Rails (and in this EXACT order): Suppose I have

WITH sub_table as (
  SELECT * FROM main_table LIMIT 10 OFFSET 100 ORDER BY id
)

SELECT * FROM sub_table INNER JOIN other_table
ON sub_table.id = other_table.other_id

The importance here is that the order of execution must be:

1. LIMIT and OFFSET in that sub_table query MUST be executed first
2. The second statement should happen after.

So if the relations I have are called OtherTable and MainTable does something like this work:

subTableRelation = MainTable.order(id: :asc).limit(10).offset(100)
subTableRelation.join(OtherTable, ....)

The main question here is how Rails Relation execution order impacts things.

Answer 1

While ActiveRecord does not provide CTEs in its high level API, Arel will allow you to build this exact query.

Since you did not provide models and obfuscated the table names I will build this completely in Arel for the time being.

sub_table = Arel::Table.new('sub_table')
main_table = Arel::Table.new('main_table')
other_table = Arel::Table.new('other_table')

sub_table_query = main_table.project(Arel.star).take(10).skip(100).order(main_table[:id])

sub_table_alias = Arel::Nodes::As.new(Arel.sql(sub_table.name),sub_table_query)

query = sub_table.project(Arel.star)
  .join(other_table).on(sub_table[:id].eq(other_table[:other_id]))
  .with(sub_table_alias)

query.to_sql

Output :

WITH sub_table AS (
  SELECT 
   * 
  FROM main_table 
  ORDER BY main_table.id
  -- Output here will differ by database
  LIMIT 10 OFFSET 100 
)

SELECT 
  * 
FROM sub_table 
INNER JOIN other_table ON sub_table.id = other_table.other_id

If you are able to provide better context I can provided a better solution, most likely resulting in an ActiveRecord::Relation object which is likely to be preferable for chaining and model access purposes.