Laravel 如何使用 Eloquent 获取对象的行号？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23408091/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Laravel how do I get the row number of an object using Eloquent?
提问by William Wino
I'd like to know the position of a user based on its creation date. How do I do that using Eloquent?
我想根据用户的创建日期了解用户的位置。我如何使用 Eloquent 做到这一点？
I'd like to be able to do something like this:
我希望能够做这样的事情：
User::getRowNumber($user_obj);
回答by Jarek Tkaczyk
I suppose you want MySQL solution, so you can do this:
我想你想要 MySQL 解决方案，所以你可以这样做：
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
As this scope requires raw statement setting @rowto 0 everytime, we use caching for 1 minute to avoid unnecessary queries.
由于此范围@row每次都需要将原始语句设置为 0，因此我们使用缓存 1 分钟以避免不必要的查询。
回答by Frank Myat Thu
$query = \DB::table(\DB::raw('Products, (SELECT @row := 0) r'));
$query = $query->select(
\DB::raw('@row := @row + 1 AS SrNo'),
'ProductID',
'ProductName',
'Description',
\DB::raw('IFNULL(ProductImage,"") AS ProductImage')
);
// where clauses
if(...){
$query = $query->where('ProductID', ...));
}
// orderby clauses
// ...
// $query = $query->orderBy('..','DESC');
// count clause
$TotalRecordCount = $query->count();
$results = $query
->take(...)
->skip(...)
->get();
回答by Sopheakdey Moeun
[For Postgres]
[对于 Postgres]
In your model
public function scopeWithRowNumber($query, $column = 'id', $order = 'asc'){ $sub = static::selectRaw('*, row_number() OVER () as row_number') ->orderBy($column, $order) ->toSql(); $query->from(DB::raw("({$sub}) as sub")); }In your controller
$user = User::withRowNumber()->get();
在你的模型中
public function scopeWithRowNumber($query, $column = 'id', $order = 'asc'){ $sub = static::selectRaw('*, row_number() OVER () as row_number') ->orderBy($column, $order) ->toSql(); $query->from(DB::raw("({$sub}) as sub")); }在您的控制器中
$user = User::withRowNumber()->get();
回答by Rubens Mariuzzo
I believe you could use Raw Expresssionsto achieve this:
我相信您可以使用原始表达式来实现这一点：
$users = DB::table('users')
->select(DB::raw('ROW_NUMBER() OVER(ORDER BY ID DESC) AS Row, status'))
->where('status', '<>', 1)
->groupBy('status')
->get();
However, looking trough the source codelooks like you could achieve the same when using SQLServer and offset. The sources indicates that if you something like the following:
但是，查看源代码看起来在使用 SQLServer 和offset时可以实现相同的目标。消息来源表明，如果您出现以下情况：
$users = DB::table('users')->skip(10)->take(5)->get();
The generated SQL query will include the row_number overstatement.
生成的 SQL 查询将包含row_number over语句。
