How to select nth child from parent table with counts
Suppose I have two tables:
create table parents (id integer, name varchar, child_count integer);
create table children (id integer, name varchar, parent_id integer);
Suppose the parent: child ratio is about 1: 1000.
And let it child_count
be updated periodically (or available through a materialized view in PostgreSQL), for example using this update statement:
update parents p set child_count = pc.count
from (select p.id id, count(c.*) count
from parents p join children c on p.id = c.parent_id
group by p.id) as pc
where p.id = pc.id;
I want to find the nth child for all children, where the children are sorted first by parent name and then by child name. Basically, I want this query:
select c.*
from children c join parents p on p.id = c.parent_id
order by p.name, c.name
limit 42 offset 42;
... except that this is an expensive request requiring scans through children, but I know it can be done much cheaper using the child_count field - at the risk of being deprecated, but I don't care what.
Is there a way to write a query (preferably for postgresql) that uses the parent's child_count to select the nth child for all parents?
Or is there a better way to structure the data? I would consider using ranges (regardless of whether postgresql is native or uses two separate columns), except that they seem tricky to update. And I can select the nth child using a different sort order (but always sorting using parents, then children).
My best idea is to cache all parents in memory and use a constraint / offset query after I have opened the correct parent myself, but there may be a significant number of parents as well.
source to share
It seems that row_number
the window function (and see this and this ) might be exactly what you need:
SELECT x.*
FROM ( SELECT p.id AS parent_id,
p.name AS parent_name,
c.id AS child_id,
c.name AS child_name,
row_number() OVER ( ORDER BY p.name, c.name ) AS ordinal
FROM children c
JOIN parents p ON c.parent_id = p.id
) AS x
WHERE x.ordinal = 42
ORDER BY p.name, c.name;
In fact, with the above query, you don't even need to store the column child_count
(for other reasons).
source to share