convert 2 select in db_select

Question

I have this query in SQL

select * 
from node n, node_collection_data nc
where n.nid = nc.nc_child_nid
and 
nc.nc_parent_nid = (select nc_parent_nid
from node_collection_data
where nc_child_nid=17)

This is what I got so far:

 $query -> join('node_collection_data', 'nc', 'n.nid =
 nc.nc_child_nid'); $query->condition('type', $node->type)
       ->condition('status', 1)
       ->condition('n.nid', $node->nid, '!=')
       ->condition(db_or()
       ->condition('n.language', array($language->language, LANGUAGE_NONE), 'IN')
         ->condition('n.language', $node->language, '=')
       )
       // get nc_child_id
       //->join('node_collection_' . $sort, $sort, 'n.nid = ' . $sort . '.nc_child_id')
       ->fields('n', array('nid', 'title'))
       ->fields('nc',array('nc_parent_nid')) // select nc_parent_id

I am trying to convert the last part.

nc.nc_parent_nid = (select nc_parent_nid from node_collection_data where nc_child_nid = 17)

How can I convert it to db_select()?

Answer 1

You need to search for db_select using subquery.

Look the below links to get some idea.

Using db_select with subqueries

db_select condition with subquery

Answer 2

Put your subquery in a variable like

$subquery = "(select nc_parent_nid from node_collection_data where nc_child_nid = 17)";

And then use it with addExpression function in your main query

$query->addExpression($subquery, 'sub_query');

Use the following debug function to check your query

dpm((string) $query);
dpm($query->arguments());