CODEWITHSUNDEEP
phplaravellaravel-5eloquent
Yahya Uddin
Yahya Uddin

Reputation: 28861

Laravel: Count number of rows in a relationship

I have the following relationship:

I have the following Eloquent model to represent this:

class Venue {
    public function orders()
    {
        return $this->hasManyThrough(Order::class, Offer::class);
    }
}

I want to determine the total number of orders for venues with location_id = 5 using Laravel's Eloquent model.

The only way I managed to do this is as follows:

$venues = Venue::where('location_id', 5)->with('orders')->get();

$numberOfOrders = 0;
foreach($venues as $venue) {
    $numberOfOrders += $venue->orders->count();
}
dump($numberOfOrders); // Output a single number (e.g. 512)

However, this is obviously not very efficient as I am calculating the count using PHP instead of SQL.

How can I do this using Eloquent model alone.

Upvotes: 5

Views: 13447

Answers (3)

Alex Harris
Alex Harris

Reputation: 6402

If you are using Laravel 5.3 or above you can use withCount.

If you want to count the number of results from a relationship without actually loading them you may use the withCount method, which will place a {relation}_count column on your resulting models. For example:

$venues = Venue::withCount(['orders'])->get;

foreach ($venues as $venue) {
    echo $venue->orders_count;
}

You can read more about withCount in the Laravel Documentation.

If you are using lower than 5.3, you can make a custom relation on your Venue model:

public function ordersCount()
{
    return $this->belongsToMany('App\Models\Order')
        ->selectRaw('venue_id, count(*) as aggregate_orders')
        ->groupBy('venue_id');
}

public function getOrderCount()
{
    // if relation is not loaded already, let's do it first
    if (!array_key_exists('ordersCount', $this->relations)) {
        $this->load('ordersCount');
    }

    $related = $this->getRelation('ordersCount')->first();
    // then return the count directly
    return ($related) ? (int) $related->aggregate_orders : 0;
}

which can then be used as: Venue::with('ordersCount');. The benefit of this custom relation is you only are querying the count rather than the querying all of those relations when they are not necessary.

Upvotes: 2

Mr. Pyramid
Mr. Pyramid

Reputation: 3935

$venues = Venue::with([
    'orders' => function ($q) {
        $q->withCount('orders');
    }
])->get();

then use it this way for getting single record

$venues->first()->orders->orders_count();

Alternatively, you can use this way too for collections

foreach($venues as $venue)
{
echo $venue->order_count;
}

Upvotes: 2

Svetlin Yotov
Svetlin Yotov

Reputation: 136

You can use Eloquent. As of Laravel 5.3 there is withCount().

In your case you will have

$venues = Venue::where('location_id', 5)->with('orders')->withCount('orders')->get();

Then access it this way

foreach ($venues as $venue) {
    echo $venue->orders_count;
}

Can find reference here: https://laravel.com/docs/5.3/eloquent-relationships#querying-relations

Upvotes: 8

Related Questions