How to write `(SELECT @row_index:=-1) AS r` in Arel?

Question

I need to convert the following query to Arel and I don't see any documentation convert the

(SELECT @row_index:=-1) AS r

into Arel query. For context the full query is like this.

SELECT AVG(subq.#{column_name}) as median_value, (SELECT @row_index:=-1) AS r, '#{type}' as median_type
        FROM (
          SELECT @row_index:=@row_index + 1 AS row_index, #{column_name}
          FROM location_markets
          WHERE #{where} AND location_type = '#{location_type}' AND #{column_name} is NOT NULL
          ORDER BY #{column_name}
        ) AS subq
        WHERE subq.row_index IN (FLOOR(@row_index / 2) , CEIL(@row_index / 2))

Not sure how to write the select statement in Arel for this query.

Answer 1

You can write pretty much anything in Arel but in this case it might not make a lot of sense.

Arel::Nodes::As.new(
  Arel::Nodes::Grouping.new(
    Arel::Nodes::UnaryOperation.new('SELECT',
      Arel::Nodes::InfixOperation.new(':=',Arel.sql('@row_index'),-1)
    )
  ),
Arel.sql('r')).to_sql
#=> "( SELECT @row_index := -1) AS r"

UPDATE Full Translation:

# Required variables
column_name = :abc
type = 'SOME_TYPE'
location_type = 'LOCATION_TYPE'

# Tables
sub = Arel::Table.new('subq')
lm = Arel::Table.new('location_markets')

# resuable literal
ridx = Arel.sql('@row_index')

# Sub Query Definition
sub_q_body = lm.project(
  Arel::Nodes::InfixOperation.new(':=',ridx,Arel::Nodes::Addition.new(ridx,1)).as('row_index'), 
  lm[column_name]
).where(
  lm[:location_type].eq(location_type).and( 
    lm[column_name].not_eq(nil)
  )
).order(
  lm[column_name]
)

# Sub Query Alias
sub_q = Arel::Nodes::As.new(sub_q_body,Arel.sql(sub.name)) 

# Full Query
query = sub.project(
  sub[column_name].average.as('median_value'),
  Arel::Nodes::As.new(
    Arel::Nodes::Grouping.new(
      Arel::Nodes::UnaryOperation.new('SELECT',
        Arel::Nodes::InfixOperation.new(':=',ridx,-1)
      )
    ),
  Arel.sql('r')), 
  Arel::Nodes::As.new(Arel::Nodes.build_quoted(type),Arel.sql('median_type'))
).from(sub_q).where(sub[:row_index].in(
  [Arel::Nodes::NamedFunction.new('FLOOR',[Arel::Nodes::Division.new(ridx,2)]),
    Arel::Nodes::NamedFunction.new('CEIL',[Arel::Nodes::Division.new(ridx,2)])]
))

Result

SELECT 
  AVG(subq.abc) AS median_value, 
  ( SELECT @row_index := -1) AS r, 
  'SOME_TYPE' AS median_type 
FROM 
 (
   SELECT 
     @row_index := @row_index + 1 AS row_index, 
     location_markets.abc 
   FROM 
     location_markets
   WHERE 
     location_markets.location_type = 'LOCATION_TYPE' 
     AND location_markets.abc IS NOT NULL  
   ORDER BY 
     location_markets.abc) AS subq 
WHERE 
  subq.row_index IN (FLOOR(@row_index / 2), CEIL(@row_index / 2))