OrderBy special condition

A drill down technique would be easier in my opinion. And do you really need Topic and Category.

There are ways to achieve this and yet keep it simple.

I found the solution and here I try to explain how I did it.

First I run a query to get the list of news items in a category. The selection of the category is made by the user.

In the following example I enter the category manually, just to show how it works:

$search = 'TV series';

$query = News::with([
    'category_related',
    'topic_related'
])
->whereHas('category_related', function($query) use ($search){

    $query->where('name', $search);
        
})
->orderBy('	created_at', 'desc')
->paginate(20);

Then I created a resource:

<?php

namespace App\Http\Resources;

use Illuminate\Http\Resources\Json\JsonResource;

class NewsListResource extends JsonResource{

    public function toArray($request){
        
        return [
            'id' => $this->id,
            'title' => $this->title,
            'topic_id' => !empty($this->topic_related) ? $this->topic_related->id : null,
        ];

    }
    
}

In the created resource, I check whether the relationship with the "topics" has a result and if it does, I print the id relating to the "topic".

Now I use the resource with sortByDesc method:

$data = NewsListResource::collection($query)->sortByDesc('topic_id')->values();

I simply get the list of news items, reorder it according to the topic id (so that news items containing a topic are placed first).

I hope I have been clear in my explanation

Other Solution

// Count topic related
$query->withCount(['topic_related as count_topic' => function($query) use($search){
    $query->where('name', $search);
}]);

// Order
$query->orderByDesc('count_topic')->orderBy('created_at', 'desc');