-1

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
jupcan
  • 436
  • 3
  • 7
  • 17

1 Answers1

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.

spickermann
  • 100,941
  • 9
  • 101
  • 131
  • thank you very much @spickermann! may i ask one more thing if thats okay for you, i have 3 models: box, stone and paper; each box is related to a paper through a stone so my goal was to get the last box for each paper (boxes can share papers), went for the aforementioned query after trying some active records ways to go but just realised im also wrong – jupcan Mar 15 '22 at 13:15
  • i had something like: Box.joins(:stone).group('stone.paper_id').maximum(:id).values so that i could then find the boxes with a where clause, problem is box id column is a uuid so i cant do a maximun of that, how would you go for something similar? is there a clean active record way to achieve it? thanks a lot! – jupcan Mar 15 '22 at 13:17
  • That is a tricky question. I suggest that you open a new question for this and ask for a solution in pure SQL then translating the SQL query to Rails is kind of easy. But coming up with a performant SQL statement is a hard part in this case. – spickermann Mar 15 '22 at 13:50
  • already got the postgresql query but dunno if it can be translated to ActiveRecord anymore since it is really a complex one: https://stackoverflow.com/a/71485359/3310774 what do you think @spickermann? – jupcan Mar 15 '22 at 16:40
  • think this one should be easier to translate: https://stackoverflow.com/a/71485197/3310774 – jupcan Mar 15 '22 at 16:54
  • 1
    I updated my answer to address your updated question. – spickermann Mar 15 '22 at 18:53
  • but papers is not related with boxes directly but with stones, then maybe it should be a double join instead of two different ones since that should fail to join box and papers – jupcan Mar 16 '22 at 11:22
  • 1
    You are right, in this case (nested association) it needs to be `joins(stones: :papers)` or you could define a direct association with `has_many :papers, through: :stones` on `Box` if you need this association more often. – spickermann Mar 16 '22 at 11:26