translate postrgreSQL query to ActiveRecord

Question

i would like to translate this query to an active record statement to get things done in a more rails way although it could be executed as it is with ActiveRecord::Base.connection.execute

have been struggling to achieve it, any help on this will be much appreciated!

edited with new postgresql query to translate:

SELECT DISTINCT ON (p.id) b.id
FROM boxes AS b
INNER JOIN stones AS s
  ON s.id = b.stone_id
INNER JOIN papers AS p
  ON p.id = s.paper_id
ORDER BY p.id, b.created_at DESC

or this one which is equivalent but i think more complex to translate:

with x as (
select row_number() over (partition by p.id order by b.created_at desc) 
as rn,b.id as id_box,p.id as id_paper
from boxes b join stones s on b.stone_id = s.id
join papers p on p.id = s.paper_id)
select x.id_box from x where rn = 1

Answer 1

distinct on is not supported with a dedicated method in ActiveRecord, therefore you have to be more verbose:

Box
  .select('distinct on (papers.id) boxes.id')
  .joins(stones: :papers)
  .order('papers.id, boxes.created_at DESC')

Box, joins(:stones) and joins(:papers) of course only works when you have your ActiveRecord models and associations properly set up. Depending on how you design your associations in the models you might need to use the singular instead of the plural in the joins. The table names in the other parts of the statement should alwys be in plural form when following common Rails conventions.