How to implement my sql statement in Laravel?

Question

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?

@maytham - Doesn't seem so to me, either way, it was a bad answer. You can't tell me that, from the question as it currently stands, that a raw DB query is the answer. No idea whether or not he is using Eloquent, no idea what his issue actually is and furthermore, for a rough punt, I'd say his issue is more that he simply needs to use FooModel::orderBy('utilizador_id')->orderBy('updated_at')->get() than a raw SQL call. Simply put, bad answer = downvote whether or not you think it's unfair, I don't care. SO needs high quality answers, not lazy point score hoping. — ggdx
– ggdx, Commented Jul 18, 2015 at 21:28
@DanWhite after a little thinking I do only agree, therefore I deleted my answer, the other challenge is he do not reply to any thing since 6 jul. :) — Maytham Fahmi
– Maytham Fahmi, Commented Jul 18, 2015 at 21:30
@maytham coolio. Also apprars to have a -8 score for an identical(ish) question — ggdx
– ggdx, Commented Jul 18, 2015 at 21:35
Here is a fresh answer to this question. This answer is tested and it works. — Maytham Fahmi
– Maytham Fahmi, Commented Jul 23, 2015 at 20:45

Maytham Fahmi · Accepted Answer · 2015-08-05 00:29:05Z

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 from MySQL

Random results of your mysql statement in Laravel Random results 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.

Collectives™ on Stack Overflow

How to implement my sql statement in Laravel?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related