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!

  • 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 '13 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. – Aram Boyajyan Nov 8 '13 at 22:50
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.

  • 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 '13 at 23:00
  • Thank you for your answer. I started playing around with this - will post back shortly. – Aram Boyajyan Nov 9 '13 at 9:41
  • @Clive could you elaborate a bit more please? Do you have any link to more information/example? – Aram Boyajyan Nov 11 '13 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 :) – Aram Boyajyan Nov 11 '13 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, privacy policy and cookie policy

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