Converting a SQL statement to rails commabd-CodePudding

I have a situation where I need to fetch only few records from a particular active record query response.

@annotations = Annotations.select('*', ROW_NUMBER() OVER (PARTITION BY column_a) ORDER BY column_b)

Above is the query for which the @annotations is the Active Record Response on which I would like to apply the below logic. Is there a better way to write the below logic in rails way?

with some_table as
(
 select *, row_number() over (partition by column_a order by column_b) rn
 from the_table
)
select * from some_table
where (column_a = 'ABC' and rn <= 10) or (column_b <> 'AAA')

CodePudding user response：

ActiveRecord does not provide CTEs in its high level API; however with a little Arel we can make this a sub query in the FROM clause

annotations_table = Annotation.arel_table 
sub_query = annotations_table.project(
  Arel.star,
  Arel.sql('row_number() over (partition by column_a order by column_b)').as(:rn)
)
query = Arel::Nodes::As.new(sub_query, Arel.sql(annotations_table.name))

Annotation.from(query).where(
  annotations_table[:column_a].eq('ABC').and(
    annotations_table[:rn].lt(10)
  ).or(annotations_table[:column_b].not_eq('AAA'))
)

The result will be a collection of Annotation objects using your CTE and the filters you described.

SQL:

select annotations.* 
from (
 select *, row_number() over (partition by column_a order by column_b) AS rn
 from annotations
) AS annotations
where (annotations.column_a = 'ABC' and annotations.rn <= 10) or (annotations.column_b <> 'AAA')

Notes:

With a little extra work we could make this a CTE but it does not seem needed in this case
We could use a bunch of hacks to transition this row_number() over (partition by column_a order by column_b) to Arel as well but it did not seem pertinent to the question.