CodeIgniter 4 Database Features Overview - 2022
CodeIgniter 4 continues to deliver a solid PHP development framework in 2022 and beyond. Many thanks to all the developers who build and ship such a great product.
I’m so grateful you are here reading OpenLampTech! 👍
Thank you!!!
If someone awesome shared this newsletter with you and you are not yet subscribed, please use the Subscribe button below and join:
Cover background image by 0fjd125gk87 from Pixabay
As a whole, the CodeIgniter framework team shipped many upgrade releases during the 2022 year and I don’t by any means wish to ignore or gloss over any of them.
However, in this OpenLampTech report, I am sharing my thoughts on the most significant (to me), database-related enhancements, fixes, and upgrades released over the 2022 year for CodeIgniter 4.
In 2022, we saw 15 overall releases (version 4.1.6 to version 4.2.10 ). 👏👏👏
Thank you CodeIgniter team!
OpenLampTech has top-level sponsorship opportunities in the weekly newsletter for your brand, product, or service. As a starter publication, collaboration is very budget-friendly. Inquire by email at openlamptech@substack.com for more information.
Version 4.1.6
Release Date: January 3, 2022
Notable Database Improvements:
Subqueries in
QueryBuilder
can now be an instance of theBaseBuilder
class.
Although I haven’t (yet) used it (in production at least), the $db->newQuery()
method can be useful in those instances where you are selecting data from a Derived table in the FROM
clause.
Ex: SELECT * FROM (SELECT query here) `alias`
.
Note: The Derived Table alias parameter is supported in the $db->newQuery()
method call.
Version 4.2.0
Release Date: June 3, 2022
Notable Database Improvements:
Support for Oracle Database with the OCI8 driver
Query Builder Subqueries
Query Builder
UNION
supportRaw SQL string support
1) Oracle Database Support
I am working with the Oracle database a great deal in my day job. Also, I am excited to see CodeIgniter 4 database support as I am (eventually) going to port over a vanilla PHP application and will be using Oracle Database for spatial queries and functionality. Many people hate Oracle but go ahead - that database is robust!
The Always Free Autonomous database, is a win-win.
2) UNION and UNION ALL Support
Used in the correct query context, the UNION
, UNION ALL
, INTERSECT
, and EXCEPT
set operators can be quite powerful. MySQL v8 recently implemented support for the INTERSECT
and EXCEPT
set operators (read this high-level overview I wrote on my blog).
Now in CodeIgniter 4, we can use the UNION
and UNION ALL
set operators in the Query Builder queries with dedicated methods.
Where $builder->union()
returns those distinct rows from 2 or more SELECT
queries, $builder->unionAll()
returns all rows, including any duplicates.
Interestingly enough, the individual SELECT
queries involved in the UNION
operation will query data from their own derived table.
So in the end, this is UNION
-like support in order to correct query functionality with other database vendors.
I wrote an introductory article over on my Digital Owl’s Prose developer blog about the $builder→union() and $builder→unionAll() methods you can read for more information and example queries.
3) Subquery Support
I think scalar subqueries as part of the SELECT
statement list, are one of the more powerful standard SQL features. Since this particular type of subquery is aware of the outer query, you can even use it for LEAD()
and LAG()
window function-type queries if you're not so lucky in that your SQL implementation doesn't support them (Window Functions).
In the $builder->selectSubquery()
call, you can provide a 2nd parameter for a column alias as well.
On the same token, we now have support for subqueries in the FROM
clause using the $builder->fromSubquery()
method. You can also use $builder->fromSubquery()
in conjunction with the $db->newQuery()
method and make the subquery, the main query if desired.
📰 Get your brand, product, or service the attention it deserves with affordable classified ad placement in the OpenLampTech newsletter.
4) New RawSql class implementation
With the addition of the RawSql
class, you can now pass in raw SQL strings to several functions and methods. This allows for more complex SQL strings (e.g., REGEXP
, column definitions, more involved WHERE
conditionals, etc…)
The following query builder methods can accept an instance of the RawSql
class:
select()
where()
like()
join()
In addition to the listed Query Builder methods, the Database Forge class addField()
method can receive a RawSql
instance as a parameter.
As noted in the documentation, all RawSql strings must be manually escaped.
Version 4.2.2
Release Date: August 6, 2022
Breaking Changes
Prior to this update, both the BaseBuilder
insert()
and update()
methods received an array()
$set
parameter. That type-hint has been removed for both of these methods.
In Closing
Again, as I stated earlier in this report, these are the most significant features that caught my eye over the 2022 year for database enhancements and improvements.
It is by no means a summary of all CodeIgniter 4 upgrades this year.
Visit the official CodeIgniter 4 Change Logs for all release information.
[#Classifieds and Click Ads]
🤝 OpenLampTech earns a small commission (in some form) from many of these links. Your support helps keep the weekly OpenLampTech newsletter free for readers.🙏
Subscribe to The Uncommon Solopreneur and learn how solopreneurs grow their businesses without working themselves into the ground.
Get smarter every single day by reading Refind. I read Refind every morning because Refind tailors the content to my interests.
Find your next favorite newsletter in the Sample. Start reading more great newsletters today.
The Selfish Forever newsletter is a must-have if you're even remotely (pun intended) interested in making your own way in this world. The copywriting alone is going to blow your socks off. 🧦
Thank you for reading. I hope you have a great rest of your week.
Take care.
Joshua Otwell
Visit my developer blog, Digital Owl’s Prose, where I write regularly on CodeIgniter, PHP, WordPress, and SQL.
OpenLampTech is a reader-supported publication. You can support the publication with a virtual coffee for as little as $3 (USD).