How to apply pagination on the result of a SQL query using Joins?

Question

I have an SQL query that joins 3 tables, one of which is just many-to-many joining the other two. I am using Spring JDBC ResultSetExtractor to convert a ResultSet into my objects, which look something like this:

class Customer { private String id; private Set<AccountType> accountTypes; ... } 

The implementation of ResultSetExtractor is as follows:

 public List<Client> extractData(ResultSet rs) throws SQLException, DataAccessException { Map<Integer, Client> clientsMap = new LinkedHashMap<Integer, Client>(); while (rs.next()) { int id = rs.getInt("id"); // add the client to the map only the first time if (!clientsMap.containsKey(id)) { Client client = new Client(); client.setId(id); ... clientsMap.put(id, client); } // always add the account type to the existing client Client client = clientsMap.get(id); client.addAccountType(extractAccountTypeFrom(rs, id)); } return new ArrayList<Client>(clientsMap.values()); } 

This works great without pagination.

However, I need to break these results. I usually do this by adding this to the request, for example:

 SELECT ... ORDER BY name ASC LIMIT 10 OFFSET 30; 

However, since this query has connections, when I limit the number of results, I actually limit the number of JOINED results (that is, since the client will be displayed as many times as the number of types of accounts that they have, then LIMIT is applied not to the number of clients, but the number * accountTypes clients, which is not what I want).

The only solution I came across was to remove the LIMIT (and OFFSET, because that would also be wrong) from the query and apply them programmatically:

 List<Client> allClients = jdbcTemplate.query.... List<Client> result = allClients.subList(offset, offset+limit); 

But this is clearly not a very pleasant, effective solution. Is there a better way?

Answer 1

It's funny how writing a question is thought-provoking and actually helps in creating a solution to your own problem.

I managed to solve this problem by simply adding a part of the page request to a subquery of my main request, and not to the main request itself.

For example, instead of executing:

 SELECT client.id, client.name ... FROM clients AS client LEFT JOIN client_account_types AS cat ON client.id = cat.client_id FULL JOIN account_types AS at ON cat.account_type_id = at.id ORDER BY client.name ASC LIMIT 10 OFFSET 30; 

I'm doing it:

 SELECT client.id, client.name ... FROM ( SELECT * FROM clients ORDER BY name ASC LIMIT 10 OFFSET 0 ) AS client LEFT JOIN client_account_types AS cat ON client.id = cat.client_id FULL JOIN account_types AS at ON cat.account_type_id = at.id; 

Hope this helps other people as well.

Answer 2

If your DBMS supports it, use the window function DENSE_RANK . For instance:

 SELECT * FROM (SELECT *, DENSE_RANK() OVER (ORDER BY name, id) count FROM (SELECT a.id, a.name, b.title, DENSE_RANK() OVER (ORDER BY a.name, a.id) offset_ FROM AUTHOR a, BOOK b WHERE a.id = b.authorId) result_offset WHERE offset_ > 30) result_offset_count WHERE count <= 10