Laravel eloquent get relation count

you can use withCount(). It is available from 5.3 version

for more info about eloquent visit : https://laravel.com/docs/5.3/eloquent-relationships

Define a articles() relation in your Category model as:

public function articles() 
{
    return $this->hasMany(Article::class, 'cat_id');
}

Then you can try it as:

Category::where('parent_id', 0)->withCount('articles')->get();

You can use the hasManyThrough() Eloquent method to fetch all of the childrens' Articles, then add the article counts together in a nice little getter. I added the getter to the $appends array on the model to help illustrate it in the Tinker output.

class Category extends Model
{

    protected $appends = [
        'articleCount'
    ];

    public function articles()
    {
        return $this->hasMany(Article::class);
    }

    public function children()
    {
        return $this->hasMany(Category::class, 'parent_id');
    }

    public function childrenArticles()
    {
        return $this->hasManyThrough(Article::class, Category::class, 'parent_id');
    }

    public function getArticleCountAttribute()
    {
        return $this->articles()->count() + $this->childrenArticles()->count();
    }
}

Here's the Tinker output:

Psy Shell v0.8.0 (PHP 7.0.6 — cli) by Justin Hileman
>>> $cat = App\Category::first();
=> App\Category {#677
     id: "1",
     name: "Cooking",
     parent_id: null,
     created_at: "2016-12-15 18:31:57",
     updated_at: "2016-12-15 18:31:57",
   }
>>> $cat->toArray();
=> [
     "id" => 1,
     "name" => "Cooking",
     "parent_id" => null,
     "created_at" => "2016-12-15 18:31:57",
     "updated_at" => "2016-12-15 18:31:57",
     "articleCount" => 79,
   ]
>>> 

If you want to restrict your Category query to ones that have children that have articles, you could do that using the has() method:

Category::has('children.articles')->get();

Here's more on the has() method:

https://laravel.com/docs/5.3/eloquent-relationships#querying-relationship-existence

And the hasManyThrough() method:

https://laravel.com/docs/5.3/eloquent-relationships#has-many-through