+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: Best Practices (https://forum.codeigniter.com/forumdisplay.php?fid=12)
+--- Thread: Delete with Join or Where_in not working (/showthread.php?tid=76340)
Delete with Join or Where_in not working - nicolas33770 - 05-04-2020
Hello,
I would like to know the best way to delete with Query builder and join (not working).
This select with join working good with Query Builder and I would like to use the same way to delete but is not working. How I can do that ?
PHP Code:
//WORKS FINE
public function get_services($profile_id, $location_id)
{
$this->db->select('s.*,l.location_id,l.profile_id');
$this->db->from('service s');
$this->db->join('location l', 's.location_id = l.location_id', 'left');
$this->db->where('s.location_id', $location_id);
$this->db->where('l.profile_id', $profile_id);
$this->db->order_by("s.service_title", "ASC");
$query = $this->db->get();
return $query->result();
}
PHP Code:
// NOT WORKING
public function delete_service($location_id,$service_id,$profile_id)
{
$this->db->from('service s');
$this->db->join('location l', 's.location_id = l.location_id', 'left');
$this->db->where('s.service_id', $service_id);
$this->db->where('s.location_id', $location_id);
$this->db->where('l.profile_id', $profile_id);
return $this->db->delete('service s');
}
PHP Code:
// NOT WORKING
public function delete_service($location_id,$service_id,$profile_id)
{
$this->db->select('s.*,l.location_id,l.profile_id');
$this->db->from('service s');
$this->db->join('location l', 's.location_id = l.location_id', 'left');
$this->db->where('l.profile_id', $profile_id);
$this->db->where('s.location_id', $location_id);
$this->db->where('s.service_id', $service_id);
$where_clause = $this->db->get_compiled_select();
$this->db->where_in('s.location_id', $where_clause);
return $this->db->delete('service s');
}
RE: Delete with Join or Where_in not working - php_rocs - 05-04-2020
@nicolas33770,
Have you tried using query binding (https://codeigniter.com/userguide3/database/queries.html#query-bindings)? It may work better for you.
RE: Delete with Join or Where_in not working - niklas - 05-05-2020
Hi,
I don't know if its a copy & paste failure but you provide your not working functions two times with $service_id.
Did you changed one of them to $location_id?
RE: Delete with Join or Where_in not working - nicolas33770 - 05-05-2020
(05-05-2020, 02:04 AM)niklas Wrote: Hi,
I don't know if its a copy & paste failure but you provide your not working functions two times with $service_id.
Did you changed one of them to $location_id?
Thanks, yes its copy & paste failure, I fixed that
(05-04-2020, 08:22 PM)php_rocs Wrote: @nicolas33770,
Have you tried using query binding (https://codeigniter.com/userguide3/database/queries.html#query-bindings)? It may work better for you.
Thanks for your reply but I would like use join with Query builder on DELETE. I don't understand how this feature could help me? But if I understand correctly it is not possible to use Join queries with query Builder on DELETE. so I have to make my request myself?
I use query binding with my own Query and it's working but is not even simple than Query builder. :-)
Thanks for your help
RE: Delete with Join or Where_in not working - neuron - 05-05-2020
1. You should do inner join in this case instead of left join (unless $profile_id can be null).
2. In MySQL if you specifying alias for table name you should use alias in delete statement not a table name.
So try this:
PHP Code:
public function delete_service($location_id,$service_id,$profile_id)
{
$this->db->from('service s');
$this->db->join('location l', 's.location_id = l.location_id');
$this->db->where('s.service_id', $service_id);
$this->db->where('s.location_id', $location_id);
$this->db->where('l.profile_id', $profile_id);
return $this->db->delete('s');
}
RE: Delete with Join or Where_in not working - php_rocs - 05-05-2020
@nicolas33770,
With query binding you can do any query and it will work. I prefer it because it more or less goes directly to the db. I have very strong db skills so I prefer it over having the framework build it for me.
RE: Delete with Join or Where_in not working - nicolas33770 - 05-05-2020
(05-05-2020, 09:35 AM)neuron Wrote: 1. You should do inner join in this case instead of left join (unless $profile_id can be null).
2. In MySQL if you specifying alias for table name you should use alias in delete statement not a table name.
So try this:
PHP Code:public function delete_service($location_id,$service_id,$profile_id)
{
$this->db->from('service s');
$this->db->join('location l', 's.location_id = l.location_id');
$this->db->where('s.service_id', $service_id);
$this->db->where('s.location_id', $location_id);
$this->db->where('l.profile_id', $profile_id);
return $this->db->delete('s');
}
Thanks for your replay, I tried but seems join is ignored in final query. Don't working.
(05-05-2020, 12:42 PM)php_rocs Wrote: @nicolas33770,
With query binding you can do any query and it will work. I prefer it because it more or less goes directly to the db. I have very strong db skills so I prefer it over having the framework build it for me.
Yes of course. Thank you.
