How to initialize user-defined MySQL variables?

posted 10 years ago

Query I want to run needs to initialize a @total variable:

SET @total = 0;
SELECT ...
    @total := @total + x AS total_x,
    ...
WHERE
    @total < 1000

Query works when tested via phlMyAdmin, but fails to run through Laravel. Tried putting both statements into single DB::select( $query) call, and running two consequent calls:

DB::statement( DB::raw( 'SET @total := 0'));
$results = DB::select( $query); // only the "select.." part here

What's the workaround here?

Last updated 3 years ago.

jimgwhit

replied 10 years ago

Have you thought about using a stored procedure?

awsp

replied 10 years ago

Solution

This seems work for me...What were you trying to do?

DB::statement(DB::raw('SET @total = "bar"'));
$a = DB::select(DB::raw('select * from applications where foo = @total'));
    
DB::statement(DB::raw('SET @total = "id"'));
$b = DB::select('select @total from applications');

jarektkaczyk

replied 10 years ago

There's inline initializing available in MySQL:

DB::select(DB::raw('@total := @total + x as total_x'), ...)
   ->from(DB::raw('your_table, (select @total := 0) as var'))
   ... 
   ->get();

pmvalente

replied 10 years ago

could you help me to put this query in laravel

SET @prev=0,@rownum=0;
SELECT utilizador_id, nome
FROM (SELECT *,
         IF( @prev <> utilizador_id,
             @rownum := 1,
             @rownum := @rownum+1
         ) AS rank,
         @prev := utilizador_id,
         @rownum
  FROM ( SELECT * FROM anuncios
    ORDER BY utilizador_id, rand()
  ) random_prodcts
) products_ranked
WHERE rank <= 2

thanks in advance

Last updated 10 years ago.

Moderators