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