Using db_query and joins

Question

I am using Drupal 6 and trying to use db_query to delete rows from a table based on joins in order to untag a certain node of all tags of certain vocabulary.

This http://drupal.org/node/1079620 implies that delete doesn't work when using joins.

So I have a tried all the following forms of $queries:

1) $query = "DELETE a FROM {term_node} a LEFT JOIN {term_data} b ON a.tid = b.tid WHERE a.nid = %d AND b.vid = %d";

2) $query = "DELETE a FROM {term_node} a, {term_data} b WHERE a.tid = b.tid AND a.nid = %d AND b.vid = %d";

3) $query = "DELETE FROM a USING {term_node} AS a, {term_data} AS b WHERE (a.tid = b.tid) AND a.nid = %d AND b.vid = %d";

Before running

$result = db_query($query, $nid, $vid);

None of them deletes the rows from database. I have checked that $nid and $vid get the appropriate values and when I take of the curly brackets and run the query in phpMyAdmin it works like a charm.

I tried to perform simple query where there are no needs from joins. It didn't work either. But once again it works perfectly if I run the query in phpMyAdmin.

I started thinking could it be somehow related to the Drupal batch processing. The delete query not working gets executed when feeds module is performing a batch process (importing nodes to Drupal). So does Drupal during batch process somehow restrict access to tables or something? I didn't find anything in the mysql logs.

Answer 1

Some thing you can try doing is try printing the entire query string like,

$query = "DELETE a FROM {term_node} a LEFT JOIN {term_data} b ON a.tid = b.tid WHERE a.nid = ".$nid." AND b.vid = ".$vid;
print $query; //printing the query

Notice that I have used the variables instead of place holders %d. This might not be relevant but has helped me many times while debugging queries.

On the link you mentioned http://drupal.org/node/1079620, have you tried #5 ? if not give that a try, it should work IMO.

Answer 2

A thing that has bitten me in the past is PHP's "I'm not a typed language until I am a typed language," so throwing a cast into the mix, eg,

$result=db_query($query, (int) $nid, (int) $vid);

has solved some of these problems for me.

Answer 3

I am curious on the other answers coming, and too lazy to go test this out in dummy d7 instance, I would consider not using left joins with the delete operation.

I think I would try using 2 queries instead of a join. I just think it would be the next possible options short of using subqueries, but those don`t seem really well supported by D6 db_select anyhow. So the simplest approach when confronted one can try to divide and conquer. Just use 2 queries, I would consider as an alluring alternate approach.

I'll post working code example during the week-end if you really need one, but it's basically a 10 liner maybe 15 max.

Good-day, happy coding