Codeigniter Cannot Delete Rows from Database Table Using Join Method

Question

I want to delete from ‘table1’ those rows where (user_id = 5) but I should check if those posts’ (title = title1 in table2). I use Codeigniter and I get this error while trying to delete: ‘Deletes are not allowed unless they contain a "where" or "like" clause.’ Could you please help me to check what is wrong with my code below.

table1:

table2:

public function delete($title, $user_id){ 

    $this->db->select('table1.*');
    $this->db->from('table1','table2');   
    $this->db->where('table1.user_id', $user_id); 
    $this->db->where('table2.title', $title);
    $this->db->join('table2','table1.post_id=table2.post_id');

     $query = $this->db->get();   

        if ($query && $query->num_rows() > 0) {

    $this->db->delete('table1.*');
    $this->db->from('table1','table2');   
    $this->db->where('table1.user_id', $user_id); 
    $this->db->where('table2.title', $title);
    $this->db->join('table2','table1.post_id=table2.post_id');
    return true;
            } 
    else {
    return false;
    }

   }

Accepted Answer

Make use of subqueries.

example

#Create where clause
$this->db->select('id');
$this->db->from('table2');
$this->db->where('table2.title', $title);
$where_clause = $this->db->get_compiled_select();

#Create main query
$this->db->where('table1.user_id', $user_id); 
$this->db->where("`id` NOT IN ($where_clause)", NULL, FALSE);
$this->db->delete('table1');

Must Read

Spring Batch Jpaitemwriter vs Hibernateitemwriter and Why Hibernatetransactionmanager Is Needed When Hibernateitemwriter Is Used
Different Font Sizes in the Same Annotation of Matplotlib?

References

stolen from here:
about subqueries:
compiled select:

Answer 2

After running first query you will get the set of user that you have to delete. Run this set throw foreach loop for getting id of user and posts that you have to delete to an array.

  $user_array = array();
  $post_array = array();

  foreach($query->result() as $query) 
  {
      $user_array[$query->user_id] = $query->user_id;
      $post_user[$query->post_id] = $query->post_id;
  }

And then

  this->db->where_in('user_id', $user_array)->delete('table1');
  this->db->where_in('post_id', $post_array)->delete('table2');

I know that this is not the best decision. But i think that this is the most understandable.

Answer 3

You may use the following code to delete data from tables becasue codeigniter ignore join when you delete multiple data from multiple tables.

$sql = "DELETE t1 FROM table1 t1
  JOIN table2 t2 ON t1.thing_id = t2.id
  WHERE t2.otherthing_id = ?";
$this->db->query($sql, array($id));

Answer 4

JOINS are used to fetch data from database not used to delete data if you want to delete data from multiple table using single query then you need to use cascading in MySQl using that you can delete data also from other tables that are related to current table.

Codeigniter Cannot Delete Rows from Database Table Using Join Method

4 Answers

example

Must Read

References

Robert Thorne