Details of my code are in this support request: http://drupal.org/node/1770602

To sum up the issue briefly: I have a query that I need to express using a UNION. I also need to use a pager to display the results properly. When I run my code, I see the pager, but I don't see the actual results.

When I turn on query logging in devel, I see that this is the query that gets generated and run:

SELECT n.nid AS my_id, n.title AS title, n.uid AS uid, n.changed AS changed, n.created AS created, u.name AS username, n.nid AS nid, n.type AS my_type, fdb.body_value AS body
FROM node n
LEFT OUTER JOIN field_data_body fdb ON n.nid = fdb.entity_id
INNER JOIN users u ON u.uid = n.uid
WHERE (type = :db_condition_placeholder_0)
LIMIT 20 OFFSET 0
UNION ALL
SELECT c.cid AS my_id, c.subject AS title, c.uid AS uid, c.changed AS changed, c.created AS created, c.name AS username, n.nid AS nid, n.type AS my_type, fcb.comment_body_value AS body
FROM comment c
INNER JOIN node n ON n.nid = c.nid
LEFT OUTER JOIN field_data_comment_body fcb ON c.cid = fcb.entity_id
INNER JOIN users u ON u.uid = c.uid

That query is wrong. In fact, when I tested the query against SQL, I see that it doesn't effectively limit the results: everything is returned! StackExchange explains that it's missing a second LIMIT 20 OFFSET 0, which would enable it to work properly:

SELECT n.nid AS my_id, n.title AS title, n.uid AS uid, n.changed AS changed, n.created AS created, u.name AS username, n.nid AS nid, n.type AS my_type, fdb.body_value AS body
FROM node n
LEFT OUTER JOIN field_data_body fdb ON n.nid = fdb.entity_id
INNER JOIN users u ON u.uid = n.uid
WHERE (type = :db_condition_placeholder_0)
LIMIT 20 OFFSET 0
UNION ALL
SELECT c.cid AS my_id, c.subject AS title, c.uid AS uid, c.changed AS changed, c.created AS created, c.name AS username, n.nid AS nid, n.type AS my_type, fcb.comment_body_value AS body
FROM comment c
INNER JOIN node n ON n.nid = c.nid
LEFT OUTER JOIN field_data_comment_body fcb ON c.cid = fcb.entity_id
INNER JOIN users u ON u.uid = c.uid
LIMIT 20 OFFSET 0

(And, yes, testing that on my system, it does indeed return 20 results as required.)

Note that the same StackExchange discussion also says what to do with ORDER BY (which was giving me an error when I tried to add it). The correct final query, including "ORDER BY created DESC", would be:

(SELECT n.nid AS my_id, n.title AS title, n.uid AS uid, n.changed AS changed, n.created AS created, u.name AS username, n.nid AS nid, n.type AS my_type, fdb.body_value AS body
FROM node n
LEFT OUTER JOIN field_data_body fdb ON n.nid = fdb.entity_id
INNER JOIN users u ON u.uid = n.uid
WHERE (type = :db_condition_placeholder_0)
ORDER BY created DESC LIMIT 10 OFFSET 0)
UNION ALL
(SELECT c.cid AS my_id, c.subject AS title, c.uid AS uid, c.changed AS changed, c.created AS created, c.name AS username, n.nid AS nid, n.type AS my_type, fcb.comment_body_value AS body
FROM comment c
INNER JOIN node n ON n.nid = c.nid
LEFT OUTER JOIN field_data_comment_body fcb ON c.cid = fcb.entity_id
INNER JOIN users u ON u.uid = c.uid
ORDER BY created DESC LIMIT 10 OFFSET 0);

In this case, the parentheses around the two UNIONed SELECT statements are necessary, or the query doesn't work. (Note: I divided the "limit 20" into two equal parts, otherwise I get back 40 results total, but that's a minor detail!)

Comments

Version:7.15» 8.x-dev
Priority:Major» Normal
Status:Active» Postponed (maintainer needs more info)
Issue tags:+needs backport to D7

Can you reproduce this in D8?

@tim.plunkett (#1):

I tried enabling the module on a fresh D8 instance, and I get the following error:

Fatal error: Class 'PagerDefault' not found in
/Users/jac2349/drupal/core/lib/Drupal/Core/Database/Query/Select.php
on line 299

So I guess the answer is, I can't reproduce it on D8 without some work/porting. And I don't know how the D8 database API works. However, I can point to the code, so others who know more can help explain how it might (or might not) work in D8.

The .module is here: https://github.com/cdavid/drupal_planetary/blob/master/sites/all/modules/planetmath_messages/planetmath_messages.module