Wrong query generated when used "dbprefix" - 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: Wrong query generated when used "dbprefix" (/showthread.php?tid=70786) |
Wrong query generated when used "dbprefix" - Vitaly83 - 05-29-2018 Hi. I noticed that when is used the dbprefix option DB library generates wrong query. I'm refactoring a site and new tables starts with v2_ prefix. Mostly it works fine, but sometimes... Here is an example of my query: $from = $this->db ->select('mi.id') ->select('count_1 AS count') ->select('custom_1 AS custom') ->select('o.id AS order_id') ->from('orders o') ->join('menu m', 'o.menu_id = m.id') ->join('menu_items mi', 'mi.id = m.dish_id') ->where('mi.id', $menu_item_id) ->where('o.employee_id', $employee_id) ->limit(3) ->get_compiled_select(); return $this->db ->select('id') ->select('count') ->select('custom') ->select('COUNT(order_id) cnt') ->from('(' . $from . ') t') ->group_by('count') ->group_by('custom') ->order_by('cnt', 'desc') ->get() ->row_array(); I get an database error with such query: Code: SELECT `id`, `count`, `custom`, COUNT(order_id) cnt FROM (SELECT `mi`.`id`, `v2_``count_1` AS `count`, `v2_``custom_1` AS `custom`, `v2_orders`.`id` AS `order_id` FROM `v2_orders` `o` JOIN `v2_menu` `m` ON `v2_orders`.`menu_id` = `m`.`id` JOIN `v2_menu_items` `mi` ON `mi`.`id` = `m`.`dish_id` WHERE `mi`.`id` = '12' AND `v2_orders`.`employee_id` = '19' LIMIT 3) t GROUP BY `count`, `custom` ORDER BY `cnt` DESC Here is compiled select from $from variable: Code: SELECT `mi`.`id`, `count_1` AS `count`, `custom_1` AS `custom`, `v2_orders`.`id` AS `order_id` FROM `v2_orders` `o` JOIN `v2_menu` `m` ON `v2_orders`.`menu_id` = `m`.`id` JOIN `v2_menu_items` `mi` ON `mi`.`id` = `m`.`dish_id` WHERE `mi`.`id` = '12' AND `v2_orders`.`employee_id` = '19' LIMIT 3 Is it bug or I should do some changes in my code? -- UPD: There is another strange thing: first and last column are not processed. For example, if I swap first and second columns, then will get this result: Code: SELECT `count_1` AS `count`, `mi`.`v2_``id` |