Get count of related rows in postgres with existing lateral join

Question

This query gives me several rows from table1 along with the related records from table2 using left join lateral. I've limited the rows from table2 to 10 rows each.

select t1.id, array_agg(t2.column1) 
from table1 t1 
left join lateral (select * from table2 where table1_id = t1.id order by column2 limit 10) t2 on true 
where t1.other = other_value
group by t1.id

But how can I include the total count(*) of all records in table2 related to table1 (select count(*) from table2 where table1_id = t1.id). Since I'm doing a lateral join I'm not sure how I would add these results.

Could I reuse the lateral join I'm already doing, or would I have to do a separate lateral join because the first one has a limit 10 and the count(*) requires no limit? What should the query look like to make it work like this? (I think there might be a way to do it using the array slice syntax from the first lateral join but I think that would be expensive since it would have to get all the rows just to get a count of them.)

Gordon Linoff · Accepted Answer · 2020-04-27T17:31:20.803

You might as well use window functions for this:

select t1.id, array_agg(t2.column1) 
from table1 t1 left join
     (select t2.*, count(*) over (partition by table1_id) as cnt,
             row_number() over (partition by table1_id order by column2) as seqnum
      from table2
     ) t2
     on t2.table1_id = t1.id and sequm <= 10
where t1.other = other_value 
group by t1.id;

You could do this as a separate lateral join.

EDIT:

With a separate lateral join:

select t1.id, array_agg(t2.column1), t2c.cnt
from table1 t1 left join lateral
     (select *
      from table2
      where table1_id = t1.id
      order by column2
      limit 10
     ) t2
     on true left join lateral
     (select count(*) as cnt
      from table2
      where table1_id = t1.id
     ) t2c
     on true
where t1.other = other_value
group by t1.id, t2.cnt;

Or with a single lateral join with no aggregation in the outer query:

select t1.id, t2.column1s, t2.cnt
from table1 t1 left join lateral
     (select array_agg(t2.column1) as column1, max(cnt) as cnt
      from (select t2.*,
                   row_number() over (order by column2 desc) as seqnum,
                   count(*) over () as cnt
            from table2
            where table1_id = t1.id
           ) t2
      where seqnum <= 10
     ) t2
     on true left join 
where t1.other = other_value;

This is probably the best approach.

Thanks, could you also please show the way to do it with a separate lateral join? — user779159, Apr 27 '20 at 13:58

Get count of related rows in postgres with existing lateral join

1 Answers1