Laravel ranking field how to use set variables in MySQL?

Question

I have a users table with an integer field called ep.

Now, I want to retrieve all users from a specific group ordered by the EP field, and with a generated MySQL field called rank.

This is what I tried:

DB::transaction(function()
{
    DB::statement("SET @rank:=0");
    $group = Sentry::findGroupByName('Player');
    $users = Sentry::getUserProvider()->createModel()
        ->join('throttle', 'throttle.user_id', '=', 'users.id')
        ->join('users_groups', 'users_groups.user_id', '=', 'users.id')
        ->where('throttle.banned', 0)
        ->where('throttle.suspended', 0)
        ->where('users_groups.group_id', $group->id)
        ->orderBy('ep', 'desc')
        ->paginate(100, array('@rank := @rank + 1', 'users.ep', 'users.username'));
});

But I got this error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column '@rank := @rank + 1' in 'field list' (SQL: select `@rank := @rank + 1`, `users`.`ep`, `users`.`username` from `users` inner join `throttle` on `throttle`.`user_id` = `users`.`id` inner join `users_groups` on `users_groups`.`user_id` = `users`.`id` where `throttle`.`banned` = 0 and `throttle`.`suspended` = 0 and `users_groups`.`group_id` = 2 order by `ep` desc limit 100 offset 0)

score 7 · Answer 1 · answered Apr 06 '19 at 18:31

7

Ran into this same issue and found the correct solution on : https://laravel.io/forum/12-18-2014-how-to-initialize-user-defined-mysql-variables

DB::statement( DB::raw( 'SET @total := 0'));
$results = DB::select( $query); // only the "select.." part here

answered Apr 06 '19 at 18:31

maximus 69

1,388
4
22
35

To add, if we have laravel write & read database connection config. Remember to use the `write` connection in `select(...)` function as by default it set to read connection which will be a different SQL session from the `statement(...)` function where we set the variable. e.g: `$results = DB::select($query, [], false);` – GusDeCooL Sep 28 '22 at 00:05

score 0 · Answer 2 · answered Apr 14 '15 at 17:33

array(DB::raw('@rank := @rank + 1 as A_VALID_ARRAY_KEY'), ...

You have to use alias, otherwise you won't be able to use that value.

Also, you can set the variable inline like here http://softonsofa.com/tweaking-eloquent-relations-how-to-get-n-related-models-per-parent/

Laravel ranking field how to use set variables in MySQL?

2 Answers2