-
nicolas33770
Junior Member
-
Posts: 41
Threads: 13
Joined: Nov 2019
Reputation:
0
05-04-2020, 02:51 PM
(This post was last modified: 05-05-2020, 03:18 AM by nicolas33770.)
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'); }
-
php_rocs
Administrator
-
Posts: 1,391
Threads: 78
Joined: Jun 2016
Reputation:
71
-
niklas
Junior Member
-
Posts: 13
Threads: 5
Joined: Mar 2020
Reputation:
0
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?
-
nicolas33770
Junior Member
-
Posts: 41
Threads: 13
Joined: Nov 2019
Reputation:
0
05-05-2020, 03:18 AM
(This post was last modified: 05-05-2020, 03:44 AM by nicolas33770.)
(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/datab...y-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
-
neuron
Member
-
Posts: 198
Threads: 39
Joined: Nov 2016
Reputation:
8
05-05-2020, 09:35 AM
(This post was last modified: 05-05-2020, 09:36 AM by neuron.)
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'); }
-
nicolas33770
Junior Member
-
Posts: 41
Threads: 13
Joined: Nov 2019
Reputation:
0
05-05-2020, 02:16 PM
(This post was last modified: 05-05-2020, 02:17 PM by nicolas33770.)
(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.
|