maytham
542
1
Laravel

DB RAW

Posted 3 years ago by maytham

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?

Please sign in or create an account to participate in this conversation.