How to implement my sql statement in Laravel?

Question

I have the following MySQL query that works fine. It returns random results from my table.

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() ) AS random_ads ) AS ads_ranked WHERE rank <= 2; 

Here is my table:

 +-------------+------+ |utilizador_id|nome | +-------------+------| | 1 |test1 | | 1 |test2 | | 1 |test3 | | 1 |test4 | | 1 |test5 | | 2 |test1 | | 2 |test2 | | 2 |test3 | | 3 |test1 | | 3 |test2 | | 3 |test3 | +-------------+------| 

Expected Random Results:

 +-------------+------+ |utilizador_id|nome | +-------------+------| | 1 |test2 | | 1 |test5 | | 2 |test1 | | 2 |test2 | | 3 |test1 | | 3 |test3 | +-------------+------| 

The sql statement as a mention works fine in MySQL, but I want to implement it in my Laravel environment.

My question is: How to implement this sql statement in Laravel?

Answer 1

You can solve this problem using DB::statement , DB:raw and DB::select .

The code is being tested in my Laravel 5.0 test environment, and it works great.

Your mysql statement is also verified, it works fine on the MySQL console.

Here is the code:

 DB::statement(DB::raw('SET @prev=0,@rownum=0')); $results = DB::select( DB::raw(" 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() ) AS random_ads ) AS ads_ranked WHERE rank <= 2; ") ); 

View Results

 echo "utilizador_id | nome <br />"; foreach ($results as $result) { echo $result->utilizador_id . "__________| " . $result->nome . "<br />"; } 

Remember by adding use DB; after namespace:

 <?php namespace App\Http\Controllers; use DB; 

I did a look at the result code only to demonstrate all the results, but it's up to you how to manipulate the data in your code.

Test results

Random results of your mysql statement in MySQL console

Random results from your mysql statement in Laravel

Note:

1- I solved this issue for myself, but I had a small problem, and I received information from Kryptonit3 in the Laracast forum.

2. You can find other solutions to this issue or solve it in different ways, but I decided to solve this way.

The full question and answer in note 1 can be found here .