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`
