3

I have the following SQL that I'm trying to translate using Drupal's Database API:

select nid,biblio_type from biblio where not exists
    (select * from biblio_types where biblio_type = tid);

What would the translation be? Currently I have this which is not working:

$sub_query = db_select('biblio_types','bt');
$sub_query->fields('bt',array('tid'))
         ->condition('biblio_type','tid','=');
$query = db_select('biblio','b');
$query->fields('b',array('biblio_type'))
      ->notExists($sub_query);
$result = $query->execute();

EDIT #1: Changed exists to notExists.

EDIT #2:

After changing exists to notExists and replacing the condition statement in the subquery with where per Clive's instructions, this is the SQL that is placed in $result->queryString:

SELECT b.biblio_type AS biblio_type
FROM 
biblio b
WHERE  ( NOT EXISTS  (SELECT bt.tid AS tid
FROM 
biblio_types bt
WHERE  (b.biblio_type = bt.tid) ))

...which is what I expect. If I place this statement verbatim in MySQL Query Browser I get the exact results I want. It returns exactly 9 rows of data. Perfect!

1

I think you'll need to use a where() for the condition in the subquery, PDO will most likely quote the 'tid' and mess it up.

$sub_query->where('bt.biblio_type = b.tid');

You can cast a SelectQuery to a string to view it's SQL equivalent at any time, it helps a lot with debugging the more complex queries.

print (string) $query;
  • I was actually just going to edit to change exists to notExists. – Lester Peabody Dec 6 '12 at 19:48
  • It's probably the subquery condition then, have a look at the SQL for the query after you've added the notExists(), it should give you a good insight into what's going on – Clive Dec 6 '12 at 19:51
  • Okay super weirdness going on... updating question. – Lester Peabody Dec 6 '12 at 19:51
  • I'm not completely in the clear yet, see my edit. Since you answered my original question I'll upvote and mark as accepted. – Lester Peabody Dec 6 '12 at 20:00
  • Hmm that's a strange one, I can't think of a reason the query wouldn't work in Drupal if it works in the browser. I'll let you know if anything comes to mind – Clive Dec 6 '12 at 20:08

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.