Please How can I convert sql query into laravel query

Question

I need to convert this sql query into laravel query builder or how can I run into laravel as it is.

SELECT rt.id, rt.type, rt.total_no_of_rooms,IFNULL(SUM(r.no_of_rooms), 0) as "reserved rooms", rt.total_no_of_rooms - IFNULL(SUM(r.no_of_rooms),0) as "rooms available" FROM Rooms rt LEFT OUTER JOIN Reservations r ON r.roomtype_id = rt.id  AND  '2020-04-04' >=  r.check_in AND  '2020-04-02' <=  r.check_out GROUP BY rt.id, rt.type, rt.total_no_of_rooms

Mondini · Accepted Answer · 2020-04-02 17:06:17Z

1

how can I run into laravel as it is.

There is this approach:

$query= DB::select( DB::raw("SELECT rt.id, rt.type, rt.total_no_of_rooms,IFNULL(SUM(r.no_of_rooms), 0) as 'reserved rooms', rt.total_no_of_rooms - IFNULL(SUM(r.no_of_rooms),0) as 'rooms available' FROM Rooms rt LEFT OUTER JOIN Reservations r ON r.roomtype_id = rt.id  AND  '2020-04-04' >=  r.check_in AND  '2020-04-02' <=  r.check_out GROUP BY rt.id, rt.type, rt.total_no_of_rooms"));

It will simply run the query. You can foreach the $query now.

answered Apr 2, 2020 at 17:06

Mondini

6106 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gijs Beijer Over a year ago

Maybe a nice addition: If you need Eloquent models from this, you can use the 'hydrate' method on your model, pass it in the result of the query and you should get a collection with models.

Collectives™ on Stack Overflow

Please How can I convert sql query into laravel query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related