Postgres order entries by id, self reference id and date, after every parent, list childs

I have the table orders with the following fields id, updated_at, self_ref_id, and others which doesn’t count.
The column self_ref_id is a self reference two an order, one order can have multiple children, a child will not have other children.
I am trying to order all entries by updated_at desc but after every parent order, I want to have his children, also ordered by updated_at, even though the child updated_at is lower than the next parent updated_at.

I tried to order them by updated_at and group them by id and self_ref_id.

I am using go with gorm, if it counts, but I need a hint how to make this query. I was also trying with a subquery, but I didn’t succeeded. I don’t need duplicate fields, I just want to order them.

I can do some post-processing to sort them how I want, but I am curious If I can do this from a query.

Answer

You can do this with a self-join:

select o.*
from orders o
left join orders p on p.id = o.self_ref_id
order by 
    coalesce(p.updated_at, o.updated_at), 
    p.id is not null,
    o.updated_at

The left join brinds the parent row, if any. Then, we order by the parent date (or the current date if there is no parent); the second ordering criteria (p.id is not null) gives the priority to the “parent” row, and the last criteria orders children rows by date.