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, Products, Users and Items that will store the products sold in each order and the quantity sold.
- Display the product in the following format “Products.name - Products.description”.
- Total of products sold.
- Total of products in stock..
- Total amount on sold products.
$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:
-
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')]),]); -
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'))]),
]); -
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'))
])]); -
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'))]), ])
]);
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!
Company mission - WHO ARE YOU?
Goals / Challenges - we all have them, don’t be shy
Expansion Plan - how will you make it to the next level?
Upcoming Actions - Making a clear timeline for the next year. It is important to make the timeline obtainable... in other words, don’t make goals that are impossible to meet.
Other important topics include additional small actions, goal timeline, campaign calendars, advertising outlets, social media platforms (and the importance, utilization for each one).
Basically, instead of creating a regular controller with many methods, we create Traits and group the actions by domain business.
Sometimes we need to override the functions, and we want add our custom logic after -before or replace the function on Traits:
If you wanna bake Traits more complexly I recommend you check out the 
* This project doesn't have Resources files
Now, let's create a new constant for Resources on /config/paths.php:
Finally, update the paths on config/app.php:
The root of the issue is the packages using 
Now, let see how the project looks:
Here, we have few deprecations and warnings. Do you remember I mentioned the rector is optional? So, the question is the rector and it's not always able to handle these issues.
I will use the PHPStan to fix this - we will install with composer:
Now, we can run the phpstan analyse and fix the issues:
It's up to you how much effort you will put in with PHPStan issues. I recommend fixing everything. For this post, I did fix only what was needed to run the project after the update, you can check the fixes on this 
That’s all? No. But we upgraded CakePHP? Yes.
Real applications probably use many plugins, and if these plugins don't have a version for CakePHP 4, you will need to update. Depending on the size and level of complexity of the project, the upgrade could be hard, but never impossible.
If you do not feel confident or your company would like to outsource support for this, don't hesitate to 