1

Here's an example raw query of what I am trying to achieve with database API:

SELECT * FROM (
    SELECT
      n.nid AS entity_id,
      n.uid,
      n.title,
      node_body.body_value,
      node_body.body_format,
      'node' AS entity_type
    FROM
      `node` n,
      `field_data_body` node_body
    WHERE
      n.status = 1 AND
      n.type = 'forum' AND
      n.nid = node_body.entity_id
  UNION ALL
    SELECT
      c.cid,
      c.uid,
      '-' AS subject,
      comment_body.comment_body_value,
      comment_body.comment_body_format,
      'comment' AS entity_type
    FROM
      `node` n,
      `comment` c,
      `field_data_comment_body` comment_body
    WHERE
      c.nid = n.nid AND
      n.status = 1 AND
      n.type = 'forum' AND
      c.cid = comment_body.entity_id
) AS results ORDER BY results.entity_id

I have to do this using db_select() because of the pager.

My question is: how can I use db_select() for selecting results that are not from a specific table, but from subqueries like the query above?

Thanks!

2
  • 1
    Basically you can't, unless you add subqueries created with db_select (or objectively). Have you seen this and tried to play with it?
    – Mołot
    Nov 8, 2013 at 22:41
  • Thanks for the comment. That's what I was afraid of - I was thinking of having a bogus query in the main db_query() and then adding the real query through an expression or a join. Nov 8, 2013 at 22:50

2 Answers 2

3

First of all, you can prepare a SelectQuery object using db_select(), then use it in place of a table in another db_select(). Example (off-the-cuff, not tested):

$inner_query = db_select('comment', 'c')
  ->fields('c');
$outer_query = db_select($inner_query, 'sub');

Second of all, you can UNION two prepared SelectQuery objects with the SelectQuery::union() method.

$some_query = db_select('comment_table', c)->fields('c');
$union_query = db_select('other_comment_table', o)->fields('o')->union($some_query, 'ALL');

So all the tools you need are there; it'll just take some trial and error to put them all together.

4
  • Great solutions, but I don't think the exact query the OP's looking for is possible with the core SelectQuery. Specifically, there's no way to get SELECT * at the beginning of the query as far as I can tell (unless you can assign an alias to the chain of UNIONs and force it to select results.*). It looks like a table and/or field alias will always be added to the expression. Subclassing SelectQuery to do it would be a pretty quick job though
    – Clive
    Nov 8, 2013 at 23:00
  • Thank you for your answer. I started playing around with this - will post back shortly. Nov 9, 2013 at 9:41
  • @Clive could you elaborate a bit more please? Do you have any link to more information/example? Nov 11, 2013 at 12:53
  • @LesLim seems this can't directly answer the question; the main problem is exactly what Clive mentioned - I can't get the SELECT * in front of everything, which is essential as I want to mix results of nodes and comments. Do you have any other suggestions? Thanks in any case :) Nov 11, 2013 at 16:53
1

I posted a modified question on DBA site and the solution was to simply use a DB view instead.

Here is the code I ended up using:

MYMODULE.install file:

function MYMODULE_install() {
  db_query("CREATE VIEW {MYMODULE_view_sample}
              SELECT
                n.nid AS entity_id,
                n.uid,
                n.title,
                node_body.body_value,
                node_body.body_format,
                'node' AS entity_type
              FROM
                {node} n,
                {field_data_body} node_body
              WHERE
                n.status = 1 AND
                n.type = 'forum' AND
                n.nid = node_body.entity_id
            UNION ALL
              SELECT
                c.cid,
                c.uid,
                '-' AS subject,
                comment_body.comment_body_value,
                comment_body.comment_body_format,
                'comment' AS entity_type
              FROM
                {node} n,
                {comment} c,
                {field_data_comment_body} comment_body
              WHERE
                c.nid = n.nid AND
                n.status = 1 AND
                n.type = 'forum' AND
                c.cid = comment_body.entity_id");
}

function MYMODULE_uninstall() {
  // Remove the view once the module is uninstalled.
  db_query("DROP VIEW {MYMODULE_view_sample}");
}

And then simply browse it as if it was another table:

$query = db_select('MYMODULE_view_sample', 's');
$query = $query
  ->extend('PagerDefault')
  ->limit(10);
$query->fields('s');
$results = $query->execute();
foreach ($results as $result) {
  dpm($result);
}

Thanks to everyone posting here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.