Counting related rows in a child table

I have been trying to do some queries and getting a count on related tables using eloquent.

Tables:

• requests
• contact (belongs to requests)
• history (belongs to contact)

As such X number of requests each have Y number of contacts which in term each have Z number of histories

Using sql I can do something like this to get all the counts.

SELECT
    id,
    (
         SELECT count(contact.id)
         FROM contact 
         WHERE contact.requests_id = requests.id
    ) AS n_contact,
    (
         SELECT count(history.id)
         FROM contact INNER JOIN history ON (history.contact_id = contact.id)
         WHERE contact.requests_id = requests.id
    ) AS n_history
FROM requests;

But I am a bit lost when using eloquent to build queries. If for instance I was selecting all contacts for a given request at what point would I join/count the history? Or do I need to add in some accessor’s into the relevant Models for these 3 tables?

public function getAllContacts($id) {
    return Requests::where('requests.id', '=', $id)
            ->join('requests', 'contact.requests_id', '=', 'requests.id')
            ->select('contact.*', 'requests.name');
            ->get();
}

Thanks in advance for any help.

Answer

You can use helper relation for this, if you’d like to use Eloquent instead of manual joins:

// Request model
public function contactsCount()
{
    return $this->hasOne('Contact')->selectRaw('request_id, count(*) as aggregate')->groupBy('request_id');
}

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

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

The same would go for Contact model towards History model.

For counting far relation (Request -> History) you can use hasManyThrough relation with a little adjustment.

This way you can eager load those aggregates for multiple models without n+1 issue, nice and easy:

$requests = Request::with('contactsCount', 'contacts.historyCount')->get();
// 1 query for reuqests, 1 query for contacts count and 2 queries for contacts.historyCount

// example output
$requests->first()->contactsCount; // 17
$requests->first()->contacts->first()->historyCount; // 5