How to implement my sql statement in Laravel?

问题:

I have the following MySQL query which 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 mention works fine in MySQL but I want to implement it in my Laravel environment.

My Question: How do I implement this sql statement in Laravel?

回答1:

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

The code is tested on my Laravel 5.0 test environment and it works perfectly.

Your mysql statement is also tested it works perfectly on 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 to added use DB; after the name space:

<?php

namespace App\Http\Controllers;

use DB;

I have made View results code only to demonstrate all results output, but it is up to you how to manipulate the data in your code.

Test results

Random results of your mysql statement in MySQL console

Random results of your mysql statement in Laravel

Note:

1- I have solved this question my self for you but I faced a little issue and I got input from Kryptonit3 in Laracast forum.

2- You might find other solutions to this question or it can be solved in different ways, but I have chosen to solve this way.

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