Using db_select with subqueries 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!
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')
  -&gt;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)-&gt;fields('c');
$union_query = db_select('other_comment_table', o)-&gt;fields('o')-&gt;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.