As a backend developer, there are often situations where you have to write and optimize sql queries. Most of them are fairly simple but there are some situations where things are getting a bit more complicated. In today’s blog article we are going to explore some very cool features of MySql like common table expressions aka cte’s and lateral derived tables.
Use Case
You are a sql developer at a trading company like e.g Revolut where you can make money transfers between accounts. Your boss came to you one day and said “Gunts” – in this article I am going to pick Günther aka Gunts as our imaginary sql developer – “Gunts, there are these 3 tables related to money operations: money_transfers,money_deposits and money_withdrawals. They look like this:”
money_transfers:
money_deposits & money_withdrawals:
“Don’t let the other columns bother you, this is all you need to know. When a user makes a money deposit there are multiple money transfers created. The first one is the parent, the other ones relate to him through the parent_id column. Only the parent will have model_type and model_id columns set. deposit_id column is always set and cancelled_deposit_id only if a deposit was rejected, e.g user had insufficient funds.”
“Now since a money transfer is like a chain of money operations, I want you to write a query which returns me all the related operations for 1 MoneyDeposit. For e.g deposit_id 3 I want your query to return all the related deposits including the one with the id 3.”
Wow, Gunts has a difficult task ahead of him! Let us join him on his journey and try to solve this problem together using subqueries.
The first part is to find out the parent money_transfer id. From our table above we know that there are 3 ways a deposit relates to a money transfer, that is through the morph columns: model_type & model_id, deposit_id and cancelled_deposit_id. If the money transfer has the parent_id column set, we will take this id otherwise we will take the money transfer id assuming that this is the parent since the parent_id column was not set. Translating all these words to MySql it looks like this:
select COALESCE(parent_id, id) as o_id
from `money_transfers`
where (`model_type` = 'App\\Models\\MoneyDeposit' and
`model_id` = 3 or
`deposit_id` = 3 or
`cancelled_deposit_id` = 3)
and `money_transfers`.`deleted_at` is null
Let’s run the query:
Great, let’s store our query in a variable:
set @parentId = (select COALESCE(parent_id, id) as o_id
from `money_transfers`
where (`model_type` = 'App\\Models\\MoneyDeposit' and
`model_id` = 3 or
`deposit_id` = 3 or
`cancelled_deposit_id` = 3)
and `money_transfers`.`deleted_at` is null);
Now let’s select all our money transfers using the variable:
Let’s now select all the related money deposits for the one with id 3:
select money_deposits.*, derrived_money_transfers.created_at as money_transfer_created_at
from money_deposits,
(select case model_type when 'App\\Models\\MoneyDeposit' then model_id end as morph_id,
deposit_id,
cancelled_deposit_id,
created_at
from money_transfers
where (id = @parentId or parent_id = @parentId) and money_deposits.id = money_transfers.model_id
or money_deposits.id = money_transfers.deposit_id
or money_deposits.id = money_transfers.cancelled_deposit_id) as derrived_money_transfers
But the query fails:
That’s because normally, a derived table like in our example derrived_money_transfers cannot refer to columns of preceding tables in the same FROM clause. To allow such references we need to specify this using the lateral keyword like this:
select money_deposits.*, derrived_money_transfers.created_at as money_transfer_created_at
from money_deposits,
lateral (select case model_type when 'App\\Models\\MoneyDeposit' then model_id end as morph_id,
deposit_id,
cancelled_deposit_id,
created_at
from money_transfers
where (id = @parentId or parent_id = @parentId) and money_deposits.id = money_transfers.model_id
or money_deposits.id = money_transfers.deposit_id
or money_deposits.id = money_transfers.cancelled_deposit_id) as derrived_money_transfers
Just as expected. Essentially lateral derived tables are like the for each loop in MySQL.
MySQL Common Table Expressions (CTE’s)
MySQL CTE’s have 3 major benefits. They are reusable, improve readability and can even improve the performance, depending on your database. In our example above we need 1 secondary query to store the variable. With CTE’s we can run the whole thing in 1 single query, no other extra queries added. You can create as many CTE’s as you like and even use them in other CTE’s statements, all in a single statement by separating each CTE with a comma:
WITH parent_cte as ((select COALESCE(parent_id, id) as o_id
from `money_transfers`
where (`model_type` = 'App\\Models\\MoneyDeposit' and `model_id` = 3 or
`deposit_id` = 3 or cancelled_deposit_id = 3)
and `money_transfers`.`deleted_at` is null)),
money_transfers_cte as (select case model_type
when 'App\\Models\\MoneyDeposit' then model_id
End as financial_id,
deposit_id,
cancelled_deposit_id,
created_at
from money_transfers, parent_cte
where money_transfers.id = parent_cte.o_id or money_transfers.parent_id = parent_cte.o_id)
SELECT money_deposits.*, derrived_money_transfers.created_at as money_transfer_created_at
FROM money_deposits,
lateral (
select *
from money_transfers_cte
where money_deposits.id = money_transfers_cte.financial_id
or money_deposits.id = money_transfers_cte.deposit_id
or money_deposits.id = money_transfers_cte.cancelled_deposit_id
) as derrived_money_transfers
Notice how clean and readable they are versus using subqueries! The readability bonus is magnified even more when you have many subqueries which go even deeper in level as the one in our example. The result is still as expected:
Conclusion
MySQL cte’s and lateral derived tables are very cool features which give you even more flexibility and beauty when writing queries. So go ahead, play around and enjoy a nice cool day. As always, happy coding!
Useful Links:
Lateral derived tables:
LATERAL derived table in MySQL
Common table expressions:
Improve your SQL Common Table Expressions (CTE) and Window Functions
We have helped 20+ companies in industries like Finance, Transportation, Health, Tourism, Events, Education, Sports.
