Paginate items from different sources

Question

Here's a problem I'm facing: I need to lists some items. Those items come from different sources (let's say table A, table B, table C), with different attributes and nature (although some are common).

How can I merge them together in a list that is paginated?

The options I've considered:

• Get them all first, then sort and paginate them afterwards in the code. This doesn't work well because there are too many items (thousands) and performance is a mess.

• Join them in a SQL view with their shared attributes, once the SQL query is done, reload only the paginated items to get the rest of their attributes. This works so far, but might become difficult to maintain if the sources change/increase.

Do you know any other option? Basically, what is the most used/recommended way to paginate items from two data sources (either in SQL or directly in the code).

Thanks.

Answer 1

If UNION solves the problem, here are some syntax and optimization tips.

This will provide page 21 of 10-row pages:

(
  ( SELECT ... LIMIT 210 )
  UNION  [ALL|DISTINCT]
  ( SELECT ... LIMIT 210 )
) ORDER BY ... LIMIT 10 OFFSET 200

Note that 210 = 200+10. You can't trust using OFFSET in the inner SELECTs.

Use UNION ALL for speed, but if there could be repeated rows between the SELECTs, then explicitly say UNION DISTINCT.

If you take away too many parentheses, you will get either syntax errors or the 'wrong' results.

If you end up with a subquery, repeat the ORDER BY but not the LIMIT:

SELECT ...
    FROM (
           ( SELECT ... LIMIT 210 )
           UNION  [ALL|DISTINCT]
           ( SELECT ... LIMIT 210 )
           ORDER BY ... LIMIT 10 OFFSET 200
         ) AS u
    JOIN something_else  ON ...
    ORDER BY ...

One reason that might include a JOIN is for performance -- The subquery u has boiled the resultset down to only 10 rows, hence the JOIN will have only 10 things to look up. Putting the JOIN inside would lead to lots of joining before whittling down to only 10.

Answer 2

I actually had to answer a similar situation very recently, specifically reporting across two large tables and paginating across both of them. The answer I came to was to use subqueries, like so:

SELECT
    t1.id as 't1_id',
    t1.name as 't1_name',
    t1.attribute as 't1_attribute',
    t2.id as 't2_id',
    t2.name as 't2_name',
    t2.attribute as 't2_attribute',
    l.attribute as 'l_attribute'
FROM (
    SELECT
        id, name, attribute
    FROM
        table1
    /* You can perform joins in here if you want, just make sure you're using your aliases right */
    /* You can also put where statements here */
    ORDER BY
        name DESC, id ASC
    LIMIT 0,50
    ) as t1
INNER JOIN (
    SELECT
        id,
        name,
        attribute
    FROM
        table2
    ORDER BY
        attribute ASC
    LIMIT 250,50
    ) as t2
    ON  t2.id IS NOT NULL
LEFT JOIN
    linkingTable as l
    ON  l.t1Id = t1.id
    AND l.t2Id = t2.id
/* Do your wheres and stuff here */
/* You shouldn't need to do any additional ordering or limiting */