Booyant Blog

CodeIgniter ActiveRecords Don’t Support Joins in Delete Statements

I love CodeIgniter’s ActiveRecords functionality. It makes programmatically building complex queries infinitely neater – and thus, more maintainable.

After years of using ActiveRecords, they have failed me for the first time.

ActiveRecords Ignores My Join

Here is what I was trying to do (in a simplified example):

What I’ve Got

I have two tables:

• table_1
• table_2

Table 1

• id (int)
• thing (varchar)
• table_2_id (field on which to join info from table_2)

Table 2

• id (int)
• thing (varchar)

What I want to do

I want to delete all rows from table_1, which have a joined table_2 with a “thing” value of “delete_me”.

Here is the query as I would write it by hand.

DELETE t1.*
FROM table_1 AS t1
LEFT OUTER JOIN table_2 AS t2 ON t1.table_2_id = t2.id
WHERE t2.thing='delete_me'

This works all day long, exactly as expected.

Testing ActiveRecords Ability to Delete Rows Based on Joined Data

Here is the test that I ran:

<?php
    // Let's ensure these queries are save
    $this->EE->db->save_queries = TRUE;

    $this->EE->db->from("table_1 as t1");
    $this->EE->db->join("table_2 AS t2", "t1.table_2_id = t2.id", "LEFT OUTER");
    $this->EE->db->where("t2.thing", "delete_me");
    $this->EE->db->delete("t1.*");

    // Output what ActiveRecords thinks I wanted
    echo $this->EE->db->last_query();
?>

And here is what ActiveRecords thinks I want:

DELETE FROM `t1`.* WHERE `t2`.`thing` = 'delete_me'

This of course:

• Is not what I want
• Throws errors because it references what MySQL thinks is an undeclared t2 table.

Conclusion

As far as I can tell – this is just something that is not supported in CodeIgniter’s ActiveRecords functionality. I’m not really complaining. It’s just as easy to write the query out by hand – but if anyone else out there is running into funky results while trying to do something similar, this might be helpful.