CodeIgniter Forums
complex query failing - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: complex query failing (/showthread.php?tid=70623)



complex query failing - PaulC - 05-05-2018

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


RE: complex query failing - jreklund - 05-05-2018

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/database/queries.html#escaping-queries
Code:
->order_by(" IF(`user_to`.`id` = ". $this->db->escape(intval($user_id)) .", `user_from`.`online`, `user_to`.`online`)", 'DESC',FALSE)



RE: complex query failing - PaulC - 05-05-2018

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.