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

Question

I have SQL query which joins 3 tables, one being just a many-to-many connecting the other two. I use a Spring JDBC ResultSetExtractor to convert the ResultSet into my Objects which look roughly like this:

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

The ResultSetExtractor implementation looks like this:

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 fine without pagination.

However, I need to paginate these results. The way I usually do it is by adding this to the query, for example:

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

However, as this query has joins, when I limit the number of results, I am actually limiting the number of JOINED results (ie. as a client will appear as many times as the number of account types they have, the LIMIT is applied not the number of clients, but to the number of clients*accountTypes, which is not what I want).

The only solution I came up with 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 nice, efficient solution. Is there a better way?

Answer 1

It's funny how writing a question makes you think, and actually helps a lot in imagining a solution for your own problem.

I was able to solve this problem by simply adding the pagination part of the query to a sub-query of my main query, rather than to the main query itself.

For example, instead of doing:

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 am doing this:

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 too.