1

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) ;
stoneshaq
  • 316
  • 3
  • 18
  • What are you trying to do ? Can you explain please. If you just want to count booked room, you can do it in some other way. – Sagar Gautam Jul 08 '17 at 10:35
  • Hi Sagar.. i m trying to count the booked rooms.. i need the number of single, double and triple rooms.. total is the total number of passenger.. it is all working with no problem. i was just wondering if there is a more elegant way than what i did – stoneshaq Jul 08 '17 at 10:52
  • 1
    i tried that. i got the logic. for some reason it only counts the single room. it doesnt count the double or triple rooms. i will figure it out though.. i picked the first way. – stoneshaq Jul 08 '17 at 11:16
  • okay, I think it should work but I don't know why it's getting count for only single room – Sagar Gautam Jul 08 '17 at 11:29
  • am i to use the second way with foreach? – stoneshaq Jul 08 '17 at 11:33
  • If you have no problem regarding performance and any other things, then do not change your code. – Sagar Gautam Jul 08 '17 at 11:36

3 Answers3

1

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!

Rwd
  • 34,180
  • 6
  • 64
  • 78
  • as well as $total , i also need $room_single, $room_double, room_triple.. is there an easy way with your query? – stoneshaq Jul 15 '17 at 02:29
  • @stoneshak I've updated my answer with some more possibilities. – Rwd Jul 15 '17 at 10:26
  • Thanks so much. Its great. nice and simple. do you mind looking at this question as well? https://stackoverflow.com/questions/44808189/how-to-get-the-record-if-count-is-zero-in-laravel – stoneshaq Jul 15 '17 at 13:58
  • is there any way to make this Laravel 5.1 compatible ? thanks mate – stoneshaq Jul 16 '17 at 10:07
  • @stoneshak Is it just the `$rooms->sum(...)` bit that is failing. – Rwd Jul 16 '17 at 10:19
0

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.

S.M Talha
  • 59
  • 1
  • 10
0

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.

Sagar Gautam
  • 9,049
  • 6
  • 53
  • 84