Getting count from pivot table in laravel eloquent

Question

I have a many to many relationship for orders and products.

<?php
class Order extends Eloquent {

    public function user()
    {
        return $this->belongsTo('User');
    }

    public function products()
    {
        return $this->belongsToMany('Product');
    }
 }
 ?>


<?php
class Product extends Eloquent {

    public function orders()
    {
        return $this->belongsToMany('Order');
    }

 }
?>

Need to fetch the number of times each product is ordered.In mysql,this task can be achieved by using the following query

SELECT products.id, products.description, count( products.id )
FROM products
INNER JOIN order_product ON products.id = order_product.product_id
INNER JOIN orders ON orders.id = order_product.order_id
GROUP BY product_id
LIMIT 0 , 30

Result of the above query is as follows:-

id  description   count(products.id)    
 1     Shoes          3
 2     Bag            2
 3     Sun glasses    2
 4     Shirt          2

How this task can be achieved using laravel eloquent (without using query builder)????How can i fetch the number of times each product is ordered using laravel eloquent??

Solution 1

Mind that Eloquent uses Query\Builder under the hood, so there is no such thing in Laravel, like 'query eloquent without using query builder'.

And this is what you need:

// additional helper relation for the count
public function ordersCount()
{
    return $this->belongsToMany('Order')
        ->selectRaw('count(orders.id) as aggregate')
        ->groupBy('pivot_product_id');
}

// accessor for easier fetching the count
public function getOrdersCountAttribute()
{
    if ( ! array_key_exists('ordersCount', $this->relations)) $this->load('ordersCount');

    $related = $this->getRelation('ordersCount')->first();

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

This will let you take advantage of eager loading:

$products = Product::with('ordersCount')->get();

// then for each product you can call it like this
$products->first()->ordersCount; // thanks to the accessor

Read more about Eloquent accessors & mutators,

and about dynamic properties, of which behaviour the above accessor mimics.

---

Of course you could use simple joins to get exactly the same query like in you example.

Solution 2

For future viewers, as of Laravel 5.2, there is native functionality for counting relationships without loading them, without involving your resource model or accessors -

In the context of the example in the approved answer, you would place in your controller:

$products = Product::withCount('orders')->get();

Now, when you iterate through $products on your view, there is a orders_count (or, generically, just a {resource}_count) column on each retrieved product record, which you can simply display as you would any other column value:

@foreach($products as $product)
    {{ $product->orders_count }} 
@endforeach

This method produces 2 fewer database queries than the approved method for the same result, and the only model involvement is ensuring your relationships are set up correctly. If you're using L5.2+ at this point, I would use this solution instead.

Solution 3

If you already have the $products object, you can do the following:

$rolecount = $products->roles()->count();

Or if you are using eager loading:

$rolecount = $products->roles->count();

Cheers.

Comments

[developer34]

The code is working fine and i m getting the desired result now.Thanks for ur help :):) but one more question.Do the function getOrdersCountAttribute() is automatically called when accessing ordersCount() ????Can you explain how these two functions go in flow???

[Jarek Tkaczyk]

heck the edit - added links to the docs, where you can find all you need. Basically accessors/mutators take advantage of magic __get/__set and let you manipulate model's attributes so they fit your needs. created_at and other timestamps work pretty much the same way.

[developer34]

I completely forgot about the accessors/mutators.Thanks for the reference !!! :):)

[ElChupacabra]

I'd give you +10 if I could. This should be accepted answer.