• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Problem with MySQL Union and Limit

#1
[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


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.