在laravel eloquent中从数据透视表中获取计数

2021-12-26 00:00:00 count php laravel laravel-4 eloquent

我有订单和产品的多对多关系.

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');
    }

 }
?>

需要获取每个产品的订购次数.在mysql中,这个任务可以通过使用下面的查询来实现

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

以上查询结果如下:-

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

如何使用 laravel eloquent 完成这项任务(不使用查询构建器)????如何使用 laravel eloquent 获取每个产品的订购次数??

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??

推荐答案

注意 Eloquent 在底层使用 QueryBuilder,所以 Laravel 中没有这样的东西,就像'查询雄辩而不使用查询构建器'.

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

这就是你所需要的:

// 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

阅读更多关于 Eloquent accessors &变异子,

以及动态属性,上面的访问器模仿了这些行为.

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.

相关文章