laravel 口才从每组中获得前5个记录

siv3szwd 于 2022-11-18 发布在其他

关注(0)|答案(1)|浏览(254)

我尝试使用Eloquent查询从每个类别中获取前5个记录，类似于示例MySQL查询，如下所示;

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY secid ORDER BY created_at DESC) AS n
    FROM trees
) AS x
WHERE n <= 5

这是我试过的

$q= Tree::select(['*',DB::raw("ROW_NUMBER() OVER (PARTITION BY secid ORDER BY created_at DESC) AS n")])
         ->WhereRaw(['n','<=','5'])

〉选择（）;
出现“数组到字符串转换”错误
我试过了

->WhereRaw('n','<=','5')

和获取

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '5 n order by `trees`.`created_at` desc' at line 1

有谁能指出我在这里做错了什么吗？任何帮助都将不胜感激。

laravel

来源：https://stackoverflow.com/questions/74464064/eloquent-get-top-5-records-from-each-group

1条答案

按热度按时间

bpsygsoo1#

whereRow方法的第一个参数是sql字符串，第二个参数是绑定数组。要避免此错误，需要执行以下调用：

->whereRaw('n <= ?', [5])

但是仍然无法正确构建查询。我建议您使用以下结构：

$subQuery = DB::table('trees')
    ->selectRaw('*, ROW_NUMBER() OVER (PARTITION BY secid) AS n')
    ->orderByDesc('created_at');

$result = DB::fromSub($subQuery, 'x')
    ->where('n', '<=', 5)
    ->get();

Eloquent溶液：

$subQuery = Tree::selectRaw('*, ROW_NUMBER() OVER (PARTITION BY secid) AS n')
    ->orderByDesc('created_at');

$result = Tree::fromSub($subQuery, 'trees')
    ->where('n', '<=', 5)
    ->get();

赞(0）回复(0）举报 2022-11-18

我来回答

laravel 口才从每组中获得前5个记录

1条答案

相关问题

热门标签

最新问答