How to sort by fields in the form of a tree in Laravel?

Question

Laravel

There are two models:
1) Category. I did something like here https://tjd-studio.com/blog/laravel-categories-wit... , there can only be two levels !
2) News. News stores category_id, and you can select a category of any level (1 or 2). There is a lot of news, it needs to be shown with pagination.

Категория 1
   Новость 53
   Новость 12
   - Подкатегория 1
       Новость 32
       Новость 23
   - Подкатегория 2
       Новость 21
       Новость 24
Категория 2
   Новость 1
   Новость 2
   - Подкатегория 3
       Новость 56
       Новость 57
[Пагинация]

There are more categories and subcategories, but we are now showing N news on the first page, which means only a few categories, there may already be other categories on the next page.
those. we have to take the first N news sorted by the category.name relation, while I think in the direction of joins, but it turns out that we need to join twice to select the parent of the category.
How easy is it to get here?

Answer 1

So far I've done this:

$news = News::select('news.*')
    ->leftJoin('categories AS a', 'a.id', '=', 'news.category_id')
    ->leftJoin('categories AS b', 'b.id', '=', 'a.parent_id')
    ->orderByRaw('IF(b.id IS NULL, a.name, b.name)')
    ->orderByRaw('IF(b.id IS NULL, NULL, a.name)')
    ->orderBy('news.name')
    ->paginate($limit);

It seems to work.

Answer 2

Write a request as you need for business and cache it. There will be no performance issues even without a cache.