Deleting data with joins in 2 tables

[eluser]rupin[/eluser]
I have come across a rather confusing problem, and would like some one from the forum to provide assistance

Basically,I am trying to delete data from one table, and its has a join with another table, to make sure the correct row gets deleted.

Here is the code from the model that gets executed

$orgID=$this->session->userdata('orgID');
$this->db->where('questions.questionID', $questionID);
$this->db->where('questions.formID', $formID);
$this->db->join('forms', 'questions.formID = forms.formID');
$this->db->where('forms.orgID', $orgID);
this->db->delete("questions");

The SQL that I want to get executed is

delete
FROM (`questions`)
JOIN `forms` ON `questions`.`formID` = `forms`.`formID`
WHERE `questions`.`questionID` = '3'
AND `questions`.`formID` = '2'
AND `forms`.`orgID` = '2'

Unfortunately, CI throws an error while executing that it cannot find forms.orgID.When i checked the query by printing it,I see that the SQL from the join statement isnt egtting added to the ActiveRecord query...

This is the error that I get

--------------------------------------------------

A Database Error Occurred

Error Number: 1054

Unknown column 'forms.orgID' in 'where clause'

DELETE FROM `questions` WHERE `questions`.`questionID` = '3' AND `questions`.`formID` = '2' AND `forms`.`orgID` = '2'
------------------------------------------------------------------------

Is there a sequence of statements that needs to be followed for CI to understand how to create the SQL query?

Any help is much appreciated...

[eluser]slowgary[/eluser]
I usually try to follow the same order that I would if I were writing the SQL directly, with the exception of the "action" statement (INSERT, UPDATE, GET, DELETE, etc...). I'm pretty sure I've done a JOIN'd DELETE before using active record. Try putting the JOIN first.

[eluser]slowgary[/eluser]
On second thought, this really shouldn't matter what order they're in. The non "action" statements are just added to the active record object. It's not until one of the "action" statements that the previous statements are assembled and the query is executed.

There is currently no support in CodeIgniter for DELETE JOIN queries. I believe this is because some database engines do not support it. There's another thread discussing it: http://ellislab.com/forums/viewthread/63991/

[eluser]rupin[/eluser]
Thanks slowgary...
The workaround looks like running a select first with all the where conditions and joins- and later deleting just with where conditions from a single table based on the presence of a record..

It does sound logical though,

lets say I ran a "select * " with a join from 2 tables, then the result will have all columns from both tables.
Now if I had the same where conditions ahead of the "delete from", the database would probably be confused on which columns to delete..

I might be wrong

[eluser]slowgary[/eluser]
Rupin,

You are correct... it is logical and IT WORKS. In MySQL, you can indeed do:

Code:

DELETE *
   FROM whatever
   JOIN something_else
      ON whatever.id = something_else.whatever_id
   WHERE whatever.id = 150

The problem is that CodeIgniter's ACTIVE RECORD class doesn't support it.

[eluser]seanloving[/eluser]
Is there a plan to make AR support this MySQL syntax?