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 Answer 1

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;
6
  • I was actually just going to edit to change exists to notExists. Dec 6, 2012 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, 2012 at 19:51
  • Okay super weirdness going on... updating question. Dec 6, 2012 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. Dec 6, 2012 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, 2012 at 20:08

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.