1

I have a db_select that selects row from t_project_proposal_writer table only if in joined t_project_milestone table row with milestone_id 10 is found:

$query = db_select('t_project_proposal_writer', 'p')->fields('p');
$query->innerJoin('t_project_milestone', 'm', 'p.id=m.proposal_id');
$query->condition('p.initiative_nid', $node->nid)->condition('m.milestone_id', 10);

One project row can have 10 milestone rows. Now I need to do another query that selects only the rows from t_project_proposal_writer where milestone_id 10 not exists in joined t_project_milestone.

Is there any other possiblity to do that, instead of query all the projects and iterate throught them while checking if milestone 10 exists?

2

You can use isNull() method :

$query = db_select('scald_atoms', 'a');
$query->fields('a', array('sid','title'));
$query->leftjoin('table_name','table_alias','table_alias.entity_id = a.sid');
$query->isNull('table_alias.mon_field_value');
$query->execute();
$query->fetchAllAssoc('nid');

with your example something like that should do it :

$query = db_select('t_project_proposal_writer', 'p')->fields('p');
$query->leftjoin('t_project_milestone', 'm', 'p.id=m.proposal_id');
$query->isNull('m.milestone_id');
  • It doesn't work - it will select nothing, because for one project are in milestone table 9 rows (with milestone_id from 1 to 9, but not 10). – Incredible Feb 2 '16 at 15:44
  • 2
    Instead the innerJoin do a leftJoin on "p.id=m.proposal_id AND m.milestone_id=10", and then you can check whether the milestone_id is NULL using the method proposed by @Kgaut. – Mario Steinitz Feb 2 '16 at 15:55
  • Good point Mario, Thanks, my copy/paste of the original post was too fast ;). – Kgaut Feb 3 '16 at 7:04

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.