Laravel : Eloquent HAVING returns invalid data set

Question

I am trying to retrieve the hotel list, In which rooms available for requested dates.

The room availability table looks like below.

room_availability

id |    hotel   |     room      | start_date |   end_date  | count | 
--------------------------------------------------------------------
1  |     301    |      121      | 2019-04-01 |  2019-04-01 |   10  |
2  |     301    |      121      | 2019-04-02 |  2019-04-02 |   7   | 
3  |     301    |      121      | 2019-04-03 |  2019-04-03 |   4   | 
4  |     301    |      120      | 2019-04-02 |  2019-04-02 |   5   | 
5  |     301    |      120      | 2019-04-03 |  2019-04-03 |   6   |

And the search model code as,


$no_of_days = (Carbon::parse($data['start_date'])
              ->diffInDays(Carbon::parse($data['end_date'])));
$no_of_days += 1;

$hotelList = $this
        ->select('id','hotel_code','room', \DB::Raw('count(hotel_code) as total_days'))
        ->with(['hotel:id,name,logo,location,code'])
        ->where('start_date','>=',$data['start_date'])
        ->Where('start_date','<=',$data['end_date'])
        ->groupBy('hotel_code')
        ->having('total_days',$no_of_days)->get();

For the request (2019-04-02 - 2019-04-03) without having condition,

->having('total_days',$no_of_days)

returns hotel 301 but when It is added empty set returned.

What I have to add or remove?

```->having(\DB::Raw('count(hotel_code)'),$no_of_days)``` same response @JoaoGRRR — OutForCode, Mar 14 '19 at 14:23

score 0 · Answer 1 · answered Mar 14 '19 at 14:22

0

try the below query,

$no_of_days = (Carbon::parse($data['start_date'])
          ->diffInDays(Carbon::parse($data['end_date'])));
$no_of_days += 1;

$hotelList = $this
    ->select('id','hotel_code','room', \DB::Raw('count(hotel_code) as total_days'))
    ->with(['hotel:id,name,logo,location,code'])
    ->where('start_date','>=',$data['start_date'])
    ->Where('start_date','<=',$data['end_date'])
    ->groupBy('hotel_code')
    ->havingRaw('count(hotel_code) ='. $no_of_days)->get();

answered Mar 14 '19 at 14:22

Sandeep Sudhakaran

1,072
2
9
22

returns empty set @Sandeep. – OutForCode Mar 14 '19 at 14:24
could you please confirm you have data which satisfy the condition `count(hotel_code) ='. $no_of_days` – Sandeep Sudhakaran Mar 14 '19 at 14:26
1

yes @sandeep, By removed having condition, returns total_days and $no_of_days as 2. for ```(2019-04-02 - 2019-04-03)``` – OutForCode Mar 14 '19 at 14:33
1

cool. can you please return the query and run it on your sql browser. query looking fine. might be chance to miss something else. use /DB::enableQueryLog() above the querybuilder and dd(/DB::getQueryLog()) below the query builder;. you will get the query. – Sandeep Sudhakaran Mar 14 '19 at 14:39
Tested in @Sandeep, using this ```WHERE `start_date` >= '2019-04-01'``` one alone, removed all the other condition. I surprised getting a single row of result as below ```1 | 301 | 121 | 5``` – OutForCode Mar 15 '19 at 09:20

score 0 · Answer 2 · answered Mar 16 '19 at 09:49

Following query returns expected data to me,

$hotelList = $this
        ->select('id','hotel_code','room', \DB::Raw('count(hotel_code) as total_days'))
        ->with(['hotel:id,name,logo,location,code'])
        ->where('start_date','>=',$data['start_date'])
        ->Where('start_date','<=',$data['end_date'])
        ->groupBy('hotel_code','room')
        ->having('total_days',$no_of_days)->get();

The set should be group by both hotel_code and room code, hotel data may repeat in this case which can be filtered on controller.

Laravel : Eloquent HAVING returns invalid data set

2 Answers2