Laravel 8中如何为SQL查询结果添加动态desc_id字段

原查询代码 #

$articles = Article::whereIn('category_id', $categories)->where(function ($query) {
        $query->where(function ($query2) {
            $query2->where('draft', 0)->whereNull('publication_date');
        })->orWhere(function ($query2) {
            $query2->where('draft', 0)->where('publication_date', '<=', DateHelper::currentDateTime());
        });
    })->orderBy('publicated_at', 'DESC')->get();

需求说明 #

需要为查询结果添加一个名为desc_id的临时字段，该字段根据publication_date从新到旧的顺序生成递增序号（最新记录的desc_id为1，次新为2，以此类推），最终结果格式如下：

错误尝试分析 #

你之前的代码中，with()是Eloquent的关联加载方法，并非用于添加自定义属性，因此写法不正确：

$count =0;
$allArticles = [];

foreach ($articles as $article) {
    $count++;
    $allArticles[] = $article->with('desc_id', $count);
}

正确实现方式 #

方法一：集合遍历添加（小数据量首选） #

先修正原查询中的排序字段笔误（publicated_at改为publication_date），确保结果按发布时间降序排列，再通过集合操作给每个模型添加desc_id属性：

// 修正排序字段并执行查询
$articles = Article::whereIn('category_id', $categories)
    ->where(function ($query) {
        $query->where(function ($query2) {
            $query2->where('draft', 0)->whereNull('publication_date');
        })->orWhere(function ($query2) {
            $query2->where('draft', 0)->where('publication_date', '<=', DateHelper::currentDateTime());
        });
    })
    ->orderBy('publication_date', 'DESC')
    ->get();

// 用map方法批量添加desc_id
$count = 0;
$articles = $articles->map(function ($article) use (&$count) {
    $count++;
    $article->desc_id = $count;
    return $article;
});

// 也可以用foreach循环实现
// $count = 0;
// foreach ($articles as $article) {
//     $count++;
//     $article->desc_id = $count;
// }

方法二：数据库查询直接生成（大数据量首选） #

如果数据量较大，推荐直接在数据库层面生成序号，效率更高：

适用于MySQL 8.0+（支持窗口函数）

$articles = Article::select('*', DB::raw('ROW_NUMBER() OVER (ORDER BY publication_date DESC) as desc_id'))
    ->whereIn('category_id', $categories)
    ->where(function ($query) {
        $query->where(function ($query2) {
            $query2->where('draft', 0)->whereNull('publication_date');
        })->orWhere(function ($query2) {
            $query2->where('draft', 0)->where('publication_date', '<=', DateHelper::currentDateTime());
        });
    })
    ->orderBy('publication_date', 'DESC')
    ->get();

适用于MySQL 5.x版本（使用变量）

$articles = DB::table('articles')
    ->select('*', DB::raw('@row := @row + 1 as desc_id'))
    ->whereIn('category_id', $categories)
    ->where(function ($query) {
        $query->where(function ($query2) {
            $query2->where('draft', 0)->whereNull('publication_date');
        })->orWhere(function ($query2) {
            $query2->where('draft', 0)->where('publication_date', '<=', DateHelper::currentDateTime());
        });
    })
    ->orderBy('publication_date', 'DESC')
    ->from(DB::raw('articles, (SELECT @row := 0) as r'))
    ->get();

// 如需转换为Eloquent模型实例
// $articles = Article::hydrate($articles->toArray());

内容的提问来源于stack exchange，提问作者Tammapao