Laravel, получите минимальную цену в подзапросе

Имея следующий рабочий код:

$greatDeals = Deal::whereHas('hotel', function ($query) {
                        $query->whereHas('room', function ($query) {
                            $query->Where('astatus', 1)->Where('status', 0);
                        })->Where('astatus', 1)->Where('status', 0);
                    })->with(['hotel' => function ($query) {
                        $query->with('room')->get(); //need to get minimum price from room table
                    }])
                    ->where('date', '>=', Carbon::today()->toDateString())
                    ->where('status', 1)
                    ->orderBy('discount', 'desc')
                    ->limit(9)
                    ->get();

В приведенном выше запросе i need to get minimum price from room table возможно ли это с существующим запросом?

Обновление: мой текущий результат:

[  
   {  
      "id":5,
      "hotel_id":1,
      "deal_code":"units",
      "date":"2018-03-30 00:00:00",
      "discount":80,
      "status":1,
      "created_at":"2018-03-16 07:55:32",
      "updated_at":"2018-03-16 07:55:32",
      "deleted_at":null,
      "client_id":2,
      "hotel":{  
         "id":1,
         "name":"Testing Grounds",
         "address":"1-23 City Rd, Southbank VIC 3006, Australia",
         "phone":"9842814927",
         "country":"4",
         "country_name":"American Samoa",
         "state":"166",
         "state_name":"Swains Island",
         "city":"48331",
         "city_name":"Swains Island",
         "area":"test",
         "pincode":"3006",
         "nearest_airport":"test",
         "nearest_railway":"teste",
         "star":"5",
         "check_in":"05:15 AM",
         "check_out":"10:30 AM",
         "lat":"-39",
         "lng":"144.967621",
         "description":"tsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaa",
         "astatus":"1",
         "status":"0",
         "created_at":"2018-03-16 07:28:28",
         "updated_at":"2018-03-16 07:31:22",
         "client_id":2,
         "hotel_age":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsa",
         "hotel_privilege":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsa",
         "departure":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsa",
         "checkout_fee":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsa",
         "book_policy":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsad",
         "hotel_secured":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsa",
         "hotel_pets":"Allowed",
         "hotel_other":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsa",
         "room":[  
            {  
               "id":2,
               "roomtype_id":1,
               "room_count":null,
               "max_adult":2,
               "max_children":2,
               "capacity":4,
               "room_number":null,
               "floor":null,
               "number":null,
               "name":null,
               "city_name":"Swains Island",
               "city":48331,
               "price":"500.00",
               "extra_bed_price":null,
               "astatus":"1",
               "status":"0",
               "created_at":"2018-03-16 07:51:17",
               "updated_at":"2018-03-16 07:52:20",
               "deleted_at":null,
               "client_id":2,
               "hotel_id":1
            }
         ]
      }
   },
   {  
      "id":4,
      "hotel_id":1,
      "deal_code":"unit",
      "date":"2018-03-28 00:00:00",
      "discount":70,
      "status":1,
      "created_at":"2018-03-16 07:55:09",
      "updated_at":"2018-03-16 07:55:09",
      "deleted_at":null,
      "client_id":2,
      "hotel":{  
         "id":1,
         "name":"Testing Grounds",
         "address":"1-23 City Rd, Southbank VIC 3006, Australia",
         "phone":"9842814927",
         "country":"4",
         "country_name":"American Samoa",
         "state":"166",
         "state_name":"Swains Island",
         "city":"48331",
         "city_name":"Swains Island",
         "area":"test",
         "pincode":"3006",
         "nearest_airport":"test",
         "nearest_railway":"teste",
         "star":"5",
         "check_in":"05:15 AM",
         "check_out":"10:30 AM",
         "lat":"-39",
         "lng":"144.967621",
         "description":"tsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaa",
         "astatus":"1",
         "status":"0",
         "created_at":"2018-03-16 07:28:28",
         "updated_at":"2018-03-16 07:31:22",
         "client_id":2,
         "hotel_age":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsa",
         "hotel_privilege":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsa",
         "departure":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsa",
         "checkout_fee":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsa",
         "book_policy":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsad",
         "hotel_secured":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsa",
         "hotel_pets":"Allowed",
         "hotel_other":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsa",
         "room":[  
            {  
               "id":2,
               "roomtype_id":1,
               "room_count":null,
               "max_adult":2,
               "max_children":2,
               "capacity":4,
               "room_number":null,
               "floor":null,
               "number":null,
               "name":null,
               "city_name":"Swains Island",
               "city":48331,
               "price":"500.00",
               "extra_bed_price":null,
               "astatus":"1",
               "status":"0",
               "created_at":"2018-03-16 07:51:17",
               "updated_at":"2018-03-16 07:52:20",
               "deleted_at":null,
               "client_id":2,
               "hotel_id":1
            }
         ]
      }
   },
   {  
      "id":1,
      "hotel_id":1,
      "deal_code":"test",
      "date":"2018-03-23 00:00:00",
      "discount":50,
      "status":1,
      "created_at":"2018-03-16 07:54:26",
      "updated_at":"2018-03-16 07:54:26",
      "deleted_at":null,
      "client_id":2,
      "hotel":{  
         "id":1,
         "name":"Testing Grounds",
         "address":"1-23 City Rd, Southbank VIC 3006, Australia",
         "phone":"9842814927",
         "country":"4",
         "country_name":"American Samoa",
         "state":"166",
         "state_name":"Swains Island",
         "city":"48331",
         "city_name":"Swains Island",
         "area":"test",
         "pincode":"3006",
         "nearest_airport":"test",
         "nearest_railway":"teste",
         "star":"5",
         "check_in":"05:15 AM",
         "check_out":"10:30 AM",
         "lat":"-39",
         "lng":"144.967621",
         "description":"tsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaa",
         "astatus":"1",
         "status":"0",
         "created_at":"2018-03-16 07:28:28",
         "updated_at":"2018-03-16 07:31:22",
         "client_id":2,
         "hotel_age":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsa",
         "hotel_privilege":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsa",
         "departure":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsa",
         "checkout_fee":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsa",
         "book_policy":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsad",
         "hotel_secured":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsa",
         "hotel_pets":"Allowed",
         "hotel_other":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsa",
         "room":[  
            {  
               "id":2,
               "roomtype_id":1,
               "room_count":null,
               "max_adult":2,
               "max_children":2,
               "capacity":4,
               "room_number":null,
               "floor":null,
               "number":null,
               "name":null,
               "city_name":"Swains Island",
               "city":48331,
               "price":"500.00",
               "extra_bed_price":null,
               "astatus":"1",
               "status":"0",
               "created_at":"2018-03-16 07:51:17",
               "updated_at":"2018-03-16 07:52:20",
               "deleted_at":null,
               "client_id":2,
               "hotel_id":1
            }
         ]
      }
   },
   {  
      "id":2,
      "hotel_id":1,
      "deal_code":"test",
      "date":"2018-03-24 00:00:00",
      "discount":50,
      "status":1,
      "created_at":"2018-03-16 07:54:41",
      "updated_at":"2018-03-16 07:54:41",
      "deleted_at":null,
      "client_id":2,
      "hotel":{  
         "id":1,
         "name":"Testing Grounds",
         "address":"1-23 City Rd, Southbank VIC 3006, Australia",
         "phone":"9842814927",
         "country":"4",
         "country_name":"American Samoa",
         "state":"166",
         "state_name":"Swains Island",
         "city":"48331",
         "city_name":"Swains Island",
         "area":"test",
         "pincode":"3006",
         "nearest_airport":"test",
         "nearest_railway":"teste",
         "star":"5",
         "check_in":"05:15 AM",
         "check_out":"10:30 AM",
         "lat":"-39",
         "lng":"144.967621",
         "description":"tsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaa",
         "astatus":"1",
         "status":"0",
         "created_at":"2018-03-16 07:28:28",
         "updated_at":"2018-03-16 07:31:22",
         "client_id":2,
         "hotel_age":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsa",
         "hotel_privilege":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsa",
         "departure":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsa",
         "checkout_fee":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsa",
         "book_policy":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsad",
         "hotel_secured":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsa",
         "hotel_pets":"Allowed",
         "hotel_other":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsa",
         "room":[  
            {  
               "id":2,
               "roomtype_id":1,
               "room_count":null,
               "max_adult":2,
               "max_children":2,
               "capacity":4,
               "room_number":null,
               "floor":null,
               "number":null,
               "name":null,
               "city_name":"Swains Island",
               "city":48331,
               "price":"500.00",
               "extra_bed_price":null,
               "astatus":"1",
               "status":"0",
               "created_at":"2018-03-16 07:51:17",
               "updated_at":"2018-03-16 07:52:20",
               "deleted_at":null,
               "client_id":2,
               "hotel_id":1
            }
         ]
      }
   },
   {  
      "id":12,
      "hotel_id":1,
      "deal_code":"tests",
      "date":"2018-03-22 00:00:00",
      "discount":35,
      "status":1,
      "created_at":"2018-03-21 08:59:22",
      "updated_at":"2018-03-21 08:59:22",
      "deleted_at":null,
      "client_id":2,
      "hotel":{  
         "id":1,
         "name":"Testing Grounds",
         "address":"1-23 City Rd, Southbank VIC 3006, Australia",
         "phone":"9842814927",
         "country":"4",
         "country_name":"American Samoa",
         "state":"166",
         "state_name":"Swains Island",
         "city":"48331",
         "city_name":"Swains Island",
         "area":"test",
         "pincode":"3006",
         "nearest_airport":"test",
         "nearest_railway":"teste",
         "star":"5",
         "check_in":"05:15 AM",
         "check_out":"10:30 AM",
         "lat":"-39",
         "lng":"144.967621",
         "description":"tsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaatsaa",
         "astatus":"1",
         "status":"0",
         "created_at":"2018-03-16 07:28:28",
         "updated_at":"2018-03-16 07:31:22",
         "client_id":2,
         "hotel_age":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsa",
         "hotel_privilege":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsa",
         "departure":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsa",
         "checkout_fee":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsa",
         "book_policy":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsad",
         "hotel_secured":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsa",
         "hotel_pets":"Allowed",
         "hotel_other":"asfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsaasfsadfsa",
         "room":[  
            {  
               "id":2,
               "roomtype_id":1,
               "room_count":null,
               "max_adult":2,
               "max_children":2,
               "capacity":4,
               "room_number":null,
               "floor":null,
               "number":null,
               "name":null,
               "city_name":"Swains Island",
               "city":48331,
               "price":"500.00",
               "extra_bed_price":null,
               "astatus":"1",
               "status":"0",
               "created_at":"2018-03-16 07:51:17",
               "updated_at":"2018-03-16 07:52:20",
               "deleted_at":null,
               "client_id":2,
               "hotel_id":1
            }
         ]
      }
   }
]

игнорировать этот текст игнорировать этот текст игнорировать этот текст игнорировать этот текст игнорировать этот текст игнорировать этот текст игнорировать этот текст игнорировать этот текст игнорировать этот текст игнорировать этот текст игнорировать этот текст


mysql laravel laravel-5.6 laravel-5.5
person arun    schedule 22.03.2018    source источник
comment
В каком формате возвращается коллекция? И я думаю, вам нужна минимальная цена с использованием метода сбора (laravel.com/docs/5.6 /collections#method-min) ... в противном случае, я думаю, вам нужен другой запрос.   -  person Indra    schedule 22.03.2018
comment
Нет, это невозможно. причина этого заключается в генерации ошибки при попытке получить данные в подзапросе, потому что структура запроса неверна, если вы пытаетесь получить данные в подзапросе   -  person Ravindra Bhanderi    schedule 22.03.2018
comment
какое имя переменной вы использовали для определения цены в вашей таблице?   -  person Chirag Patel    schedule 22.03.2018
comment
@ЧирагПател price   -  person arun    schedule 22.03.2018
comment
@arunkumar вы получаете результат с этим запросом? как сделки?   -  person Chirag Patel    schedule 22.03.2018
comment
@ChiragPatel, см. обновленный текст, мой текущий результат обновлен   -  person arun    schedule 22.03.2018
comment
попробуйте использовать orderBy('price','ASC')-›get() https://stackoverflow.com/questions/17553181/laravel-4-how-to-order-by-using-eloquent-orm   -  person Just L    schedule 23.03.2018

Ответы (2)


arrow_upward
0
arrow_downward

Единственный способ, которым я вижу, что это работает, довольно ресурсоемкий

$minPrice = 10000000;
$roomId = 0;
$dealId = 0;

$result->each(function($item, $key) use (&$minPrice, &$roomid, &$dealId){
     if($value->hotel->room->price < $minPrice){
          $minPrice = $value->room->price;
          $roomId = $value->room->id;
          $dealId = $value->id;
     }
});

Я бы рекомендовал изменить запрос. Дайте мне знать, если вам нужна помощь с этим.

Также может ли в отеле быть несколько номеров? Если да, то есть еще один запрос

person Indra    schedule 22.03.2018

arrow_upward
0
arrow_downward

Вы можете использовать агрегированный метод Laravel min в своем запросе,

$query->with('room')->min('price');

Я не уверен, что это сработает или нет, но вы можете попробовать это. Просто вставьте этот код, чтобы попробовать,

$greatDeals = Deal::whereHas('hotel', function ($query) {
                        $query->whereHas('room', function ($query) {
                            $query->Where('astatus', 1)->Where('status', 0);
                        })->Where('astatus', 1)->Where('status', 0);
                    })->with(['hotel' => function ($query) {
                        $query->with('room')->min('price'); //need to get minimum price from room table
                    }])
                    ->where('date', '>=', Carbon::today()->toDateString())
                    ->where('status', 1)
                    ->orderBy('discount', 'desc')
                    ->limit(9)
                    ->get();

Если будет ошибка. Скажи мне, я найду другой запрос.

person Chirag Patel    schedule 22.03.2018
comment
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'price' in 'field list' (SQL: select min(price) as aggregate from hotels`, где hotels.id в (1, 6))` - person arun; 22.03.2018
comment
-›with('отели.номера') - person Indra; 22.03.2018
comment
какая связь между Deal и Hotel? это "отель" или "гостиницы"? - person Chirag Patel; 22.03.2018