Hi,
I have a quite heavy query that, a bit simplified, looks like this:
(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
