laravel - 如何查询与最大数量的三个关系？

Question

我有 3 个模型

1.SellInvoice模型：

protected $table = "sell_invoices";

public function jewelsItems(){
  return $this->hasMany(JewelsItem::class,'buy_invoice_id');
}

2.宝石型号：

protected $table = 'jewel';

public function jewelsItems(){
return $this->hasMany('App\Models\JewelsItem');
}

3.JewelsItem型号：

protected $table = 'jewel_items';

public function jewel(){
  return $this->belongsTo('App\Models\Jewel');
}

public function sellInvoice(){
    return $this->belongsTo(SellInvoice::class,'sell_invoice_id');
}

查询：在我的数据库中查找 10 个最畅销的珠宝或对某个日期的销售数量进行排序。

注意：我在 SellInvoice 中有一个 InvoiceDate，必须使用它来了解从某个日期开始最畅销的珠宝

尝试：我尝试通过销售的商品获取组，该商品的销售日期为 2021-03-1：

JewelsItem::whereHas('sellInvoice',function ( $query) {
        $query->where('InvoiceDate','>=','2021-03-1');
    })->groupby('jewel_id')->get();

但这让我出错：

语法错误或访问冲突：1055 'laravel_goldshop.jewel_items.id' 不在 GROUP BY 中（SQL：select * from jewel_itemswhere exists (select * from sell_invoiceswhere jewel_items. sell_invoice_id= sell_invoices. idand InvoiceDate>= 2021-03-1) group by jewel_id）

标签： laraveleloquentlaravel-query-builder

Answer 1

set global 
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';