Bug: shared BaseBuilder instance and Model

[iRedds]

Describe the bug
Wrong approach to implementing a generic BaseBuilder instance in the model and post processing queries to the database.
When using a subquery, the work of the model / builder breaks.

$t = new Test(); //table test

$t->from('(SELECT * FROM test_1) t', true)->paginate(1);
d($t->db->getLastQuery()); // "SELECT * FROM (SELECT * FROM test_1) t LIMIT 1"

$t->findAll();
d($t->db->getLastQuery()); // "SELECT * FROM (SELECT * FROM test_1) t"  expected SELECT * FROM test

$t->from('(SELECT id, name FROM test_1) t', true)->paginate(1);
d($t->db->getLastQuery()); // "SELECT * FROM (SELECT id, name FROM test_1) t LIMIT 1"

$t->findAll(); // SQL error -  SELECT * FROM (SELECT id

The fact is that after the query is executed, the table is reassigned.

CodeIgniter4/system/Database/BaseBuilder.php

Lines 3396 to 3400 in de8d7f4

	// Reset QBFrom part
	if (! empty($this->QBFrom))
	{
	$this->from(array_shift($this->QBFrom), true);
	}

For the first case, the value will be: (SELECT * FROM test_1) t
For the second case: (SELECT id, because the from() method can accept as a table name a string consisting of several tables separated by commas: from('table_1, table_2, table_3)

Affected module(s)
Model, BaseBuilder

You can fix this with crutches, but I think it's time to change the concept of the model and query builder.

[kenjis]

It seems the table reassignment is come from #2800

Related: #4457

[lonnieezell]

Are you trying to override the table given in the model? I'm assuming that's what you're doing. And that goes against how the Models are designed to work. The problem is I don't see what it is you're trying to have happen - you just point out how it doesn't do what you're expecting.

You can always grab a new QueryBuilder with either $this->db->table('test_1')->paginate() or $this->builder('test_1)->paginate()`. Both of these assume you're within the model.

If you're outside of the model trying to use that query, then you can get it using something like db_connect()->table('test_1')->paginate().

[iRedds]

BaseBuilder has no paginate() method. It is only defined in BaseModel()

[kenjis]

@iRedds How about PR #4463 ?

[iRedds]

@kenjis Good idea, but for some reason I am plagued by doubts. It seems that some nuance is slipping out of sight.

[kenjis]

@iRedds
Specifying sub query in from() is not documented in the User Guide in CI3/CI4.

$builder->from('(SELECT id, name FROM test_1) t', true);

$from (mixed) – Table name(s); string or array
https://codeigniter4.github.io/CodeIgniter4/database/query_builder.html#from

'(SELECT id, name FROM test_1) t' is not table names.

So it seems this is simply misuse and it is not a bug.
Is it a common use case around you?

[iRedds]

@kenjis
I am not using CI4.
Yes, this is not a bug.
But I believe this cuts off the functionality.
In the case I described, in order not to break the work of the model, you will have to write a raw SQL query.

[kenjis]

@iRedds Is this issue still there?

[iRedds]

Yes. Nothing changed.
Is it relevant for me? No.
As I wrote earlier, I don't use CI4.

I won't mind if this issue is closed.

[kenjis]

Thanks. I forgot the details of the issue, but I thought recent development of Query Builder might change this.

As I sent a PR, I thought there was a problem.
So you don't need to close this.
I would like to fix someday.

[iRedds]

A solution may emerge from a discussion where all parties have an understanding of the situation or a desire to understand it. Because a possible solution may be at the level of the concept of working with a model and a database.