Laravel, get the minimum price within subquery

Question

Having the below working code:

$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();

In the above query i need to get minimum price from room table is it possible with existing query?

Update: my current result:

[  
   {  
      "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
            }
         ]
      }
   }
]

ignore this text ignore this text ignore this text ignore this text ignore this text ignore this text ignore this text ignore this text ignore this ignore this text ignore this text

What is the format of the collection returned? And I guess you want the min price using a collection method (https://laravel.com/docs/5.6/collections#method-min) ... otherwise I think you need another query. — Indra, Mar 22 '18 at 11:31
No , That not Possible . reason behind it is generate error in you try to get data in subquery , becuase the structure of query is not proper if you try to get data in subquery — Ravindra Bhanderi, Mar 22 '18 at 11:32
what variable name you have used to define price in your table? — Chirag Patel, Mar 22 '18 at 11:35
@arunkumar are you getting a result with this query? like deals? — Chirag Patel, Mar 22 '18 at 11:36
@ChiragPatel, see the updated text, my current result is updated — arun, Mar 22 '18 at 11:41
try use orderBy('price','ASC')->get() [https://stackoverflow.com/questions/17553181/laravel-4-how-to-order-by-using-eloquent-orm](https://stackoverflow.com/questions/17553181/laravel-4-how-to-order-by-using-eloquent-orm) — Just L, Mar 23 '18 at 05:07

score 0 · Answer 1 · answered Mar 22 '18 at 11:47

The only way I see it working is pretty resource consuming

$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;
     }
});

I would recommend changing the query. Let me know if you need help with that.

Also can a hotel have multiple rooms? If yes there's another query there

score 0 · Answer 2 · answered Mar 22 '18 at 11:55

You can use Laravel aggregate method min in your query,

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

I am not sure it will work or not but you can try this. Just paste this code to try,

$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();

If there will be error. Tell me I will find another query.

`SQLSTATE[42S22]: Column not found: 1054 Unknown column 'price' in 'field list' (SQL: select min(`price`) as aggregate from `hotels` where `hotels`.`id` in (1, 6))` — arun, Mar 22 '18 at 12:01
what is the relation between Deal and Hotel? it's 'hotel' or 'hotels'? — Chirag Patel, Mar 22 '18 at 12:08

Laravel, get the minimum price within subquery

2 Answers2