complex query failing |
Hi Team,
This query worked on CI v2 but fails on latest v3. $this->db ->select('`friends`.*, `user_to`.`id` AS `to_id`, `user_to`.`username` AS `to_username`, `user_to`.`online` AS `to_online`, `user_from`.`id` AS `from_id`, `user_from`.`username` AS `from_username`, `user_from`.`online` AS `from_online`, `user_to`.`last_activity` AS `to_last_activity`, `user_from`.`last_activity` AS `from_last_activity`, `friends_statuses`.name AS `friend_status`') ->join('`users` `user_to`', 'friends.to=user_to.id', 'left') ->join('`users` `user_from`', 'friends.from=user_from.id', 'left') ->join('`friends_statuses`', 'friends.status=friends_statuses.status_id', 'left') ->where('`friends`.`to`', $user_id) ->where('`friends`.`status`', FRIEND_ACCEPTED) ->or_where('`friends`.`from`', $user_id) ->where('`friends`.`status`', FRIEND_ACCEPTED); if ($limit) { $this->db->limit($limit - $pending_requests->num_rows()); } //Prevent back ticks $this->db->_protect_identifiers = false; $confirmed_friends = $this->db ->order_by(" IF(`user_to`.`id` = '".(int) $user_id."', `user_from`.`online`, `user_to`.`online`)", 'DESC') ->get('friends'); The first error is to do with the _protect_identifiers and I have read some comments and also tried adding protect_identifiers(false) after $this->db (ie $this->db->protect_identifiers(false) ). Still fails with problem on the order_by line. Reading a bit further, I cannot find any suggestions on how to include the sql IF statement inside the order_by function. I have spent hours fiddling with quoting without success, so I am conceding and asking for some help please! I hasten to add I didn't write this magic and don't profess to be a query master :-( I'm also aware I can write SQL and bypass all the CI functions, but would like to get it working this way if its a simple fix. Thx as usual, Paul
What error message do you get?
Can you replace ->get('friends') with ->get_compiled_select('friends') and give us the complete SQL generated. Remove: $this->db->_protect_identifiers = false; If you pass a third parameter into order_by() it won't try to escape it. Code: ->order_by(" IF(`user_to`.`id` = '".(int) $user_id."', `user_from`.`online`, `user_to`.`online`)", 'DESC',FALSE) AND escape it manually. https://www.codeigniter.com/user_guide/d...ng-queries Code: ->order_by(" IF(`user_to`.`id` = ". $this->db->escape(intval($user_id)) .", `user_from`.`online`, `user_to`.`online`)", 'DESC',FALSE)
Some more magic!
It works now. Looks like the embedded single quotes was the problem. I didnt use the compiled select, but added the 3rd parameter, and changed the quoting of the user_id as suggested. Many thanks. |
Welcome Guest, Not a member yet? Register Sign In |