mysql - Laravel 将 WHERE 添加到 DB::raw 和 Joins

Question

这是我的代码：

return ExportInvoice::join('sold_products', 'sold_products.export_invoice_id', '=', 'export_invoices.id')
        ->join('products', 'sold_products.product_id', '=', 'products.id')
        ->select(
            'sold_products.product_id',
            'products.name',
            DB::raw('
                round(round(sum(round(sold_products.sold_price * sold_products.quantity * (1 - sold_products.discount / 100), 2))
                 * (1 - export_invoices.discount / 100), 2) * (1 + IF(export_invoices.tax, 14, 0) / 100), 2)
                 as total
            ')
        )
        ->groupBy('sold_products.product_id', 'products.name', 'export_invoices.discount', 'export_invoices.tax')
        ->orderBy('total', 'DESC')
        ->limit(100)
        ->get();

我正在尝试做的事情：

我正在尝试添加whereNotNull('export_invoices.deleted_at')，whereNotNull('sold_products.deleted_at')因为我使用的是 Laravel 软删除，并且上面的查询返回所有数据，即使是那些被软删除的数据。

标签： mysqllaravel

Answer 1

如果您无法做到这一点->whereNull('sold_products.deleted_at')，请->whereNull('export_invoices.deleted_at')尝试在select声明后添加此内容。

$columns = ['sold_products.deleted_at', 'export_invoices.deleted_at']

->where(function($q) use ($columns){
    foreach($columns as $column){
        $q->whereNull($columns);
    }
})

所以它会是：

$columns = ['sold_products.deleted_at', 'export_invoices.deleted_at']

return ExportInvoice::join('sold_products', 'sold_products.export_invoice_id', '=', 'export_invoices.id')
        ->join('products', 'sold_products.product_id', '=', 'products.id')
        ->select(
            'sold_products.product_id',
            'products.name',
            DB::raw('
                round(round(sum(round(sold_products.sold_price * sold_products.quantity * (1 - sold_products.discount / 100), 2))
                 * (1 - export_invoices.discount / 100), 2) * (1 + IF(export_invoices.tax, 14, 0) / 100), 2)
                 as total
            ')
        )->where(function($q) use ($columns){
           foreach($columns as $column){
             $q->whereNull($columns);
           }
})->groupBy('sold_products.product_id', 'products.name', 'export_invoices.discount', 'export_invoices.tax')
        ->orderBy('total', 'DESC')
        ->limit(100)
        ->get();

我希望它有帮助！