codeigniter4
Bug: shared BaseBuilder instance and Model
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. https://github.com/codeigniter4/CodeIgniter4/blob/de8d7f40d799d584d86af2945deac113e1a7c840/system/Database/BaseBuilder.php#L3396-L3400
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.
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().
@kenjis Good idea, but for some reason I am plagued by doubts. It seems that some nuance is slipping out of sight.
@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?
@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.
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.
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.
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.