Getting count from pivot table in laravel eloquent

Question

I have many different relationships 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'); } } ?> 

It is required to get the number of times each product is ordered. In mysql, this task can be completed 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 

The 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 can this task be achieved using laravel eloquent (without using the query builder)? How can I get the number of times each product is ordered using laravel eloquent ??

Answer 1

Note that Eloquent uses Query\Builder under the hood, so there is no such thing as “eloquent query without using the query builder” in Laravel.

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 allow you to use convenient downloads:

 $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 and Mutators ,

and dynamic properties , the behavior of which is given above.

---

Of course, you can use simple joins to get exactly the same query as in your example.

Answer 2

For future viewers, starting with Laravel 5.2, there is a built-in function for calculating relationships without loading them without involving your model of resources or accessories -

In the context of the example in the approved answer, you will post in your controller:

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

Now, when you iterate through $ products in your view, there is a orders_count column (or usually only << 22>) for each received product record, which you can simply display, like any other column value:

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

This method makes 2 queries to the database than the approved method for the same result, and the only involvement of the model is the correct establishment of your relationship. If you are using L5.2 + at this point, I would use this solution.

Answer 3

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

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

Or if you are using a downloadable download:

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

Greetings.

Answer 4

I am using Laravel 5.1 and I can do it by doing this

  $photo->posts->count() 

And the posts method in Photo model is as follows

 public function posts(){ return $this->belongsToMany('App\Models\Posts\Post', 'post_photos'); }