Converting a SQL statement to rails command

Question

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

engineersmnky · Accepted Answer · 2021-11-16 23:50:07Z

2

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.

edited Nov 16, 2021 at 23:50

answered Nov 16, 2021 at 21:54

engineersmnky

30.5k2 gold badges42 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

max Over a year ago

Provided you have a fairly modern version of Rails and Ruby (2.7+) you can use endless and beginless ranges to create LT/GT/LTE/GTE conditions with the hash syntax. where(column_a: 'ABC', rn: ..10).or(Anotation.where('annotations.column_b <> 'AAA')'). In older versions you can use 10..Float::INFINITY.

engineersmnky Over a year ago

@max I always forget about that because it still feels awkward and almost dirty to me. Qualified Range means between, endless Range greater than equal to, beginingless range less than equal to, triple dot remove the equality, too much magic. I would almost prefer predication parsing e.g where(column_a: 'ABC', rn: {lt_eq: 10})

max Over a year ago

Yeah it is somewhat awkward and I can never remeber if .. or ... does LTE. I wish that where took a block and yielded the arel table or select manager or something so you could do where { |a| a[:rn].gt(10) }. Kind of like Squeel but with less metaprogramming insanity.

engineersmnky Over a year ago

@max agreed especially since sequel offers this type of interface and ransack offers compound finders like rn_lteq: 10

Collectives™ on Stack Overflow

Converting a SQL statement to rails command

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related