Welcome Guest, Not a member yet? Register   Sign In
Wrong query generated when used "dbprefix"

(This post was last modified: 05-31-2018, 03:46 AM by Vitaly83.)

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('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)

return $this->db
   ->select('COUNT(order_id) cnt')
   ->from('(' . $from . ') t')
   ->order_by('cnt', 'desc')

I get an database error with such query:

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:

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:
SELECT `count_1` AS `count`, `mi`.`v2_``id`

Theme © iAndrew 2016 - Forum software by © MyBB