按日期和发布表的时间进行的Laravel搜索

Question

我有与warehouse相关的表warehouse和与warehouse_delivery相关的表。

结构如下：

+--------------------------------------+    +-----------+
|                Order                 |    | Warehouse |
+----+--------------+------+-----------+    +----+------+
| id | warehouse_id | cost | send_date |    | id | name |
+----+--------------+------+-----------+    +----+------+

+--------------------------------------------------------------------------+
|                            WarehouseDelivery                             |
+----+--------------+------------------------+-----------------------------+
| id | warehouse_id | domestic_delivery_time | international_delivery_time |
+----+--------------+------------------------+-----------------------------+

交付时间以天数形式存储。

现在我想通过send_date搜索Orders，send_date + domestic_delivery_time现在在哪里。

我不知道如何获得添加到send_date中的那个send_date。

Order::where('warehouse_id', $warehouseId)
->with(['product', 'warehouse.warehouseDelivery'])
->get();

如何写查询？

谢谢。

Answer 1

我不确定是否有数据库不可知论的解决方案。

您需要使用DB::raw。下面是使用涵盖MySQL、Server和PostgreSQL的作用域的解决方案：

// On Owner.php
public function scopeDueToday($query) {
    $today = \Carbon\Carbon::now()->format('Y-m-d');
    $driver = $query->getConnection()->getDriverName();

    return $query->whereHas('warehouse.warehouseDelivery', function ($deliveries) use ($today, $driver) {

        $raw = [
            'mysql' => "CAST(start_date + INTERVAL domestic_delivery_time days AS date)",
            'pgsql' => "(start_date + (domestic_delivery_time || ' day')::interval)::date",
            'sqlsrv' => "CONVERT(date, DATEADD(day, domestic_delivery_time, start_date))",
        ];

        return $deliveries->where(\DB::raw($raw[$driver]), '=', $today);
    });
}

// On your controller
Order::dueToday()->where('warehouse_id', $warehouseId)
        ->with(['product', 'warehouse.warehouseDelivery'])->get();