Better way to extend BaseBuilder

[Martin-4Spaces]

I'm extending the functionality of CI4's BaseBuilder to handle sub-queries like this:

$isStarredQuery = new UserStarModel();
$isStarredQuery
        ->selectCount('*', 'count')
        ->where('user_id', Client::$user->id)
        ->where('starred_user_id', '${parent}.id', false);

$userModel = new UserModel();
$userModel
        ->select('*')
        ->selectSubQuery($isStarredQuery, 'isStarred')
        ->havingIn('isStarred', [0, null]);

In this use case, i'm trying to fetch all users who I have not already "starred".
Anyway, the use case is not important. The idea is to combine multiple queries. This feature is not that hard to accomplice.

Imagine a function public function selectSubQuery($query, $alias) where $quey is an instance of Model.

Step 1: Replace ${parent} with the table name of $this in all $query->binds
Step 2: Merge $query->binds with $this->binds
Step 3: $sql = $query->compileSelect()
Step 4: Do some str_replacing in $sql to handle conflicting table names
Step 5: $this->select("{$sql} AS {$alias});

I've implemented this feature in my own code and it works. The issue is BaseBuilder is not extendable. So I cannot modify the binds-array in a proper way. I have to create my own DB-driver to extend BaseBuilder. Furthermore, I cannot call compileSelect in BaseBuilder because it is a protected method.
My issue is solved for now by copying a database driver and make the changes there. But I think we should be able to extend more database classes. :)

[lonnieezell]

Being able to extend the database classes is a known issue. Something that will be addressed down the road for sure.

If you look over the Query Builder docs there's a number of places where you can use subqueries as an anonymous function. I take it these don't work for your use case?

[najdanovicivan]

This will be very useful to be supported properly but there are a lot of issues how to handle this properly. Problem is that database builders extends the BaseBuilder an if you need to extend it you need to extend the Builder for specific database

For example for MySQL you'll have to extend CodeIgniter\Database\MySQLi\Builder

[najdanovicivan]

The only idea that comes on my mind at the moment is to use Trait for the extensions methods and extend each of the database builders so they use the Trait and use the extended classes for querying the db

[MGatner]

I can't discern how this is different than Query Grouping (https://codeigniter4.github.io/CodeIgniter4/database/query_builder.html?highlight=subquery#query-grouping) that Lonnie linked. @najdanovicivan or @Martin-4Spaces can you elaborate on what MySQL output you would like from sub-queries that is not covered?

The larger issue of BaseBuilder I believe is beyond the scope of an Issue at this point, maybe when NoSQL support is added.

[yassinedoghri]

Hey @MGatner, following your question, I'm stumbling on a limit with the query builder: sub queries are only implemented for the WHERE clause. I'm having a use case where I need to use a sub query in a FROM clause. I don't seem to find a way to implement that with the Query Builder in the docs. Am I missing something?

[kenjis]

@yassinedoghri See #5510

[kenjis]

Please submit feature requests to our forum.
We use github issues to track bugs and planned work.