Eloquent query with count of objects in a one-to-many relationship

You might try something like:

class Store extends \Eloquent
{
    protected $fillable = ['name', 'location'];

    public function items()
    {
        return $this->hasMany('Item');
    }
}

class Item extends \Eloquent
{
    protected $fillable = [];

    public function store()
    {
        return $this->belongsTo('Store');
    }
}

Then wherever you retrieve your records:

$stores = Store::with('items')->all();
foreach ($stores as $store) {
    $itemCount = $store->items()->count();
}

Hopefully that gets you close to something useable. :)

If you're trying to limit the number of DB queries you do, you might try something along the lines of this:

Store::join('items', 'items.store_id', '=', 'stores.id')
    ->groupBy('stores.id')
    ->get(['stores.id', 'stores.name', DB::raw('count(items.id) as items')])
);

Thanks, Jeffrey. That did it. I would've never thought of that. The only thing I don't like is that stores with 0 items won't appear on the list, but I remember this being an issue for me in another webapp I made using straight PHP/MySQL.

Mike, I figured I could do something similar, but since I was fetching the list of all items associated with the stores instead of the count, I figured that wouldn't be very efficient.

@roulette6 - Then do Store::leftJoin() instead.

Great! This solved my issue exactly. Thanks a lot.

will this solution work in ms-sql? i am getting the following error "invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause".

There's more convenient way to do this using helper relation, that allows you to load and access the count easily:

$stores = Store::with('itemsCount')->get(); // single relational query for multiple aggregates
$store = $stores->first();
$store->itemsCount; //  returns unsigned integer

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