MySQL query in Laravel

Question

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 |
+-------------------+-------+

Dynamite · Accepted Answer · 2020-04-25 15:34:56Z

1

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.

edited Apr 25, 2020 at 15:34

Dynamite

981 silver badge9 bronze badges

answered Apr 22, 2020 at 18:22

Odinn

8085 silver badges23 bronze badges

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

9 Comments

Dynamite Over a year ago

It is giving an error. SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') as total from restaurant_orders inner join restaurants on restaurant_id' at line 1 (SQL: select item as item, sum() as total from restaurant_orders inner join restaurants on restaurant_id = restaurants.id group by item) after I used your query.

Odinn Over a year ago

so for some odd reason it's not including the * try changing sum(*) to sum(1) see if that helps... if not I suggest looking into some tutorials on how to do sum selects in the ORM Laravel is using...

Dynamite Over a year ago

I tried using your logic to solve the issue, which I have already tried before as well. I don't want it to be sum() for items, I want that every item shows how many times they were ordered. Your query is giving me how many times they were ordered without including the 'quantity' column to be multiplied. Still thank you for trying to help me, I appreciate it.

Odinn Over a year ago

so why not just multiply the sum here with the sum of quantity?

Dynamite Over a year ago

If you mean by using the sum of items and then multiplying with the sum of quantity then it isn't working. Maybe you can send me a query of what you are trying to explain to me so I understand it better?

|

Collectives™ on Stack Overflow

MySQL query in Laravel

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related