Paging in Postgres on a Left Join

Question

Summary:

I have data in a db that needs to be displayed client side. Up until this point it wasn't paged but now the data has grown to a point that it's noticeably slowing the connection down. So I want to page it.

Setup:

• Client side I'm using DataTables
• Server side I'm using F#
• The DB is postgres

Problem:

I have 3 tables, Tables [A , B, C]. Table A has a one to many relationship with tables B and C. So when I do a query like

select * from A left join B on a.id = b.tableidb left join C on a.id = c.tableidc

I would get 7 rows, which is fine. This is all the data I actually want. The problem really comes when we try and page

select * from A left join B on a.id = b.tableidb left join C on a.id = c.tableidc limit 5 offset 0

As you can see, it does in fact bring back only 5 rows. However, because of the left joins, we don't get the full set of data.

Expected Solution

What I'd like to say is something to the effect of "Give me 5 rows from table A at offset 0, then left join on tables B and C"

Is there a way to do this in postgres?

Answer 1

You can use subselects in the FROM clause.

All you have to do is limit the number of rows there:

SELECT *
FROM (SELECT * FROM A
      ORDER BY a.id
      LIMIT 5) AS al
   LEFT JOIN b ON al.id = b.tableidb
   LEFT JOIN c on al.id = c.tableidc;

Notes:

1. Using LIMIT without ORDER BY does not make much sense.

2. If you consider paging, don't use LIMIT and OFFSET.

   Rather, remember the last a.id you selected the first time and query WHERE a.id > previous_a_id LIMIT 5.