4

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.

  • I think the first thing to do is find out what query's actually being executed on the db (MySQL?). (You can do this eg. with devel.module or by adding a temporary dpm() in db_query() or _db_query().) – Andy Dec 12 '11 at 13:23
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.

  • I tried #5. It didn't help at this case. – jjei Dec 13 '11 at 11:32
0

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.

  • Didn't solve the problem in my case. – jjei Dec 13 '11 at 11:33
  • I updated my question. – jjei Dec 13 '11 at 11:47
0

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

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.