时间戳

首页 > 解决方案 > Laravel 中的 MySQL 查询

mysql - Laravel 中的 MySQL 查询

问题描述

我有两张桌子：restaurants和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 |
    +----+------------+---------------+----------+

表 restaurant_orders 存储预订和订购的项目数据。我试图显示（在图表中）特定项目的订购次数。所以乘以数量。

控制器

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));
}

我得到的输出：

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

我不希望重复相同的项目，如上所示的“蛋糕（切片）”。我希望它像：

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

标签： mysqllaravel

解决方案

编辑：

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));
}

将查询更改为sum(1 * quantity) as total并(int) $value->total解决了问题。这是由于谷歌图表引起的。

推荐阅读