Advanced SQL DELETE syntax currently not supported bij Active Record

Advanced SQL DELETE syntax currently not supported bij Active Record - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21)
+--- Thread: Advanced SQL DELETE syntax currently not supported bij Active Record (/showthread.php?tid=7443)

Advanced SQL DELETE syntax currently not supported bij Active Record - El Forum - 04-09-2008

[eluser]mycroes[/eluser]
My database with company records doesn't allow for multiple changes at the same time by the same person (all fields form the primary key). When merging two companies together I can't just change the company id because that might generate a duplicate record (although it's really quite impossible that someone saved two company records at the same time). The solution is quite simple, I delete from the company with the higher id all the rows that conflict with the company with the lower id. The SQL syntax I was using was this (14 being the low id and 143 being the high id):

Code:
DELETE FROM company_change AS company1

USING company_change AS company1, company_change AS company2

WHERE company1.company_id = 143

AND company2.company_id = 14

AND company1.user_id = company2.user_id

AND company1.timestamp = company2.timestamp;

Another possible syntax which results in the same changes is the following:

Code:
DELETE company_change AS company1

FROM company_change AS company1, company_change AS company2

WHERE company1.company_id = 143

AND company2.company_id = 14

AND company1.user_id = company2.user_id

AND company1.timestamp = company2.timestamp;

I find the syntax with USING doesn't give me the feeling I'm deleting too much. Anyway, my attempt at getting the active record class to do just this was done this way (by me):

Code:
$this->db->from('company_change AS company1 USING company_change AS company1, company_change AS company2');

$this->db->where('company1.company_id', $high_id);

$this->db->where('company2.company_id', $low_id);

$this->db->where('company1.user_id = company2.user_id');

$this->db->where('company1.timestamp = company2.timestamp');

$this->db->delete();

Of course Active Record won't let me abuse db->from as I did here, so it fails. I looked into the active record code and noticed that the other SQL syntax won't work either.

Up until now I managed to do about everyhting with the active record class, except for one occassion where I needed to do a subquery and used the db->where with only one argument, which worked nice too. I'd like to do this one with the AR class too, but I'd rather not do it in 2 steps, which seems to be the only option now I think.

Anyway, I hope this bit of explanation proves useful to someone else and if someone manages to see another option that I could use then that'd be nice to know too.
Regards,

Michael