DB RAW

I am trying to solve issue with DB::RAW.

Here is the sql statement, when I run it on MySQL test database it returns following correct values

category    question
1           question11
1           question12
2           question21
2           question122

Here is my sql statement which works fine:

SET @prev=0,@rownum=0;
SELECT category, question 
FROM (
  SELECT *, 
         IF( @prev <> category, 
             @rownum := 1, 
             @rownum := @rownum+1 
         ) AS rank, 
         @prev := category, 
         @rownum  
  FROM (
    SELECT * FROM `random2`
    ORDER BY category, rand()
  ) AS random_ads
) AS ads_ranked 
WHERE rank <= 2;

I have made DB::RAW for this statement in the project

        $results =
        DB::select(
            DB::raw("
                SELECT category, question
                FROM (
                  SELECT *,
                         IF( @prev <> category,
                             @rownum := 1,
                             @rownum := @rownum+1
                         ) AS rank,
                         @prev := category,
                         @rownum
                  FROM (
                    SELECT * FROM `random2`
                    ORDER BY category, rand()
                  ) AS random_ads
                ) AS ads_ranked
                WHERE rank <= 2;
            ")
        );

        var_dump($results);

It works fine, but it returns only the last values

2       question21
2       question122

Here comes the question:
What I have missed, since i return only last values?

Kryptonit3

10 years ago

Best Answer

Level 11

try putting this before your $results query.

DB::statement(DB::raw('SET @prev=0,@rownum=0;'));

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

var_dump($results);

1 like

Please or to participate in this conversation.