Laravel sub query multiple column sum as a AS and make a where condition on it

avatar
Published: Jan 25, 2023

In a short answer, it's NO! You don't make a where clause in a subquery result or column alias β€œas”.  

You cannot use a column alias in a WHERE clause in standard SQL. This constraint is put in place because the column value might not be known when the WHERE code is run.

πŸ“ Took from MySQL documentation.

Instead of where condition we can use having. This one allows us to filter the result from the alias column. Before of that, we want to know the difference between Where vs Having πŸ‘‡

Assume that we have a table,

CREATE TABLE `data` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `value` int(10) unsigned NOT NULL,
 PRIMARY KEY (`id`),
 KEY `value` (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

We insert 10 rows each row containing the value of ID & Value is 1 to 10.

INSERT INTO `data`(`id`, `value`) VALUES (1, 1),(2, 2),(3, 3),(4, 4),(5, 5),(6, 6),(7, 7),(8, 8),(9, 9),(10, 10);

Let's try the query below πŸ‘‡

SELECT `value` v FROM `data` WHERE `value`>5; -- Get 5 rows
SELECT `value` v FROM `data` HAVING `value`>5; -- Get 5 rows

We can see that, we will get the same result as we can see the Having clause can work without the GROUP BY clause.

Check the difference,

SELECT `value` v FROM `data` WHERE `v`>5;

The above query will raise the error: Error #1054 - Unknown column 'v' in 'where clause'

SELECT `value` v FROM `data` HAVING `v`>5; -- Get 5 rows

WHERE the clause allows a condition to use any table column, but it cannot use aliases or aggregate functions. HAVING the clause allows a condition to using a selected (!) column, alias, or an aggregate function.

This is because WHERE the clause filters data before selection, but HAVING the clause filters the resulting data after selection.

So putting the conditions in WHERE a clause will be more efficient if you have many many rows in a table.

Try EXPLAIN to see the critical difference πŸ‘‡

EXPLAIN SELECT `value` v FROM `data` WHERE `value`>5;
 ---- ------------- ------- ------- --------------- ------- --------- ------ ------ -------------------------- 
| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra                    |
 ---- ------------- ------- ------- --------------- ------- --------- ------ ------ -------------------------- 
|  1 | SIMPLE      | data  | range | value         | value | 4       | NULL |    5 | Using where; Using index |
 ---- ------------- ------- ------- --------------- ------- --------- ------ ------ -------------------------- 

EXPLAIN SELECT `value` v FROM `data` having `value`>5;
 ---- ------------- ------- ------- --------------- ------- --------- ------ ------ ------------- 
| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra       |
 ---- ------------- ------- ------- --------------- ------- --------- ------ ------ ------------- 
|  1 | SIMPLE      | data  | index | NULL          | value | 4       | NULL |   10 | Using index |
 ---- ------------- ------- ------- --------------- ------- --------- ------ ------ ------------- 

You can see either WHERE or HAVING uses an index, but the rows are different.

So now how we can use the having clause in Laravel?

Think, we have a table of users where each user has a value of three different subjects. For example, the User name is Adam and the 3 different subject column is math, english, social. These 3 columns have values like, 40, 50, and 60. When need to sum that and get the total as an alias column name β€œtotal”.

Let's jump to the code πŸ§‘β€πŸ’»,

$userByTotal = User::query()
            ->select('name', 'math', 'english', 'social')
            ->selectRaw('(math  english   social) as total')
            ->having('total', '>', '100')
            ->get(); 

You can use orHaving but I don't any mejor difference between having & orHaving. Search more about having in Laravel Documentation.

This article contains from stack overflow (1), stack overflow (2)

⭐ Get the Tailwind CSS design from tailkitpro.com

Comments

No comments yet…