How to set a MySQL variable while selecting columns in Laravel Query Builder?

Web Development
, ,
1

I want to set up a variable to calculate (income - bills) as salary, then use that salary to calculate the total amount (salary + bonus) as total when selecting columns from a table. What is the proper syntax for this in Laravel’s Query Builder?

Here is a simple example of MySQL. I just need the syntax for more complex calculations, which is hard to explain here.

SELECT (income - bills) AS salary, (income - bills + bonus) AS total FROM table_name;

I also tried this, which not working.

SELECT (income - bills) as salary, (salary + bonus) as total FROM table_name;
2

In MySQL, you can set variables using the SET statement or SELECT variables. Variables can store values that can be reused in subsequent queries.

SET statement

SET @var_name = value;     
// or 
SET @var_name := value;

Note: both operators = and := are accepted.

SELECT statement

SELECT col1, @var_name := col2 from tb_name WHERE "condition";

Usecase

You can use the code below to set variables in MySQL for your scenario.

SET @salary = income - bills;
SELECT @salary as salary, (@salary + bonus) as total FROM table_name;

Or the code below for laravel query builder.

DB::select(
    DB::raw('SELECT (@salary := income - bills) as salary, (@salary + bonus) as total FROM table_name')
);