Question 262365 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel eloquent: OrderBy special condition

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

News Table

id title text
1 News 1 –
2 News 2 –
3 News 3 –
4 News 4 –
5 News 5 –

Category Table

id name
1 film
2 anime
3 book
4 series
5 TV series
6 police
7 crime

Topic Table

id name
1 film
2 anime
3 book
4 marvel
5 TV series
6 police
7 crime

Related post/category Table

id post_id category_id
1 1 1
2 1 2
3 2 3
4 2 2
5 3 5
6 3 4
7 4 5
8 4 6
9 5 5
10 5 7

Related post/topic Table

id post_id topic_id
1 1 1
2 2 3
3 3 5
4 4 5
5 5 7

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.

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.'%');
        
})
->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 ‘TV series’.

Can anyone help me?

Thanks in advance

EDIT

  • Filter news items by category
  • I check whether the searched category also corresponds to a topic
  • If the topic check is successful I take those news items and put them first
  • List all other news items
Tags:

2

Answers


  1. 0

    If you want to create a custom search order, you need to use FIELD(). As far as I know, there’s no eloquent counterpart for that, so you need to use orderByRaw() as follows:

    orderByRaw('FIELD(topic_id, 5) DESC, created_at ASC')

    if you wanted results for topic_id 5 first and then all other, sorted by created_at.

    Login or Signup to reply.
  2. 0

    In case I got you correct, this should do it:

    $search = 'TV series';

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

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

    The trick is, first sort by the latest news and after that order by the topic.
    The other way around it would NOT work.

    EDIT:
    I didn’t get it right. As I now understand the search muste be done in the topic_related table. Then the result has to be UNION ALLed with the news that did not match the search.

    That is what says. But I believe it is still not what he really wants.
    Please say it again in short clear sentences without mixing up categories and topics. Where do you wand to search? What do you want to order by? Isn’t category and topic the same (at least as you use it)?

    Sorry, no offense. I might be the problem here.

    Good luck!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search