I have this query in SQL

select * 
from node n, node_collection_data nc
where n.nid = nc.nc_child_nid
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('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()?


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

  • I trust you have good reasons to post those links as an answer here. But I suggest you extend your answer with some relevant "quotes" from those 2 links. Not because "I" ask so, but it'll avoid your answer will (pretty sure) gets classified as "low quality answer" (and then deleted?) and/or gets downvoted. Just trying to help, OK? – Pierre.Vriens Dec 21 '15 at 8:38

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);

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.