Dynamic query with left join

Question

I need to collect comments that belong to certain user and submitted on pages of certain content type nodes. Currently I'm making two queries:

      $nodes = db_select('node', 'n')
      ->fields('n', array('nid'))
      ->execute()->fetchAll();
      unset($node_list);
      foreach ($nodes as $node) {
        $node_list[] = $node->nid;
      }

      $comments = db_select('comment', 'c')
      ->fields('c', array('cid', 'nid', 'created'))
      ->condition('c.nid', [$node_list], 'IN')
      ->execute()->fetchAll();
      foreach ($comments as $comment) {
        some actions
      }

and it's working fine. However, I believe I can make just one query for comment table by using some kind of database join to node table and check if a node belongs to $content_type. How can I do that?

Answer 1

Here is the documentation on joins which should put you on the right path to answering the question.

Untested, but something like this should work:

$query = db_select('node', 'n');
$query->join('comment', 'c', 'n.nid = c.nid');
$query
  ->fields('n', array('nid', 'title'))
  ->condition('n.type', 'page')
  ->condition('n.status', '1')
  ->orderBy('n.created', 'DESC')
  ->addTag('node_access');