Laravel 5.4 Raw无法按预期工作，但在mySQL中工作 [英] Laravel 5.4 Raw is not working as expected but working in mySQL

问题描述

 1. MySQL :      

        SELECT a.*,COUNT(DISTINCT  b.id)  AS IndianTruck,COUNT(DISTINCT  d.id) AS BdTruck

               FROM manifests a  
                JOIN truck_entry_regs b ON a.id = b.manf_id

                  LEFT JOIN truck_deliverys d ON a.id = d.manf_id

               WHERE a.manifest='550/7'
               GROUP BY a.id


2.Laravel 5.4 
   $results = DB::select('SELECT a.*, COUNT(DISTINCT  b.id)  AS IndianTruck,COUNT(DISTINCT  d.id) AS BdTruck

   FROM manifests a
    JOIN truck_entry_regs b ON a.id = b.manf_id

      LEFT JOIN truck_deliverys d ON a.id = d.manf_id

   WHERE a.manifest=?
   GROUP BY a.id', ['550/7'])

我有相同的查询. 第一个查询在MySQL(Sqlyog)上运行良好.但是，如果我在laravel 5.4应用程序上运行(第二个查询)，它会说:

I have the same query. The first query run well on MySQL(Sqlyog). But if i run (second query ) it on my laravel 5.4 app it says:

   QueryException in Connection.php line 647:
SQLSTATE[42000]: Syntax error or access violation: 1055 'dbblpa.a.port_id' isn't in GROUP BY (SQL: SELECT a.*, COUNT(DISTINCT b.id) AS IndianTruck,COUNT(DISTINCT d.id) AS BdTruck

FROM manifests a
JOIN truck_entry_regs b ON a.id = b.manf_id

LEFT JOIN truck_deliverys d ON a.id = d.manf_id

WHERE a.manifest=550/7
GROUP BY a.id)

在连接后，如何从一个表中返回所有数据，并从其他表中返回cont? 如果我在选择中添加a.id，a.name(例如选择a.id，a.name，COUNT(DISTINCT b.id))，并在GROUP BY中添加a.id和a.name，那么它将起作用. 我是否应该从带计数的表中返回所有列的问题，我应该在group by中添加所有列吗?我知道这很奇怪！ 然后如何在laravel 5.4查询生成器中获得预期结果?

How can i return all data from a table with cont from other tables after joining? If i add a.id,a.name in the select (like select a.id,a.name, COUNT(DISTINCT b.id)) and add a.id and a.name in GROUP BY , then it works. The question if i return all column from a table with count should i add all column in group by? I know it is odd! Then how can i get my expected result in laravel 5.4 query builder?

推荐答案

Laravel 5.3和5.4默认情况下对MySQL使用严格模式.这意味着ONLY_FULL_GROUP_BY SQL模式也已启用.但是，如果您的MySQL版本至少为5.7.5，则可以按表的主键进行分组，并在SELECT子句中使用该表中的所有列，因为它们在功能上取决于PK.

Laravel 5.3 and 5.4 use strict mode for mysql per default. That means that ONLY_FULL_GROUP_BY SQL mode is also enabled. But if your MySQL version is at least 5.7.5 you can group by a primary key of a table and use all columns from that table in the SELECT clause because they are functionally dependent on the PK.

MySQL 5.7.5及更高版本实现对功能依赖性的检测.如果 启用了ONLY_FULL_GROUP_BY SQL模式(默认情况下)， MySQL拒绝选择列表，具有HAVING条件或 ORDER BY列表引用未在其中命名的非聚合列 GROUP BY子句在功能上也不依赖于它们. (前 5.7.5，MySQL无法检测到功能依赖性，并且默认情况下未启用ONLY_FULL_GROUP_BY.

MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default.

( GROUP BY的MySQL处理)

您的选择是:

将MySQL至少升级到5.7.5

Upgrade MySQL to at least 5.7.5

或在laravels db config(config/database.php)中禁用严格模式

Or disable strict mode in laravels db config (config/database.php)

// ..
'connections' => [
    // ..
    'mysql' => [
        // ..
        'strict' => false,
        // ..
    ],
    // ..
]

更新

MariaDB(和xampp)用户的坏消息:MariaDB似乎不支持检测功能依赖性"(尚未).我所能找到的就是这张门票.

Bad news for MariaDB (and xampp) user: MariaDB seems not to support the "detection of functional dependence" (yet). All i could find is this ticket.

这篇关于Laravel 5.4 Raw无法按预期工作，但在mySQL中工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！