一对多关联查询，如何用副表中统计出来的字段来进行排序

Question

$csMerchant = CsMerchant::query()
    ->where('status', CsMerchant::STATUS_ON)
    ->where('audit_status', CsMerchant::AUDIT_STATUS_SUCCESS)
    ->with(['csGoods' => function ($query) {
        $query->where('status', CsGoods::STATUS_ON)
            ->where('audit_status', CsGoods::AUDIT_STATUS_SUCCESS)
            ->groupBy('cs_merchant_id')
            ->select('cs_merchant_id', DB::raw('SUM(sale_count_yesterday) AS yesterday_total_sals'))
    }])
    ->get();

请问如上写法，如何用 yesterday_total_sals 字段来进行排序呢，如何改造，请指教

Answer 1

$csMerchant = CsMerchant::query()
      ->where('status', CsMerchant::STATUS_ON)
      ->where('audit_status', CsMerchant::AUDIT_STATUS_SUCCESS)
      ->withCount(['csGoods as yesterday_total_sals' => function ($query) {
           $query->where('status', CsGoods::STATUS_ON)
               ->where('audit_status', CsGoods::AUDIT_STATUS_SUCCESS)
               ->select(DB::raw("sum(sale_count_yesterday)"));
           }])
      ->orderBy('yesterday_total_sals', 'desc')
      ->get();