3

I need to use sub query on db_select refer the SQL below.

I think the first select should be like db_select('tabe1','tab') but this table is a virtual table. I have to create temporary only for combining 2 columns with coalesce.

Normal SQL(that works)

select nid, field_title_value, field_state_value, newid, e.denomination_ed
from (SELECT nid, n.created, field_title_value, field_state_value, n.uid, 
edi.field_edit_target_id, COALESCE(edi.field_edit_target_id, n.uid) AS newid FROM node as n
left join field_data_field_state as et on n.nid = st.entity_id 
left join field_data_field_title as ta on n.nid = t.entity_id
left join field_data_field_edit as edi on n.nid = edi.entity_id) as tabe1
left join editor as e on e.uid = tabe1.newid

How to do the first db_select

2 Answers 2

2

This is example for subquery

    $subQuery = db_select('users','u');
    $subQuery->fields('u',array('uid'));

    $query = db_select('club_members','cm')->distinct();
    $query->fields('cm',array('country_id','name', 'country'));
    $query->condition('cm.status',1);
    $query->condition('cm.user_id',$subQuery,'NOT IN');
    $resultLow = $query->execute()->fetchAllAssoc('uid');
1
  • Thanks Reshma but in my case : tabe1 is not like users, how to make db_select with it inside
    – Mitch
    Jul 14, 2015 at 8:54
1

You can try the code:

$query = db_select('node', 'n')
  ->fields('n', array('nid'));
$subquery = db_select('node', 'n2')
  ->fields('n2', array('nid'))
  ->join('users', 'u', 'n2.uid = u.uid'
  ->condition('u.status', 0);
$query->condition('', $subquery, 'EXISTS');
$result = $query->execute();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.