使用Laravel雄辩器获取嵌套实体的计数

Question

我有3个数据库表客户端优惠券和类别

clients table

id，名字，网站，描述，标志，鼻涕虫

categories table

身份证，名字，鼻涕虫

coupons table

id，client_id，category_id，类型，优惠券，标题，描述，链接，视图，段塞，过期

这种关系是: 1)许多优惠券属于客户(多到一种关系)；2)许多优惠券属于类别(多到一种关系)

我用的是拉拉5.1。

如何获得客户端的唯一计数和客户端的详细信息、客户端的优惠券数量以及单个客户的总类别数。

简化:我需要获得客户端的详细信息，并显示某类特定客户端的xxx数量的优惠券数量。

到目前为止，我可以得到唯一的客户详细信息和优惠券的数量计数。

 public function getAvailableClientsWithItemCountList($page = 1)
{
    return Client::join('coupons', 'clients.id', '=', 'coupons.client_id')
        ->join('categories', 'coupons.category_id', '=', 'categories.id')
        ->where('coupons.expiry', '>', Carbon::today())
        ->groupBy('clients.id')
        ->skip(STORES_PER_REQUEST*($page-1))
        ->take(STORES_PER_REQUEST)
        ->get(['clients.id', 'clients.name', 'clients.slug', 'clients.logo', DB::raw('count(clients.id) as dealsCount'), DB::raw('count(categories.id) as categoriesCount')]);
}

STORES_PER_REQUEST =9(常数)用于分页。提前谢谢。

Answer 1

好的，我想出了自己的额外信息，作为优惠券类型和类别可用。我所做的键只是在count中添加了情况，并删除了类别表的联接。

最后的代码如下

return App\Client::join('coupons', 'clients.id', '=', 'coupons.client_id')
    ->where('coupons.expiry', '>', \Carbon\Carbon::today())
    ->orderBy('clients.position', 'desc')
    ->groupBy('clients.id')
    ->skip(STORES_PER_REQUEST*(1-1))
    ->take(STORES_PER_REQUEST)
    ->get(['clients.id', 'clients.name', 'clients.slug', 'clients.logo', DB::raw('count(clients.id) as total'), DB::raw('count(CASE WHEN coupons.type=\'Coupon\' THEN 1 ELSE NULL END) as couponsCount'), DB::raw('count(CASE WHEN coupons.type=\'Deals\' THEN 1 ELSE NULL END) as dealsCount'), DB::raw('count(Distinct category_id) as categoriesCount')]);

结果是

[{
"id": "8",
"name": "Archies Online",
"slug": "archies-online",
"logo": "Archiesonline.jpg",
"total": "22",
"couponsCount": "20",
"dealsCount": "2",
"categoriesCount": "9"
}, {
"id": "5",
"name": "Shop Clues",
"slug": "shop-clues",
"logo": "Shopclues.jpg",
"total": "24",
"couponsCount": "24",
"dealsCount": "0",
"categoriesCount": "9"
}, {
"id": "6",
"name": "Lens Kart",
"slug": "lens-kart",
"logo": "Lenskart.jpg",
"total": "25",
"couponsCount": "25",
"dealsCount": "0",
"categoriesCount": "8"
}, {
"id": "7",
"name": "Amazer",
"slug": "amazer",
"logo": "Amzer.jpg",
"total": "21",
"couponsCount": "21",
"dealsCount": "0",
"categoriesCount": "8"
}, {
"id": "1",
"name": "Flipkart",
"slug": "flipkart",
"logo": "Flipkart.jpg",
"total": "17",
"couponsCount": "17",
"dealsCount": "0",
"categoriesCount": "9"
}, {
"id": "2",
"name": "Make My Trip",
"slug": "make-my-trip",
"logo": "Makemytrip.jpg",
"total": "11",
"couponsCount": "11",
"dealsCount": "0",
"categoriesCount": "8"
}]

这是目前的诀窍:)；

Answer 2

如果你已经建立了你的关系，你可以这样做：

 /**
 * Mock relationship for eager loading coupon count
 *
 * @return mixed
 */
public function couponCount()
{
    return $this->hasOne(Coupon::class)
        ->selectRaw('client_id, count(*) as aggregate')
        ->groupBy('client_id');

}

public function getCouponCountAttribute()
{
    // if relation is not loaded already, let's do it first
    if (!$this->relationLoaded('couponCount')) {
        $this->load('couponCount');
    }

    $related = $this->getRelation('couponCount');

    // then return the count directly
    return ($related) ? (int) $related->aggregate : 0;
}

以上内容可以用于您的Client模型，然后您只需修改Category模型的couponCount关系方法(如果您愿意的话)。

然后为您的Category计数添加以下内容：

 /**
 * Mock relationship for eager loading category count
 *
 * @return mixed
 */
public function categoryCount()
{
    return $this->hasOne(Coupon::class)
        ->selectRaw('category_id, count(*) as aggregate')
        ->groupBy('client_id, category_id');

}

public function getCategoryCountAttribute()
{
    // if relation is not loaded already, let's do it first
    if (!$this->relationLoaded('categoryCount')) {
        $this->load('categoryCount');
    }

    $related = $this->getRelation('categoryCount');

    // then return the count directly
    return ($related) ? (int) $related->aggregate : 0;
}

然后，您可以在Coupon模型中添加一个查询范围，以获取未过期的优惠券，如下所示：

public function scopeActive($query)
{
    $query->where('expiry', '>', Carbon::today());
}

如果你只想得到没有过期的优惠券的数量，你可以把它直接添加到关系中，比如groupBy('client)id')->active()

现在，您应该能够像这样加载这种关系：

 $clients = Client::with('couponCount', 'clientCount')
    ->skip(STORES_PER_REQUEST * ($page - 1))
    ->take(STORES_PER_REQUEST)
    ->get();

或者可以将查询作用域附加到急切的加载，即

 $clients = Client::with(['couponCount' => function ($q) {$q->active()}, 'clientCount' => function ($q) {$q->active()}]) ...

希望这能有所帮助！