Query Builder: Table alias gets DB prefix when using subquery

Hello,

I have two similar queries. The difference is that the second one has an extra subquery in it.

The first query gets compiled nicely, in the second one, however, the table aliases in the main query get DB prefixes.

Looks like the extra subquery causes this weird problem, although it's not supposed to act on the main query.


Here's the first, correct query:

SELECT `m`.`id`, `group_id`, `name`, `label`, `pos`
    FROM `is_menu` `m`
        LEFT JOIN `is_menu_lang` `l` ON `l`.`id` = `m`.`id`
    WHERE `m`.`group_id` = '3' AND `inactive` = 0
    ORDER BY `pos`


Here's the second, messed up query, where "is_" is the DB prefix:

SELECT `is_m`.`id`, `group_id`, `name`, `label`, `pos`,
    (
        SELECT 1
        FROM `is_menu` `s`
        WHERE `s`.`group_id` = `m`.`id`
    ) `has_tree`
    FROM `is_menu` `m`
        LEFT JOIN `is_menu_lang` `l` ON `l`.`id` = `is_m`.`id`
    WHERE `is_m`.`group_id` = '4' AND `inactive` = 0
    ORDER BY `pos`


The way I built the query is probably not relevant, but here it is for the sake of completeness:

PHP Code:

if (!$table) $table = 'menu';

$select = 'm.id, group_id, name, label, pos';

$builder = $this->db
    ->table($table.' m')
    ->select($select);
 
if (!$more_levels_to_go) {
    $subquery = $this->db->table('menu s')
        ->select('1', false)
        ->where('s.group_id = m.id');
    $builder
        ->selectSubquery($subquery, 'has_tree');
}

$builder
    ->join($table.'_lang l', 'l.id = m.id', 'left')
    ->where('m.group_id', $id)
    ->where('inactive', 0)
    ->orderBy('pos'); 


Thanks.

@leafface ,

So what is your question?

There are multiple ways you could do this query. I can think of two more ways right off the top of my head.

@leafface ,

So what is your question? 

There are multiple ways you could do this query.  I can think of two more ways right off the top of my head.

My question is how to avoid the DB prefix being added to the table alias in the second query.

I can also think of another way to do this query, using distinct + join instead of subquery, that would be my last resort.

this is a bug

---

As a temporary solution you can use object cloning

PHP Code:

$subquery = (clone $this->db)->table('menu s') 


Perhaps the fix will be included in the next bugfix release 4.2.5.
https://github.com/codeigniter4/CodeIgniter4/pull/6390

this is a bug

---

As a temporary solution you can use object cloning

PHP Code:

$subquery = (clone $this->db)->table('menu s') 


That'll do it till the update. Many thanks.