时间戳

首页 > 解决方案 > Lumen API 选择按聚合分组的数据

php - Lumen API 选择按聚合分组的数据

问题描述

我目前正在为我的后端 API 使用 Lumen 5.7。我需要使用左连接从多个表中选择一些字段。我有以下查询（普通 mysql 查询）

SELECT issue_id,issue_receipt_id, date_format(issue_datetime, '%d-%m-%Y') as issue_datetime,
issue_item,sum(issue_wt) as issue_wt, round(avg(issue_pure),3) as issue_pure,
sum(issue_fine) as issue_fine, sum(issue_cash) as issue_cash,
sum(receipt_wt) as receipt_wt, sum(receipt_fine) as receipt_fine,
pure_name as issue_item_name 
FROM item_issues 
LEFT JOIN receipt_items ON receipt_id = issue_receipt_id 
LEFT JOIN purities ON pure_id = issue_item
group by issue_receipt_id;

我已经尝试过流明，

$receipts = DB::table('item_issues')            
            ->leftJoin('receipt_items', 'receipt_items.receipt_id', '=', 'item_issues.issue_receipt_id')
            ->leftJoin('purities', 'purities.pure_id', '=', 'item_issues.issue_item')
            ->select('issue_id', 'issue_receipt_id', DB::raw("date_format(issue_datetime, '%d-%m-%Y') as issue_datetime"), 'issue_item', 'pure_name', DB::raw('sum(issue_wt) as issue_wt'), 
            DB::raw('round(avg(issue_pure),3) as issue_pure'),
            DB::raw('sum(issue_fine) as issue_fine'),
            DB::raw('sum(issue_cash) as issue_cash'),
            DB::raw('sum(receipt_wt) as receipt_wt'),
            DB::raw('sum(receipt_fine) as receipt_fine'))
            ->groupBy('item_issues.issue_receipt_id', 'item_issues.issue_item')->get();

但它会产生语法错误或访问冲突等错误：1055 Expression #1 of SELECT list is not in GROUP BY 子句并包含非聚合列“item_issues.issue_id”，它在功能上不依赖于 GROUP BY 子句中的列；但是我必须选择上述字段，如何做到这一点没有问题。而且我试图禁用严格模式，但我不知道在 .env 或其他文件中在哪里禁用此选项。我在流明中找不到 database.config。任何人都可以在这方面提供帮助。

标签： phpmysqllaravel-5lumen

解决方案

默认情况下'config/database.php'，Lumen 应用程序中没有命名任何文件夹。但可以随意将此配置文件添加到您的流明应用程序中，就像在 laravel 中一样。

并将以下代码粘贴到您的 database.php 文件中。

return [
    'default' => 'mysql',
    'connections' => [
        'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST'),
            'database' => env('DB_DATABASE'),
            'username' => env('DB_USERNAME'),
            'password' => env('DB_PASSWORD'),
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'strict'    => false
        ]
    ]
]

这里重要的是'strict' => false模式。

推荐阅读