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.

CodePudding user response：

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