surfweb's avatar

OrderBy special condition

I am creating a small news system and these are the tables I have available:

News Table

  • id
  • title
  • text
  • created_at
  • updated_at

Category Table

  • id
  • name
  • created_at
  • updated_at

Topic Table

  • id
  • name
  • created_at
  • updated_at

Related post/category Table

  • id
  • post_id
  • category_id
  • created_at
  • updated_at

Related post/topic Table

  • id
  • post_id
  • topic_id
  • created_at
  • updated_at

When writing a post, both categories and topics must be assigned. Topics and categories are the same thing but with a difference: you can assign unlimited categories but only 1 topic.

When I search for posts by category I get the list of posts containing that category. What I would like is to put first the posts that have the topic corresponding to the search made and then all the others.

Let me give you an example:

  1. News 1
    • Categories: film, anime
    • Topic: film
    • Created_at: 2023-08-22 14:37:45
  2. News 2
    • Categories: book, anime
    • Topic: book
    • Created_at: 2023-09-05 09:23:54
  3. News 3
    • Categories: TV series, marvel
    • Topic: TV series
    • Created_at: 2023-09-19 15:17:12
  4. News 4
    • Categories: TV series, police
    • Topic: TV series
    • Created_at: 2023-09-26 00:06:14
  5. News 5
    • Categories: TV series, crime
    • Topic: crime
    • Created_at: 2023-10-10 10:23:37

If I run the following query

$search = 'TV series';

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

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

I get this list:

  1. News 5
  2. News 4
  3. News 3

I would like this list:

  1. News 4
  2. News 3
  3. News 5

In the second list I would like to obtain, news number 5 is in last position because the topic is different from the search made.

Thanks in advance

0 likes
2 replies
jlrdw's avatar

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.

surfweb's avatar

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');

Please or to participate in this conversation.