0

I am showing a list of categories, and the article count within each category. I get the expected result, but I am having the N+1 problem.

My CategoriesController index function:

public function index()
{
    return View::make('categories.index', [
        'articleCategories' => Category::where('type', 'articles')->orderBy('name')->get(),
    ]);
}

The Category model has many relationship to articles:

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

My categories.index view:

@foreach($articleCategories as $articleCategory)
    <p>
    {{ HTML::link(URL::route('articles.category', array('category' => Str::slug($articleCategory->name))), $articleCategory->name) }}
    {{ $articleCategory->articles->count() }}
    </p>
@endforeach

Edit: It works if I eager load all related articles, but since I only need the article count pr category this seems overkill. Will eager loading articles and do ->count() impact performance? Or is this the best way to do it?

Thomas Jensen
  • 2,138
  • 2
  • 25
  • 48
  • Why don't you do: `count($articleCategory->articles);`? I think that's most easy and most fast. – ArjanSchouten Sep 23 '14 at 20:09
  • Thanks for your reply. I get it working with that method, but ideally I would like to not load the all related articles. I have updated my question. – Thomas Jensen Sep 23 '14 at 21:15
  • 1
    Loading multiple Eloquent models is overkill. If you have 100 rows, then you don't need to worry about it, but for thousands+ I wouldn't go that way definitely. – Jarek Tkaczyk Sep 23 '14 at 21:23

2 Answers2

1
// helper relation
public function articlesCount()
{
    return $this->hasOne('Article')->selectRaw('category_id, count(*) as aggregate')->groupBy('category_id');
}

// and accessor for fetching it easier
public function getArticlesCountAttribute()
{
    if ( ! array_key_exists('articlesCount', $this->relations)) $this->load('articlesCount');

    return $this->getRelation('articlesCount')->aggregate;
}

Then you can do this:

// eager load in single query
$categories = Category::with('articlesCount')->get();

// thanks to accessor this will return value and load relation only if needed
$categories->first()->articlesCount;
Jarek Tkaczyk
  • 78,987
  • 25
  • 159
  • 157
0

Try this 

public function index()
{
    return View::make('categories.index', [
        'category' => Category::with('articles')->where('type', 'articles')->orderBy('name')->get(),
    ]);
}

Now in View to get the category data just do

$category->type or if you have a name field you can get the name by$category->name

To get the articles of the category you can do 

foreach($category->articles as $article)
// do something with the articles
@endforeach

To get the count of articles of a category do $category->articles->count();

Make sure you also read documentation of Eager loading once, it really helps a lot.

Alley Shairu
  • 1,234
  • 1
  • 7
  • 11
  • Thanks, I am familiar with this method. But since I only need the count it seems overkill to load all the related articles. Will this impact performance? – Thomas Jensen Sep 23 '14 at 20:23
  • I get your point, I don't think there will be much of a performance issue, but the other way I can think of is by left joining the articles on category model then counting the number of articles in countArticles method. The traditional method, I believe. – Alley Shairu Sep 23 '14 at 20:35