CODEWITHSUNDEEP
mysqlsqllaravel
stoneshaq
stoneshaq

Reputation: 336

Make sql query simpler shorter ( Laravel )

is there any way to make these queries shorter or simpler? or maybe get the result in 1 query rather than 3.?? any advise appreciated

        $room_single = \DB::table('book_room')
        ->leftJoin('book_tour', 'book_room.bookingID', '=', 'book_tour.bookingID')
        ->where('tourdateID', '=', $id)
        ->where('roomtype','=',1)
        ->where('book_room.status','=',1)
        ->count();

    $room_double = \DB::table('book_room')
        ->leftJoin('book_tour', 'book_room.bookingID', '=', 'book_tour.bookingID')
        ->where('tourdateID', '=', $id)
        ->where('roomtype','=',2)
        ->where('book_room.status','=',1)
        ->count();

    $room_triple = \DB::table('book_room')
        ->leftJoin('book_tour', 'book_room.bookingID', '=', 'book_tour.bookingID')
        ->where('tourdateID', '=', $id)
        ->where('roomtype','=',3)
        ->where('book_room.status','=',1)
        ->count();
    $total= $room_single+($room_double*2)+($room_triple*3) ;

Upvotes: 1

Views: 150

Answers (3)

Rwd
Rwd

Reputation: 35220

In this case, since the roomtype column relates directly to how you would calculate the total you could just use sum instead of count:

$total = \DB::table('book_room')
    ->leftJoin('book_tour', 'book_room.bookingID', '=', 'book_tour.bookingID')
    ->where('tourdateID', '=', $id)
    ->where('book_room.status', '=', 1)
    ->sum('roomtype');

UPDATE

If you still need the count for each roomtype then you could do something like:

$query = \DB::table('book_room')
    ->leftJoin('book_tour', 'book_room.bookingID', '=', 'book_tour.bookingID')
    ->where('tourdateID', '=', $id)
    ->where('book_room.status', '=', 1);

$room_single = $query->newQuery()->where('roomtype', 1)->count();
$room_double = $query->newQuery()->where('roomtype', 2)->count();
$room_triple = $query->newQuery()->where('roomtype', 3)->count();
$total = $room_single + ($room_double * 2) + ($room_triple * 3);

Using newQuery means that you can reuse constraints without adding to the original.

Or if you don't want to make multiple queries and you want php to handle the counts

$rooms = \DB::table('book_room')
    ->select('roomtype')
    ->selectRaw('count(*) as room_count')
    ->leftJoin('book_tour', 'book_room.bookingID', '=', 'book_tour.bookingID')
    ->where('tourdateID', '=', $id)
    ->where('book_room.status', '=', 1)
    ->whereBetween('roomtype', [1, 3])//This is only needed if you have other room types
    ->groupBy('roomtype')
    ->orderBy('roomtype')
    ->get('roomtype');

list($room_single, $room_double, $room_triple) = $rooms->pluck('room_count')->toArray();
$total = $rooms->sum(function ($item) {
    return $item->room_count * $item->roomtype;
});

Hope this helps!

Upvotes: 1

Sagar Gautam
Sagar Gautam

Reputation: 9389

You can do it in more simple way.

one is, 

$query = \DB::table('book_room')
         ->leftJoin('book_tour', 'book_room.bookingID', '=', 'book_tour.bookingID')
         ->where('tourdateID', '=', $id)
         ->where('book_room.status','=',1);

$room_single = $query->where('roomtype','=',1)->count();
$room_double = $query->where('roomtype','=',2)->count();
$room_triple = $query->where('roomtype','=',3)->count();

And then add these as you want. This is just reducing your lines of code.

Another and better way is following.

$all_type = \DB::table('book_room')
            ->leftJoin('book_tour', 'book_room.bookingID', '=', 'book_tour.bookingID')
            ->where('tourdateID', '=', $id)
            ->where('book_room.status','=',1)
            ->select('roomtype',\DB::raw('COUNT(bookingID) as count'))
            ->groupBy('roomtype')
            ->get();

With this query you will get three count for each room type. Hope you understand.

Upvotes: 0

S.M Talha
S.M Talha

Reputation: 59

I dont have enough contributions that is why am posting as answer. try 

GROUP BY roomtype

Then you will not need to change your roomtype.

Upvotes: 0

Related Questions