Codeigniter deleting data with joins tables

Question

Logically in SQL we can delete data from tables with JOINS e.g.

DELETE  clb_subscriber_group_mapping .* FROM clb_subscriber_group_mapping 
INNER JOIN clb_driver_group ON (clb_driver_group.id = clb_subscriber_group_mapping.driver_group_id)
INNER JOIN clb_company ON (clb_company.id = clb_driver_group.company_id)
WHERE clb_company.id = 256 AND clb_subscriber_group_mapping.subscriber_id = 1784;

What will be the CodeIgniter equivalent of above query?

Does CodeIgniter support Delete query with joins?

score 4 · Answer 1 · answered May 14 '12 at 10:14

Do you have to use Active Records?

This below query will do otherwise.

$int_company_id = 256;
$int_subscriber_id = 1784;

$this->db->query("
DELETE  clb_subscriber_group_mapping .* FROM clb_subscriber_group_mapping 
INNER JOIN clb_driver_group ON (clb_driver_group.id = clb_subscriber_group_mapping.driver_group_id)
INNER JOIN clb_company ON (clb_company.id = clb_driver_group.company_id)
WHERE clb_company.id = ? AND clb_subscriber_group_mapping.subscriber_id = ?;

", array($int_company_id, $int_subscriber_id));

Hey I dont wanna use query() method. I wanna use $this->db->join(), $this->db->delete() Got my point? — Jatin Dhoot, May 14 '12 at 10:24

score 4 · Answer 2 · answered May 14 '12 at 10:48

You can't do that with CodeIgniter's Active Record class. It doesn't support joins in delete query. You'll have to execute the query using $this->db->query() as mentioned by Robin Castlin.

The below code is taken from the core files. It is one of the inner components, that generates the DELETE query.

function _delete($table, $where = array(), $like = array(), $limit = FALSE)
{
    $conditions = '';

    if (count($where) > 0 OR count($like) > 0)
    {
        $conditions = "\nWHERE ";
        $conditions .= implode("\n", $this->ar_where);

        if (count($where) > 0 && count($like) > 0)
        {
            $conditions .= " AND ";
        }
        $conditions .= implode("\n", $like);
    }

    $limit = ( ! $limit) ? '' : ' LIMIT '.$limit;

    return "DELETE FROM ".$table.$conditions.$limit;
}

As you can see, there's nothing in there that specifies the insertion of a JOIN clause.

Yes, but it should be, Hopefully someone from CI is listening — Jatin Dhoot, May 15 '12 at 08:31
Well why don't you take it to their forums (http://codeigniter.com/forums/) then? Or, perhaps their bug tracker (https://github.com/EllisLab/CodeIgniter/issues)? — Kemal Fadillah, May 15 '12 at 11:36

score 1 · Answer 3 · answered Jan 25 '17 at 14:38

I had the same problem, the join was ignored:

$r1 = $db->where("object2_type", $object_type)
->join($object_type, "$object_type.id = object1_id", "LEFT")
->where("$object_type.id IS NULL", null, false)
->delete("knots");

so I did it like this:

$ids = $db->select("knots.id")
->where("object2_type", $object_type)
->join($object_type, "$object_type.id = object1_id", "LEFT")
->where("$object_type.id IS NULL", null, false)
->get("knots")->result_object();
/* my ow function which generates a string like '1','2','3' or 0 */    
$ids_csv = $this->mh()->get_flat_items_as_csv($ids);
$r = $db->where("knots.id IN ($ids_csv)", null, false)->delete("knots);

Márton Tamás · Answer 4 · 2017-03-17T10:11:19.763

Instead of rewriting the whole SQL, You can do like this:

// build query as usual...
$this->db
    ->from ('cookies')
    ->join ('recipes', 'recipes.cookie = cookies.id')
    ->where ('recipes.rating', 'BAD');

/* 
 * get the original DELETE SQL, in our case:
 * "DELETE FROM `cookies` JOIN `recipes` ON `recipes`.`cookie`=`cookies`.`id` WHERE `recipes`.`rating`='BAD'"
 */
$sql = $this->db->get_compiled_delete ('cookies');

/*
 * insert the target table in the SQL string, to get this:
 * "DELETE `cookies` FROM `cookies` JOIN `recipes` ON `recipes`.`cookie`=`cookies`.`id` WHERE `recipes`.`rating`='BAD'"
 */
$target = $this->db->escape_identifiers ('cookies'); // just to be safe
$sql = substr_replace ($sql, " $target", 6, 0); // note the prepended space

// now we can run the query
$q = $this->db->query ($sql);

This way, You keep the good stuff from Query Builder (a.k.a. Active Records) and have Your freedom of customization as well. I think, it's quite safe, since the $sql string will always start with DELETE. You can wrap this into a function as a shortcut, of course.

Codeigniter deleting data with joins tables

4 Answers4

Linked