• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Wrong query generated when used "dbprefix"

#1
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`
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.