One to many relationship count - difference in acc

Question

I have one to many relation - Entry can have many Visits.

In my Entry model I have the following methods:

public function visits() {
    return $this->hasMany ('Visit', 'entry_id','id');
}

public function visitsCount() {
    return $this->hasMany('Visit', 'entry_id','id')
        ->selectRaw('SUM(number) as count')
        ->groupBy('entry_id');
}

In Blade I can get number of visits for my entry using:

{{$entry->visits()->count() }}

or

{{ $entry->visitsCount()->first()->count }}

If I want to create accessor for getting number of visits I can define:

public function getNrVisitsAttribute() 
{
    $related = $this->visitsCount()->first();
    return ($related) ? $related->count : 0;
}

and now I can use:

{{ $entry->nr_visits }}

Questions:

1. In some examples I saw defining such relation this way:

   public function getNrVisitsAttribute()
   {    
       if (!array_key_exists('visitsCount', $this->relations)) {
           $this->load('visitsCount');
       }
       $related = $this->getRelation('visitsCount')->first();
       return ($related) ? $related->count : 0;
   }
   

   Question is: what's the difference between this and the "simple method" I showed at the beginning? Is it quicker/use less resource or ... ?

2. Why this method doesn't work in this case? $related is null so accessor return 0 whereas using "simple method" it returns correct number of visits

I've tried also changing in visitsCount method relationship from hasMany to hasOne but it doesn't change anything.

Answer 1

1 Your relation won't work because you didn't select the foreign key:

public function visitsCount() {
    // also use hasOne here
    return $this->hasOne('Visit', 'entry_id','id')
        ->selectRaw('entry_id, SUM(number) as count')
        ->groupBy('entry_id');
}

2 Your accessor should have the same name as the relation in order to make sense (that's why I created those accessors in the first place):

public function getVisitsCountAttribute() 
{
   if ( ! array_key_exists('visitsCount', $this->relations)) $this->load('visitsCount');

   $related = $this->getRelation('visitsCount');

   return ($related) ? $related->count : 0;
}

This accessor is just a handy way to call the count this way:

$entry->visitsCount;

instead of

$entry->visitsCount->count;
// or in your case with hasMany
$entry->visitsCount->first()->count;

So it has nothing to do with performance.

Also mind that it is not defining the relation differently, it requires the relation to be defined like above.

Answer 2

Assuming your schema reflects one record / model per visit in your visits table, The best method would be to get rid of the visitsCount() relation and only use $entry->visits->count() to retrieve the number of visits to the entry.

The reason for this is that once this relation is loaded, it will simply count the models in the collection instead of re-querying for them (if using a separate relationship)

If your concern is overhead and unnecessary queries: My suggestion would be to eager-load these models in a base controller somewhere as children of the user object and cache it, so the only time you really need to re-query for any of it is when there have been changes.

BaseController:

public function __construct(){

    if(!Cache::has('user-'.Auth::user()->id)){
        $this->user = User::with('entries.visits')->find(Auth::user()->id);
        Cache::put('user-'.Auth::user()->id, $this->user, 60);
    } else {
        $this->user = Cache::get('user-'.Auth::user()->id);
    }

}

Then set up an observer on your Entry model to flush the user cache on save. Another possibility if you are using Memcached or Reddis would be to use cache tags so you don't have to flush the whole user's cache every time an Entry model is added or modified.

Of course, this also assumes that each Entry is related to a user, however, if it isn't and you need to use Entry alone as the parent, the same logic could apply, by moving the Cache class calls in your EntryController