CakeDC Blog

TIPS, INSIGHTS AND THE LATEST FROM THE EXPERTS BEHIND CAKEPHP

CakePHP Query Builder

CakePHP’s  database Query Builder provides a simple to use fluent interface for creating and operating database queries. It can be used to accomplish most database operations in your application, and works on all supported database systems.


Query builders allow us to create database queries that work with any supported database, without having to worry about differences between SQL implementations in the different database systems.


The CakePHP query builder uses PDO parameter binding to protect your application against SQL injection attacks. There is no need to clean strings being passed as bindings.


For creating a Query object in CakePHP, the easiest way is using find() from a Table object. In CakePHP, queries are lazily evaluated,  which means that they are not evaluated until any of the following actions happens: the query is iterated through a foreach, it’s called first(), all(), toList(), toArray(). 


You can check all the SQL queries that CakePHP is generating, you just need to enable the Database Logging. See here:  https://book.cakephp.org/4/en/orm/database-basics.html#query-logging


Let’s do a few samples using the Query Builder - this is the ER diagram of the database that we will be using for the queries. We have Orders, ProductsUsers and Items that will store the products sold in each order and the quantity sold.

Let’s create some queries using the Query Builder SQL Functions: https://book.cakephp.org/4/en/orm/query-builder.html#using-sql-functions. SQL Functions as part of the Query Builder are abstractions of  some commonly used SQL functions,  and they allow the ORM to choose the specific implementation your application needs based on the Database that is being used. For example, CONCAT is implemented in a different way in MySQL and Postgres, using concat() function will work if you use MySQL or Postgres

 

Imagine we want to build a report of the products sold, including the following:

  1. Display the product in the following format “Products.name - Products.description”.

  2. Total of products sold.

  3. Total of products in stock..

  4. Total amount on sold products.

First, we need to build the Query object using find(), this needs to be done from the Table. $query = Table->find();.

We want to get a report of the products sold and the current stock. Initially, we would need to build a subquery using ItemsTable where the information related to the products sold is present.  Don’t forget to use identifier() when referencing any column. This will tell us  the items sold per product. 

$itemsQuery = $this->Items->find() ->where(['Items.product_id' => $query->identifier('Products.id')]);

Now, let’s build each query needed for the information required:

  1. Let’s start displaying the  product in the following format: “Products.name - Products.description”. Using concat() we could get it:

    $query->select([

                'display_name' => $query->func()->concat([

                    $query->identifier('Products.name'), ' - ',  $query->identifier('Products.description')]),

            ]);

  2. Total of products sold. Using sum() we could get it, we just need to sum Items.quantity per each product,  this  is why we use $itemsQuery and then we sum the quantity per each product.

    $query->select([
      'quantity_sold' => $itemsQuery->select(['sum' => $itemsQuery->func()->sum($query->identifier('Items.quantity'))]),
    ]);

     

  3. To get the income, we need to multiply the Quantity of items  *  Price  and sum all of them. 

      $query->select([
              'income' => $itemsQuery->select(['sum' => $itemsQuery->func()->sum($query->newExpr('Items.quantity * Items.unit_price'))
                ])]);

     

  4. Stock. to get the stock we need to take advantage of the quantity_sold we just got in #2, and subtract it from the real quantity Products.quantity. For getting this, we would need to create a subquery in the FROM clause using the previous queries, and then subtract in the target query something like quantity - quantity_sold.

    $products = $query->cleanCopy()->select([
                'id' => 'ProductDetails.id',
                'price' => 'ProductDetails.price',
                'quantity' => 'ProductDetails.quantity',
                'display_name' => 'ProductDetails.displaye_name',
                'quantity_sold' => 'ProductDetails.quantity_sold',
                'income' => 'ProductDetails.income',
                'stock' => $query->newExpr('ProductDetails.quantity - ProductDetails.quantity_sold'),
            ])->from([
              'ProductDetails' => $query->cleanCopy()->select([
                    'id' => 'Products.id',
                    'price' => 'Products.price',
                    'quantity' => 'Products.quantity',
                    'display_name' => $query->func()->concat([$query->identifier('Products.name'), ' -  ', $query->identifier('Products.description')]),
                  'quantity_sold' => $itemsQuery->select(['sum' => $itemsQuery->func()->sum($query->identifier('Items.quantity'))]),
                  'income' => $itemsQuery->cleanCopy()->select(['sum' => $itemsQuery->func()->sum($query->newExpr('Items.quantity * Items.unit_price'))]),           ])
        ]);

     

Notice that we are using cleanCopy(), this is useful when the same Query object is affected previously by a select(), from() or where clauses, if you run this same example without using cleanCopy() you will get unexpected results. 

The SQL query generated is this:

SELECT ProductDetails.id AS id,
       ProductDetails.price AS price,
       ProductDetails.quantity AS quantity,
       ProductDetails.display_name AS display_name,
       ProductDetails.quantity_sold AS quantity_sold,
       ProductDetails.income AS income,
       (ProductDetails.quantity - ProductDetails.quantity_sold) AS stock
FROM
  (SELECT Products.id AS id,
          Products.price AS price,
          Products.quantity AS quantity,
          (CONCAT(Products.name, :param0, Products.description)) AS display_name,

     (SELECT (SUM(Items.quantity)) AS SUM
      FROM items Items
      WHERE Items.product_id = (Products.id)) AS quantity_sold,

     (SELECT (SUM(Items.quantity * Items.unit_price)) AS SUM
      FROM items Items
      WHERE Items.product_id = (Products.id)) AS income
   FROM products Products) ProductDetails


CakePHP QueryBuilder gives a ton of possibilities to create queries with clean and robust code. So go play with it and enjoy!

 

Latest articles

CakeFest 2021 Decisions

Well… 2021 is already feeling a little 2020ish to me, what about you? While I had high hopes of things being back to normal as far as travel, events, etc. It seems as though we still have a ways to go in that department.  

Difficult Decisions

Our events are no exception to this. While virtual CakeFest was a great success, I think that the CakePHP team, as well as the community had hoped for an in-person event… sooner, rather than later. Everyone is missing the adventures and camaraderie that physical meet-ups bring. Unfortunately, we may be waiting a little longer. The team couldn’t stand to make the tough call on the event alone, without consulting with the community.  

The Community Has Spoken

So, CakePHP took to the polls. The question was posted via social media, and included in the January newsletter: Should CakeFest be virtual or in-person (Los Angeles, specifically)? The consensus was no surprise, and 85%+ of bakers voted for a virtual event this year. One follower mentioned that he “wasn’t ready to risk the event not happening at all, so a virtual event is better than no event”. I couldn’t agree more.    A virtual conference was uncharted territory for CakeFest planners and attendees, but in my opinion.. it came together so wonderfully, that it is hard to be disappointed that it will be happening again. So here’s to gathering once again from the comfort of your own space, and hoping that everyone remembers to have their cake ready! Mark Story can even (virtually) cut it for you if you prefer.   The call for sponsors and speakers will be opening soon, so make sure to contact the CakePHP / CakeFest team with any questions you may have ahead of time.  Planning on attending? We would love to hear from you! What are some topics that you would like covered in the workshops or talks? EMAIL US HERE.   *Digital hug*  

Baking Smarter, Not Harder in 2021

After the year we had… our new motto should be work smarter, not harder, in 2021? Am I right? Luckily, CakePHP community and core members are extremely helpful and constantly working to make baking easier. Here are some things that will assist you with your CakePHP projects….

Plugins:

I recently wrote a blog featuring some of the best (voted by the community) CakePHP plugins - you can see it HERE. A full catalogue of CakePHP plugins is available at https://plugins.cakephp.org. It is no secret that plugins are essential for developers. CakeDC has a few of our own as well, you can get details at:  https://www.cakedc.com/plugins. The good news is, if you don’t see one, but have a great idea, you can build and release your own! To learn more about this process, Jose Gonzalez explains it in his CakeFest 2021 Talk.     Lots of other videos / talks / tutorials are located in the CakePHP Youtube channel as well.
 

Tools:

If you follow us on social media, we highlight a lot of tools released from the community.  One of the most popular is the debugging tool: https://book.cakephp.org/4/en/development/debugging.html A commonly used one is to help updating your composer.json while upgrading https://toolbox.dereuromark.de/utilities/upgrade   You can see more on THIS cakePHP tools list from Dereuromark!
 

Support:

I talk about support channels a lot… because well… what is open source without them? Every time I take a virtual stroll to the slack channels, I am amazed at the participation and interaction between community members. It’s like having your own development team available anytime you need them. Rest assured that if you have an issue that's causing a blocker, someone in the chat will (most likely) have a solution. A full list of support channels was listed in our last blog, but if you missed it, here you go: Discourse forum: https://discourse.cakephp.org Stack Overflow: https://stackoverflow.com/tags/cakephp IRC: https://kiwiirc.com/nextclient/irc.freenode.net#cakephp Slack: https://cakesf.herokuapp.com
 

The Book:

If you’re a veteran baker, you already know this. However, this wouldn't be a helpful blog without mentioning the all mighty book… The CakePHP bookThis is where you should start on your cake journey… read it… read it again. Then, reference back to it when you need it. There is an average of 46k users utilizing the book monthly (184k sessions). That should speak for itself.
 

Newsletter:

The CakePHP team releases a newsletter each month. This is a good resource if you’re looking to catch up on a month’s worth of news in one place.  Usually included is: releases, helpful tools, training/event dates, specials, surveys, and more.  You can see previous newsletters & subscribe HERE.   I will close this with a shameless plug: if you want to work a whole lot smarter this year, let someone else do the work for you. Check out all of the CakeDC services offered at cakedc.com/services.    Here’s to 2021 being a lot easier… more peaceful… and tasty!  

Saying Goodbye To 2020 Is A Piece of Cake

I never thought that I would be so excited to say… the year is coming to an end. What a year it has been. Let’s focus on some good things that happened for us this year.

CakePHP Community

Our team worked as closely as ever with the CakePHP community. We have hosted training sessions and multiple online meetups - for which we had consistent attendance and involvement. We have enjoyed getting to know each baker that attends and interacts with us. It was a great year for releases, too. If you follow the social media pages, you’re up to date with all of the new features and tools that are released in real time. Not to mention new and updated plugins. You can see all of the latest releases in the BAKERY.  

CakeFest

While we usually get to travel to some amazing city in the world and meet with the attendees in person, clearly that was not going to be an option this year. For the first time ever, the event was held virtually. Being so involved, and responsible for the success of the event, was stressful and frightening to say the least, but alas…it came together wonderfully.  We were able to have attendees that may not have been able to join us otherwise, and the participation was unprecedented. I am so thankful for the wonderful sponsors, speakers, attendees, and contributors that made the event possible.  In case you didn’t know, all of the CakeFest 2020 videos have been released. You can view them in the CakeFest archive, or on the CakePHP Youtube channel. Don’t forget to subscribe to the channel, and I will tell you why later.    Now… what can be expected in 2021? LOTS!

Increased Involvement

We want to expand the kitchen, if you will. We have an amazing group of core developers, and they would love to have more involvement from the community. If you’ve ever thought about contributing and getting involved, there are many options, you can check out details HERE.   

More documentation / blogs

Speaking of getting more people involved, this will include blogging. We have so many great plugins, but maybe not enough documentation to support the training on them. The core team is on board and we will all be working hard to provide more documentation for the framework. In fact, we even some some community members that have already volunteered to provide more blog contributions to the bakery. Maybe you’d like to as well?!  

Video Tutorials

One suggestion that we get consistently is that we need more video tutorials. We agree! We are going to be updating our YouTube channel and will hopefully be adding more video tutorials as soon as Q1. We will also make sure that training sessions / meetups will be available to subscribers for playback. We would love to hear your suggestions for content, you can email us at community@cakephp.org and let us know what you’d like to see. 
 

Support

As always, CakePHP has many support channels, and this will not change in the new year. In fact, we plan to make it even easier to connect with other bakers.   Here are some current available support options: Discourse forum: https://discourse.cakephp.org Stack Overflow: https://stackoverflow.com/tags/cakephp IRC: https://kiwiirc.com/nextclient/irc.freenode.net#cakephp Slack: https://cakesf.herokuapp.com
  Want to have your opinion heard? The core team wants to know what you want for CakePHP 5. Please take a moment and fill out a quick SURVEY to let them know.    We can’t wait to bake with you in 2021. Wishing you all a happy holiday season and a very happy new year!  

We Bake with CakePHP