CODEWITHSUNDEEP
mysqllaravel
Dynamite
Dynamite

Reputation: 98

MySQL query in Laravel

I have two tables: restaurants and restaurant_order.

restaurants: 

    +----+-----------------+-------+
    | id |      item       | price |
    +----+-----------------+-------+
    | 1  | Hot & Sour Soup | 2.5   |
    | 2  | Manchurian Soup | 2.5   |
    | 3  | Hummus          | 1.8   |
    | 4  | Tabouleh        | 1.5   |
    | .  | .               | .     |
    | .  | .               | .     |
    | .  | .               | .     |
    | 8  | Cake (slice)    | 2     |
    +----+-----------------+-------+

restaurant_orders: 

    +----+------------+---------------+----------+
    | id | booking_id | restaurant_id | quantity |
    +----+------------+---------------+----------+
    |  1 |         13 |             3 |        2 |
    |  2 |         15 |             9 |        1 |
    |  3 |         15 |             1 |        1 |
    |  4 |         13 |             8 |        2 |
    |  5 |         15 |             8 |        3 |
    |  6 |         15 |            11 |        1 |
    |  7 |         13 |            10 |        2 |
    +----+------------+---------------+----------+

The table restaurant_orders stores the data of booking and item ordered. I am trying to display (in graph) the number of times specific items have been ordered.So multiplying with quantity.

Controller

public function restaurant(){
    $data = DB::table('restaurant_orders')
    ->join('restaurants', 'restaurant_id', '=', 'restaurants.id')
    ->select(
        DB::raw('item as item'),
        DB::raw('count(*) * quantity as total'))
    ->groupBy('item','quantity')
    ->get();

    $array[]=['Item', 'Total'];

    foreach($data as $key=>$value)
    {
     $array[++$key] = [$value->item, $value->total];
    }

    return view('executive.restaurant')->with('item',json_encode($array));
}

The output I am getting: 

+-------------------+-------+
|       Item        | Total |
+-------------------+-------+
| Cake (slice)      |     2 |
| Cake (slice)      |     3 |
| Fried Rice        |     1 |
| Hot & Sour Soup   |     1 |
| Hummus            |     2 |
| Soft Drinks       |     2 |
| Vanilla milkshake |     1 |
+-------------------+-------+

I don't want the same item to be repeated as seen above for 'cake (slice)'. I want it to be like:

+-------------------+-------+
|       Item        | Total |
+-------------------+-------+
| Cake (slice)      |     5 |
| Fried Rice        |     1 |
| Hot & Sour Soup   |     1 |
| Hummus            |     2 |
| Soft Drinks       |     2 |
| Vanilla milkshake |     1 |
+-------------------+-------+

Upvotes: 0

Views: 65

Answers (1)

Odinn
Odinn

Reputation: 808

EDIT:

public function restaurant() {
    $data = DB::table('restaurant_orders')
    ->join('restaurants', 'restaurant_id', '=', 'restaurants.id')
    ->select(
        DB::raw('item as item'),
        DB::raw('sum(1 * quantity) as total')) 
    ->groupBy('item')
    ->get();

    $array[]=['Item', 'Total'];

    foreach($data as $key=>$value) {
        $array[++$key] = [$value->item, (int) $value->total];
    }

    return view('executive.restaurant')->with('item', json_encode($array));
}

Changing the query to sum(1 * quantity) as total and (int) $value->total solved the issue. This was arising due to Google Charts.

Upvotes: 1

Related Questions