+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: CTE with Query Builder (/showthread.php?tid=89098)
CTE with Query Builder - ltarrant - 01-08-2024
Hi,
I have some quite complex queries to which I would like to introduce some Common Table Expressions to improve performance and simplify.
The existing queries are already written using the query builder with the benefit this brings of built-in injection protection and simplicity for applying business logic to build the query. So I am quite keen to still utilise the query builder and not manually build SQL strings to execute directly as a query.
Can any one point me in the right direction of how I might go about extending the BaseBuilder (taking a quick glance I think that's the class I should be looking at) to enable CTE support.
I was thinking along the lines of a new function that accepts a parameter with the CTE in the form of an instance of the BaseBuilder or RawSql to add after a WITH clause at the start of the subsequent query.
I fear it might be beyond my understanding framework setup but you never know.
Thanks,
Luke
RE: CTE with Query Builder - InsiteFX - 01-08-2024
If your only going to add a few methods to the class then just extend it and add your methods.
RE: CTE with Query Builder - kilishan - 01-08-2024
It has been a while since I've dove deep into the guts of the database layer, but that's one portion that isn't easily extendable currently, unfortunately.
Unless someone else has a better idea, I would focus on adding some methods to a BaseModel class, or using a trait you can add to all your models that need it.
You should be able to use the Query Builder's getCompiledSelect, getCompiledInsert, getCompiledUpdate, getCompiledUpsert, and getCompiledDelete methods from within the model's query chain just fine.
Hopefully that gets you pointed in the right direction.
NOTE: I wasn't actually familiar with CTEs until you pointed this out, so thanks for helping me learn something new today!
RE: CTE with Query Builder - ltarrant - 01-08-2024
Thanks for the comments.
I think extending the BaseModel is more within my grasp. Simply using the QB getCompiledSelect and prefixing my WITH clauses should work.