MySQL Aggregate Query using CodeIgniter’s Query Builder

CodeIgniter’s Query Builder ORM has class methods for nearly any kind of database access/operation you can think of. In this post, I’ll cover some of the available methods for retrieving aggregate-level query results. The examples in this post map out Query Builder method chaining to produce results you would get from a raw MySQL query. Continue reading to see more…

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

MySQL Query prototyping

When working on Back-end PHP code, I typically prototype out MySQL queries using either MySQL Workbench or phpMyAdmin. Once I have the correct query results, I then translate the MySQL query over to the PHP layer. For applications written in core PHP, I tend to use the PDO class.

However, when using CodeIgniter 4 (which is my PHP framework of choice at this time), you can use any of these options: 

• Query Builder class methods.
• Extend the CodeIgniter\ Model class, utilizing its helper methods.
• A combination of both.

For the examples in this post, I will use Query Builder class methods which are specific for aggregate-level data.

Aggregate Query Results using MySQL

As a SQL-first type of developer, I can honestly say that SQL is my favorite programming language and I am comfortable writing MySQL queries. That being said, I do appreciate the usefulness of an ORM.

In my particular instance, since I use the CodeIgniter framework, the combination of CodeIgniter’s Models and the Query Builder class makes querying quite easy, among other things.

Here is one such case in which I had little trouble retrieving the same query results using CodeIgniter Query Builder class ORM methods, as opposed to a raw MySQL query. I don’t feel that one approach is better or more valid than the other. This is just my experience as I learn CodeIgniter and am exposed to the Query Builder class and the in-built Models functionality provided by the framework.

Let’s review this MySQL query which retrieves aggregated data on some of the walking stats and metrics I am interested in:

Nothing too extravagant. A typical query using SUM(), COUNT(), MIN(), and MAX() aggregate functions. 

The above query returns these results:

Aggregate Query Results using CodeIginiter Query Builder class methods

But, is this type of query – and more importantly – the query results, easily reproduced using CodeIgniter 4’s Query Builder class methods? They absolutely are.

Let’s visit this CodeIgniter method I have in a class which extends the CodeIgniter Model:

The 4 key Query Builder aggregate-type methods used in the above Model method are:

1. selectMin() – Maps to: SELECT MIN(field_name)
2. selectMax() – Maps to: SELECT MAX(field_name)
3. selectSum() – Maps to: SELECT SUM(field_name)
4. selectCount() – Maps to: SELECT COUNT(field_name)

(Note: All 4 of the methods accept an optional 2nd parameter used to rename the field. I think of this in the same context as aliasing a column in MySQL using the AS keyword.

The shoeAnalytics() model method produces identical query results as the MySQL query version shown previously. Looking at them in the browser using PHP”s print_r() method, we can see all of the data is there:

Consider making a small donation on my behalf as I continue to provide useful and valuable content here on my site.

The shoeAnalytics() method can now provide these specific query results to any Views or libraries needing the data.

Other CodeIgniter Query Builder class methods

Which CodeIgniter Query Builder class methods do you find map nicely to a corresponding similar SQL expression? Which are those that do not map so nicely? Let me know in the comments!

Similar Reading

Visit any of the below PHP-related blog posts I have written if you are so inclined. Please share them with others who would benefit from the content as well.

• PHP date() function for common date formats
• PHP trim functions: trim, rtrim, and ltrim
• PHP empty() function use with MySQL NULL
• Use MySQL BLOB column with PHP to store .pdf file
• CodeIgniter Form Helper library – at a glance

Like what you have read? See anything incorrect? Please comment below and thank you for reading!!!

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.

To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)

Be sure and visit the “Best Of” page for a collection of my best blog posts.

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

Have a look at all Apress PHP books and eBooks.