How to translate a simple nested query into Eloquent?

Question

Laravel

There is one simple mysql query. But I need to convert it to Eloquent. I've looked all over and can't find the right design. Here is the request itself:

select c1.name, c1.id, (
    select count(*)
    from categories as c2
    where c2.id = c1.id
    group by c2.id) as rating
from categories c1;

If you do it through query builde, everything is simple there. but I need Eloquent to pull related entities in nested arrays.

Answer 1

I wrote the code, but did not check whether it works, but the idea is this:

// Создаем связь, можно методом в модели, можно на лету, как в примере
Category::resolveRelationUsing('categoryForRating', fn($q) => $orderModel->belongsTo(Category::class, 'id', 'id'));
Category::
  withCount('categoryForRating as rating')
  ->get(['name', 'id', 'rating_count as rating']);

Answer 2

withCount()

Answer 3

Your request looks like nonsense, perhaps there is an error in it and you need to join by parant_id if you are making a tree.
Whereas you were prompted withCount and relaying on yourself to prescribe