Laravel如何使用Eloquent获取对象的行号? [英] Laravel how do I get the row number of an object using Eloquent?

问题描述

我想根据用户的创建日期知道其位置.我该如何使用Eloquent?

I'd like to know the position of a user based on its creation date. How do I do that using Eloquent?

我希望能够做这样的事情:

I'd like to be able to do something like this:

User::getRowNumber($user_obj);

推荐答案

我想您需要MySQL解决方案，因此您可以执行以下操作:

I suppose you want MySQL solution, so you can do this:

DB::statement(DB::raw('set @row:=0'));
User::selectRaw('*, @row:=@row+1 as row')->get();
// returns all users with ordinal 'row'

因此您可以实现以下内容:

So you could implement something like this:

public function scopeWithRowNumber($query, $column = 'created_at', $order = 'asc')
{
    DB::statement(DB::raw('set @row=0'));

    $sub = static::selectRaw('*, @row:=@row+1 as row')
        ->orderBy($column, $order)->toSql();

    $query->remember(1)->from(DB::raw("({$sub}) as sub"));
}

public function getRowNumber($column = 'created_at', $order = 'asc')
{
    $order = ($order == 'asc') ? 'asc' : 'desc';

    $key = "userRow.{$this->id}.{$column}.{$order}";

    if (Cache::get($key)) return Cache::get($key);

    $row = $this->withRowNumber($column, $order)
        ->where($column, '<=',$this->$column)
        ->whereId($this->id)->pluck('row');

    Cache::put($key, $row);

    return $row;
}

这需要从表中选择所有行，直到找到要查找的行，然后仅选择该特定行号.

This needs to select all the rows from the table till the one you are looking for is found, then selects only that particular row number.

它将允许您执行以下操作:

It will let you do this:

$user = User::find(15);

$user->getRowNumber(); // as default ordered by created_at ascending

$user->getRowNumber('username'); // check order for another column

$user->getRowNumber('updated_at', 'desc'); // different combination of column and order

// and utilizing the scope:
User::withRowNumber()->take(20)->get(); // returns collection with additional property 'row' for each user

由于此范围要求每次将原始代码 @row 设置为0，因此我们使用缓存1分钟，以避免不必要的查询.

As this scope requires raw statement setting @row to 0 everytime, we use caching for 1 minute to avoid unnecessary queries.

这篇关于Laravel如何使用Eloquent获取对象的行号?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！