如何在Laravel中实现我的sql语句? [英] How to implement my sql statement in Laravel?

问题描述

我有以下运行正常的MySQL查询.它从我的表中返回随机结果.

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;

这是我的桌子:

+-------------+------+
|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 |
+-------------+------|

预期的随机结果:

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

上面提到的sql语句在MySQL中工作正常，但是我想在Laravel环境中实现它.

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

我的问题:我如何在Laravel中实现此sql语句?

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

推荐答案

您可以使用DB::statement，DB:raw和DB::select来解决此问题.

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

该代码已在我的Laravel 5.0测试环境中进行了测试，并且运行良好.

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

您的mysql语句也已经过测试，可以在MySQL控制台上完美运行.

Your mysql statement is also tested it works perfectly on MySQL console.

以下是代码:

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;
        ")
    );

查看结果

echo "utilizador_id | nome <br />";

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

记住，将其添加到名称空间后的use DB;:

Remember to added use DB; after the name space:

<?php

namespace App\Http\Controllers;

use DB;

我制作了View results代码只是为了演示所有结果输出，但是如何操作代码中的数据则取决于您.

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.

测试结果

MySQL控制台中mysql语句的随机结果

Laravel中mysql语句的随机结果

注意:

1-我已经为您解决了这个问题，但遇到了一个小问题，我在Laracast论坛上从Kryptonit3获得了输入.

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-您可能会找到该问题的其他解决方案，或者可以用不同的方法解决，但我选择了这种方法.

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.

请在此处找到注释1中的完整问题和答案.

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

这篇关于如何在Laravel中实现我的sql语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！