By holtzermann17 on

I have a two part query that pulls together comments and forum posts by using a union. This works fine -- as long as there aren't too many entries to deal with.

When there are a lot of entries, of course I want to use a pager. I noticed that I have to add my own "count query" (the auto-generated one is wrong). But now, even though my query is running, I'm not getting any results out!

I'll paste in my code below - any help would be appreciated!

  $firstQuery = db_select('comment','c');
  $firstQuery->addField('c', 'cid', 'my_id');
  $firstQuery->addField('c', 'subject', 'title');
  $firstQuery->addField('c', 'uid', 'uid');
  $firstQuery->addField('c', 'changed', 'changed');
  $firstQuery->addField('c', 'created', 'created');
  $firstQuery->addField('c', 'name', 'username');
  $firstQuery->join('node','n','n.nid = c.nid');
  $firstQuery->leftJoin('field_data_comment_body','fcb','c.cid = fcb.entity_id');
  $firstQuery->join('users','u','u.uid = c.uid');
  $firstQuery->addField('n', 'nid', 'nid');
  $firstQuery->addField('n', 'type', 'my_type');
  $firstQuery->addField('fcb', 'comment_body_value', 'body');

  $secondQuery = db_select('node','n');
  $secondQuery->addField('n', 'nid', 'my_id');
  $secondQuery->addField('n', 'title', 'title');
  $secondQuery->addField('n', 'uid', 'uid');
  $secondQuery->addField('n', 'changed', 'changed');
  $secondQuery->addField('n', 'created', 'created');
  $secondQuery->condition('type','forum', '=');
  $secondQuery->leftJoin('field_data_body','fdb','n.nid = fdb.entity_id');
  $secondQuery->join('users','u','u.uid = n.uid');
  $secondQuery->addField('u', 'name', 'username');
  $secondQuery->addField('n', 'nid', 'nid');
  $secondQuery->addField('n', 'type', 'my_type');
  $secondQuery->addField('fdb', 'body_value', 'body');

  $mergedQuery = $secondQuery->union($firstQuery, 'UNION ALL');

  $firstCounter = db_select('comment','c');
  $firstCounter->addField('c', 'cid', 'my_id');
  $firstCounter->join('node','n','n.nid = c.nid');
  $firstCounter->leftJoin('field_data_comment_body','fcb','c.cid = fcb.entity_id');
  $firstCounter->join('users','u','u.uid = c.uid');

  $secondCounter = db_select('node','n');
  $secondCounter->addField('n', 'nid', 'my_id');
  $secondCounter->condition('type','forum', '=');
  $secondCounter->leftJoin('field_data_body','fdb','n.nid = fdb.entity_id');
  $secondCounter->join('users','u','u.uid = n.uid');

  $innercountQuery = $secondCounter->union($firstCounter, 'UNION ALL');
  $countQuery = db_select($innercountQuery);
  $countQuery->addExpression('COUNT(*)','count');

  $result = $mergedQuery->extend('PagerDefault')->limit(20);
  $tmp = &$result;
  $tmp->setCountQuery($countQuery);

  dsm($result->__toString()); 
  dsm($countQuery->__toString()); 
  $result->execute();
⋅ Categories: Drupal 7.x

Comments

holtzermann17’s picture

holtzermann17 commented

The pager itself seems to work, but, I'm not getting any content out from the main query.

Another bonus would be to figure out how to get orderBy in there without causing an error.

holtzermann17’s picture

holtzermann17 commented

The query itself is constructed as follows:

    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)  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

The count function is

    SELECT COUNT(*) AS count
    FROM 
    (SELECT n.nid AS my_id
    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)  UNION ALL SELECT c.cid AS my_id
    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) subquery
holtzermann17’s picture

holtzermann17 commented

Can be seen here: http://pastebin.com/raw.php?i=wgNJEHCA (though this may be sort of hard to make sense of, so please skip ahead to the next comment).

holtzermann17’s picture

holtzermann17 commented

I see this when I turn on query logging:

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

This seems like a bug to me: it doesn't actually "limit" the results to 20 (it seems like everything is returned). This slight variant on the code DOES limit the results to 20, however:

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 = 'forum')
 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;

(Compare this tip from StackExchange: http://stackoverflow.com/questions/1415328/combining-union-and-limit-ope...)