0

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?

1
  • The question title says "left join" but it's not clear from the example why/how this would need to be a left join. My example uses a regular join. I could work on a left join if that's what you truly need. – greggles Feb 22 at 15:39
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');

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.