CTE with Query Builder

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

If your only going to add a few methods to the class then just extend it and add your methods.

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

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!

Support Development •  CodeIgniter 4 Foundations • Practical CodeIgniter 3  • CodeIgniter Tutorials

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.