Querybuilder select() parsing problems

CodeIgniter 4.5.1
PHP 8.2

PHP Code:

$builder = $this->db->table('sales_items');
$builder->select("name, CONCAT(IFNULL(ROUND(percent, 2), 0), '%') AS percent, sales.sale_id AS sale_id, sales.foo AS subtotal");
$sub_query = $builder->getCompiledSelect(false); 


Generates

Code:

SELECT `name`, CONCAT(IFNULL(ROUND(percent, 2), 0), '%') AS percent, `ospos_sales`.`ospos_``sale_id` AS `sale_id`, `ospos_sales`.`foo` AS `subtotal`
FROM `ospos_sales_items`

The QueryBuilder parser is incorrectly appending the database prefix to the column name sale_id and it's escaping the column moniker after the 'AS' keyword in the second and third column names.

If I add `false` as the 2nd parameter in the select() call

PHP Code:

$builder = $this->db->table('sales_items');
$builder->select("name, CONCAT(IFNULL(ROUND(percent, 2), 0), '%') AS percent, sales.sale_id AS sale_id, sales.foo AS subtotal", false);
$sub_query = $builder->getCompiledSelect(false); 


It generates

Code:

SELECT name, CONCAT(IFNULL(ROUND(percent, 2), 0), '%') AS percent, `sales`.`ospos_sale_id` AS `sale_id`, sales.foo AS subtotal
FROM `ospos_sales_items`

For some reason it didn't get the memo about not escaping because it still escaped part of the query and it's now adding the database prefix to the column name but NOT adding it to the table name.

I tried removing the aliasing from the query, but that had no effect on the problems.

Removing the CONCAT SQL function call gets rid of the problems with prepending the prefix to the column but not the escaping problem

PHP Code:

$builder = $this->db->table('sales_items');
$builder->select("name, sales.sale_id AS sale_id, sales.foo AS subtotal", false);
$sub_query = $builder->getCompiledSelect(false); 


yields

Code:

SELECT name, `sales`.`ospos_sale_id` AS `sale_id`, sales.foo AS subtotal
FROM `ospos_sales_items`

Why is QueryBuilder->select() escaping these column and table names when the 2nd parameter of select is false?

Even wrapping the contents of the select() in a RawSql() instance still has it prepending the prefix in the wrong places.

Cannot reproduce.

PHP Code:

<?php

namespace App\Controllers;

class Home extends BaseController
{
    public function index(): string
    {
        $db = \Config\Database::connect();
        $builder = $db->table('sales_items');
        $builder->select("name, sales.sale_id AS sale_id, sales.foo AS subtotal", false);
        return $sub_query = $builder->getCompiledSelect(false);
    }
} 


I see:

Code:

SELECT name, sales.sale_id AS sale_id, sales.foo AS subtotal FROM `sales_items`

or

Code:

SELECT name, sales.sale_id AS sale_id, sales.foo AS subtotal FROM `db_sales_items`

ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper

Cannot reproduce.

PHP Code:

<?php

namespace App\Controllers;

class Home extends BaseController
{
    public function index(): string
    {
        $db = \Config\Database::connect();
        $builder = $db->table('sales_items');
        $builder->select("name, sales.sale_id AS sale_id, sales.foo AS subtotal", false);
        return $sub_query = $builder->getCompiledSelect(false);
    }
} 


I see:

Code:

SELECT name, sales.sale_id AS sale_id, sales.foo AS subtotal FROM `sales_items`

or

Code:

SELECT name, sales.sale_id AS sale_id, sales.foo AS subtotal FROM `db_sales_items`

@kenjis I figured out why you weren't able to reproduce. It turns out the issue is not with the initial query builder, but that I am using that subquery inside another query builder object and that one is mangling the generated subquery from the first.

PHP Code:

$subquery_builder = $this->db->table('sales_items');
$subquery_builder->select("name, sales.sale_id AS sale_id, sales.foo AS subtotal", false);
$sub_query = $subquery_builder->getCompiledSelect(false); 


produces

Code:

SELECT name, sales.sale_id AS sale_id, sales.foo AS subtotal FROM `ospos_sales_items`

which is what I expect but when I have:

PHP Code:

$builder = $this->db->table("($sub_query) AS temp_taxes");
$builder->select("name, percent, COUNT(DISTINCT sale_id) AS count, ROUND(SUM(subtotal), $decimals) AS subtotal, ROUND(SUM(tax), $decimals) AS tax, ROUND(SUM(total), $decimals) AS total");
$builder->groupBy('percent, name'); 


That generates:

Code:

SELECT `name`, `percent`, COUNT(DISTINCT sale_id) AS count, ROUND(SUM(subtotal), 2) AS subtotal, ROUND(SUM(tax), 2) AS tax, ROUND(SUM(total), 2) AS total
FROM (SELECT name, `sales`.`ospos_sale_id` AS `sale_id`, sales.foo AS subtotal
FROM `ospos_sales_items`) AS temp_taxes
GROUP BY `percent`, `name`

I thought perhaps I needed to wrap my SQL in a RawSql() instance, so I changed it to

PHP Code:

$builder = $this->db->table('(' . new RawSql($sub_query) . ') AS temp_taxes');
$builder->select("name, percent, COUNT(DISTINCT sale_id) AS count, ROUND(SUM(subtotal), $decimals) AS subtotal, ROUND(SUM(tax), $decimals) AS tax, ROUND(SUM(total), $decimals) AS total");
$builder->groupBy('percent, name'); 


However that produces the same result. I'm guessing this is because the 'outer' $builder acts on the query after the RawSql() instance completes it's work, so it just produces the same thing. The goal is to convert these complex queries which were using $this->db->query() to improve security but it seems that QueryBuilder has limitations with trying to run the resulting query of one instance as a sub query in another.

The example code at https://codeigniter.com/user_guide/datab...oin-rawsql implies that what I'm trying to do is possible, but the second querybuilder isn't leaving the results of the getCompiledSelect() alone.

---

Nevermind. It seems I need to use fromSubquery() (https://codeigniter.com/user_guide/datab...subqueries) to do this.