How to add a momentary value to a sql query in Laravel-CodePudding

I have this query in Laravel 8:

$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();

I need to add a momentary id to this query named desc_id, so that the values $articles that i query have a desc_id based on the publication_date. The result that i need to achieve is like this:

id	name	category	publication_date	desc_id
3	bananas	news	2022-01-16 17:30:00	1
27	kiwis	news	2021-12-05 21:30:00	3
50	apples	news	2022-01-07 09:14:00	2

I've tried adding it by passing all the elements of $articles into an empty array adding a value:

$count =0;
$allArticles = [];

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

I know that my method is incorrect but i hope that it helps understanding my question.

CodePudding user response：

It looks like you want to have the line number of each row which can be achieve like this :

Article::whereIn(...)->select()
    ->addSelect(DB::raw('RANK() OVER(ORDER BY publicated_at DESC) as desc_id'))
    ->orderBy('publicated_at', 'DESC')
    ->get();