1

I can't figure out how to select all aid's from the areas table that do not exist in the field_data_field_area_parent table.

It almost works, except it returns Windy Ridge with an "aid" of "11". I am guessing that this is because "IN" is the same as "ANY", and the number "1" is in the subquery results.

How can I use NOT EXISTS instead of NOT IN?

function verb_get_parent_areas () {
  $query = db_select('areas', 'a');
  $query->distinct();
  $query->fields('a',array('name','aid','parent','latitude','longitude','description'));
  $subquery = db_select('field_data_field_area_parent', 'p');
  $subquery->fields('p', array('entity_id'));
  $query->condition('a.aid', $subquery, 'NOT IN');

  $result = $query->execute();
  while ($record = $result->fetchAll()) {       
    $areas = drupal_json_encode($record);
  }

  return $areas;
  exit;
}

I've thought of trying this, but it returns nothing.

function verb_get_parent_areas () {
  $query = db_select('areas', 'a');
  $query->distinct();
  $query->fields('a',array('name','aid','parent','latitude','longitude','description'));
  $subquery = db_select('field_data_field_area_parent', 'p');
  $subquery->fields('p', array('entity_id'));
  $query->notExists($subquery);

  $result = $query->execute();
  while ($record = $result->fetchAll()) {       
    $areas = drupal_json_encode($record);
  }

  return $areas;
  exit;
}
1
  • 1
    Output your query and arguements to get more insight into what Drupal is doing: drupal_set_message($query->__toString()); drupal_set_message(print_r($query->arguments(),true)); Sep 4, 2013 at 23:13

2 Answers 2

0

Here is another approach to write Dynamic queries. We generally write queries in phpmyadmin or mysql command prompt and then convert them to D7 style or dynamic queries using Query coder module.

For example, as per your question, you want to find out all those records in child table which don't exist in parent table.

So D6 style query,

db_query("SELECT c.* FROM {child} c LEFT JOIN {parent} p ON c.p_id = p.p_id WHERE p.p_id IS NULL");

after converting to D7 style query using query coder module would look like,

$query = db_select('child', 'c');
$query->fields('c', array('*'));
$query->leftJoin('parent', 'p', 'c.p_id = p.p_id');
$query->isNull('p.p_id');
$result = $query->execute();
-2

Have you tried out drupal_write_record() function? if no then see below link,

https://stackoverflow.com/questions/5825337/help-with-db-query-in-drupal-if-exists-update-else-insert

This may helpful to you.

Thanks.

4
  • 2
    Chandresh,thanks for your reply, but if I'm not mistaken, drupal_write_record is for updating the database. I am only trying to query the database to output in JSON.
    – Chris
    Jan 25, 2012 at 6:20
  • 1
    ok..i'll get back to you again with solutions.
    – Chandresh
    Jan 25, 2012 at 6:22
  • Hey, thanks for your help, turns out that my query with 'NOT IN' was actually correct, my data in the database was wrong. I can't believe that I've been working on this for two days and the problem was with my data.
    – Chris
    Jan 25, 2012 at 6:33
  • 3
    ok..great. you can also use: $query->condition('', $subquery, 'EXISTS');
    – Chandresh
    Jan 25, 2012 at 6:52

Your Answer

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

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