Sub selection in db_select selectQuery

Posted by KlaasDeVries on January 29, 2013 at 8:42am

I have this part of a query running in drupal with db_query.

LEFT JOIN (SELECT ogmk.gid, ogmk.etid, kompas.* FROM og_membership ogmk 
        INNER JOIN node kompas ON kompas.nid=ogmk.etid AND kompas.type='kompas'
        INNER JOIN publication_date pd ON pd.nid=kompas.nid
        WHERE pd.published_at > :start AND pd.published_at < :end AND kompas.status = 1
        ) ogm_kompas
        ON ogm_kompas.gid=og.gid

The left join should give records if they are available. If not the fields will be null.

How can I transform this to the db_select method with a selectQuery, so I can use the theme function for sorting and paging? I want to make a subquery in a selectQuery->leftJoin().

Thanks in advance for your answer.

Comments

leftJoin

Posted by blacklabel_tom on January 30, 2013 at 5:55pm

You could try something like:

<?php
  $query->leftJoin('table', 'alias', 'SELECT * FROM a AS table WHERE b = :var', array(':var', $var));
?>

You would need to add each join on it's own.

Reference the documentation for this as what I've written is off the top of my head:

http://api.drupal.org/api/drupal/includes%21database%21select.inc/function/SelectQueryExtender%3A%3AleftJoin/7

Cheers

Tom

Bristol based Drupal web design
BlackLabel Web Design