Adding a subquery to an eloquent query

So if I understand correctly, an item has many places, and a place has many participants. For this, you can use a HasManyThrough relation on the Item model: http://laravel.com/docs/5.0/eloquent#has-many-through

@bastiaan89 An item has many places and a place has one participant, Anyway I just want to count the number of the related participants that are not approved for each item so I don`t want to fetch all of the participants fields.

and i want to do one query for all the items not one query for each.

is there a way to do this using relations?

@mtvs_dev Yes, there is:

// Item
public function participants()
{
    return $this->hasManyThrough(Participant::class, Place::class);
}

// for convenience add this helper relation and accessor
public function participantsCount()
{
    return $this->participants()
            ->selectRaw('count(*) as aggregate, item_id')
            ->groupBy('item_id');
}
// and accessor like in the article linked below

// then
$items = Item::with('participantsCount')->get();
$items->first()->participantCount; // integer

http://softonsofa.com/tweaking-eloquent-relations-how-to-get-hasmany-relation-count-efficiently/

@JarekTkaczyk Tnx but eager loading participantsCount results to a collection of the participants plus their count.

I read your blog post and noticed that for counting has many relations you used hasOne to avoid a collection but i don't think the same is possible with the hasManyThrough.

So I used the other method that you have described in your blog post and joined the tables and at the end using selectRaw() added the all items fields and the count of their unapproved participants.

Thank you for your useful post.

here is the final code:

Item::leftJoin('places', 'items.id', '=', 'places.item_id')
    ->leftJoin('participants', 'places.id', '=', 'participants.place_id')
    ->groupBy('items.id')
    ->selectRaw('items.*, COUNT(IF(approved = 0, 1, NULL)) AS unapproved_participants');

@mtvs_dev It doesn't matter what you're getting from the count method, because you will use accessor to get the value you need:

$model->participantsCount()->get(); // collection, single model, whatever
$model->participantsCount; // single value, thanks to the accessor:

public function getParticipantsCountAttribute()
{
    $this->loadIfNotLoaded('participantsCount');
    
    $relation = $this->getRelation('participantsCount')->first(); // first, bc it's a collection
    
    return ($relation) ? (int) $relation->aggregate : 0;
}

public function loadIfNotLoaded($relation)
{
    if ( ! array_key_exists($relation, $this->relations)) $this->load($relation);
}