CodeIgniter Forums
Problem with MySQL Union and Limit - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Problem with MySQL Union and Limit (/showthread.php?tid=20283)

Problem with MySQL Union and Limit - El Forum - 07-04-2009

[eluser]Unknown[/eluser]
Hi,

I have a quite heavy query that, a bit simplified, looks like this:

Code:
(SELECT
    ue.id,
    ue.type,
    ue.title,
    ue.body
FROM
    table1 AS t1
WHERE
    ue.user_id = 1)
    
UNION ALL

(SELECT
    ul.id,
    "l" as type,
    NULL AS title,
    NULL AS body
FROM
    table2 AS t2
WHERE
    ue.user_id = 1)

UNION ALL

(SELECT
    t.id,
    "ts" as type,
    t.name AS title,
    NULL AS body
FROM
    table3 AS t3
WHERE
    user_id = 1)

ORDER BY
    timestamp DESC
LIMIT
    0, 10

The reason I use UNION is that the tables don't have the same columns, but I need the data to be presented together in a chronological order.

This works just fine as long as I display it in one long list, or use 0 as offset in the LIMIT. However, if i change offset to something above 0, which is needed for the page to be paginated, the page is loading forever and eventually ends up with an almost never-ending loop, with a php notice saying 'undefined offset' for each iteration. I guess this occurs after php reaches its timeout.

So my questions are, doesn't UNION and LIMIT play well together? And does anyone have a clever workaround, or an alternative solution/setup for this? If anyone is willing to help out, i can provide you with more details if needed.

Thanks!
/ Patrik