Sub selection in db_select selectQuery [duplicate]

Question

Possible Duplicate:
Is it possible to join dynamic tables (subqueries) using db_select()?

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?

Thanks in advance for your answer.

Answer 1

I just found out that you can add a db_select as a table into the left join. So the solution for this will be:

$subselect = db_select('og_membership', 'ogmk');
$subselect->join('node', 'kompas', "kompas.nid=ogmk.etid AND kompas.type='kompas'");
$subselect->join('publication_date', 'pd', 'pd.nid=kompas.nid');
$subselect->condition('pd.published_at', $end, '<');
$subselect->condition('pd.published_at', $start, '>');
$subselect->condition('kompas.status', 1, '=');
$subselect->fields('ogmk', array('gid', 'etid'));


$select->leftJoin($subselect, 'ogm_kompas', 'ogm_kompas.gid=og.gid');