Laravel search by date plus time from releated table

user_4907288 0
Viewed 35

I have table order related to warehouse with related to warehouse_delivery.
With following structure:

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

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

Delivery time is stored as days.

And now I would like to search by send_date to find Orders where send_date + domestic_delivery_time is today.
I don't have a idea how can I grab that domestic_delivery_time added to send_date.

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

How to write query?
Thank you.

eloquent laravel
1 Answers

I'm not sure there's a database-agnostic solution for this.

You'd need to use DB::raw. Here's a solution using a scope that covers MySQL, SQL Server and 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();
user_49056370
Related