Laravel / Lumen关系 - 加入条件检索错误的其他表

Question

I'm experiencing a weird problem retrieving some data from the database when comparing two dates.

Database structure: Users, reservations, reservation_services, services_price_history

Based on the above tables: An user has many reservations, a reservation have many services, and a service has many price histories.

I need to take all user reservations, then find the service price based on the reservation date.

The problem is that when I add the date comparation condition it's not taking it into consideration

The code:

$users = User::with(['reservations' => function($query) use ($department_id, $date_start, $date_end) {

    // reservation services
    $query->with(['reservation_services' => function($query) {
        $query->join('reservations', 'reservation_services.reservation_id', 'reservations.id');
        $query->join('services', 'reservation_services.service_id', 'services.id');

        // Find the service price based on reservation date
        $query->join('services_price_history', function($query) {
            $query->on('reservations.salon_id', 'services_price_history.salon_id');
            $query->on('reservation_services.service_id', 'services_price_history.service_id');

            // this shoud be the condition that gives us the correct
            // price based on the reservation date
            $query->where('services_price_history.date_start', '<', 'reservations.date');
           // then we use order and limit 1
        });

        $query->select(
            'reservation_services.reservation_id',
            'reservation_services.service_id',
            'reservation_services.quantity',
            'services_price_history.price',
            'services_price_history.date_start',
            'services.name',
            DB::raw('(services_price_history.price * reservation_services.quantity) as total')
        );
        $query->orderBy('services_price_history.id', 'desc');
    }]);
}])->get();

This is the output:

[
   {
      "id":10,
      "first_name":"Mihaela",
      "last_name":"Radulescu",
      "reservations":[
         {
            "id":112,
            "salon_id":2,
            "client_id":161,
            "user_id":10,
            "date":"2017-10-31", // the reservation date
            "start":"10:00",
            "end":"12:00",
            "state_id":5,
            "notes":null,
            "price":"350.00",
            "reservation_services":[
               {
                  "reservation_id":112,
                  "service_id":89,
                  "quantity":1,
                  "price":"400.00",
                  "date_start":"2017-11-02", // this is wrong
                  "name":"Tratament ANTI AGE",
                  "total":"400.00"
               },
               {
                  "reservation_id":112,
                  "service_id":89,
                  "quantity":1,
                  "price":"350.00",
                  "date_start":"2017-10-19", // this is ok
                  "name":"Tratament ANTI AGE",
                  "total":"350.00"
               }
            ],
            "reservation_products":[

            ]
         },
      ]
   },
],

I don't really know why but this line on code

$query->where('services_price_history.date_start', '<', 'reservations.date');

is not working as it shoud because '2017-11-02' is higher than '2017-10-31' but it shows up in results.

Thank you very much.

Answer 1

Instead of:

$query->where('services_price_history.date_start', '<', 'reservations.date');

use:

$query->whereRaw('services_price_history.date_start < reservations.date');