Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.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
Comment #1
tim.plunkettCan you reproduce this in D8?
Comment #2
holtzermann17 CreditAttribution: holtzermann17 commented@tim.plunkett (#1):
I tried enabling the module on a fresh D8 instance, and I get the following error:
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
Comment #3
sudishth CreditAttribution: sudishth as a volunteer and commentedi werite like this it working for me
Comment #4
dawehner@sudishth
Your query certainly doesn't contain contain an union.
@holtzermann17
A link to a private repo is kinda useful :P
Comment #13
pameeela CreditAttribution: pameeela commentedMoving this over to D7 given #2 saying it can't be reproduced in D8.
Going to leave as postponed though, given that no additional info has been added since 2016. Someone who knows more about D7 can decide whether to keep it open or close :)
Comment #14
pameeela CreditAttribution: pameeela commentedComment #15
mstrelan CreditAttribution: mstrelan at PreviousNext commentedThis has been PMNMI for 11 years. It's time to close it. If anyone has steps to reproduce please re-open or raise a new issue.