Codeigniter deleting data using join tables

Question

Logically in SQL, we can delete data from tables using JOINS, for example.

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 equivalent of CodeIgniter of the above request?

Does CodeIgniter support query removal using connections?

Answer 1

Do you need to use Active Records ?

The request below 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)); 

Answer 2

You cannot do this with the Active Record CodeIgniter class. It does not support joins in a delete request. You will need to complete the query using $this->db->query() , as mentioned by Robin Kastrin.

Below is the code from the main files. This is one of the internal components that generates a DELETE request.

 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 is nothing that indicates the insertion of a JOIN clause.

Answer 3

I had the same problem, the connection 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); 

Answer 4

Instead of rewriting all of SQL, you can do the following:

 // 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 things from Query Builder (active Ak.k.a. entries) and have your freedom of customization. I think this is completely safe, since the $sql line always starts with DELETE . Of course, you can wrap this in a function as a shortcut.