laravel 如何解决mysql查询中的“不在GROUP BY中”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33591606/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to resolve "isn't in GROUP BY" error in mysql query
提问by horse
I have two models: postsand likingswhich have one-to-many relationship (so, one post has many likes). Likings model has also an isActivefield which shows liking is active or passive.
我有两个模型：posts并且likings具有一对多关系（因此，一个帖子有很多赞）。喜好模型还有一个isActive字段，显示喜好是主动的还是被动的。
I want to get (sort) top 5 posts which had received maximum "active" likes (only likes whose isActive field is true would be considered).
我想获得（排序）获得最多“活跃”喜欢的前 5 个帖子（仅考虑 isActive 字段为真的喜欢）。
This is the query:
这是查询：
select posts.*, count(likings.id) as likes_count from 'posts'
left join 'likings' on 'likings'.'post_id' = 'posts'.'id' and 'likings'.'isActive' = 1
group by 'posts'.'id'
order by 'likes_count' desc
limit 5
which is resulted from this laravel query:
这是由此laravel查询的结果：
Post::selectRaw('posts.*, count(likes.id) as likes_count')
->leftJoin('likes', function ($join) {
$join->on('likes.post_id', '=', 'posts.id')
->where('likes.is_active', '=', 1);
})
->groupBy('posts.id')
->orderBy('likes_count', 'desc')
->take(5)
->get();
And this is the error:
这是错误：
SQLSTATE[42000]: Syntax error or access violation: 1055
'database.posts.user_id' isn't in GROUP BY
Here, posts.user_idis also a field of poststable and shows the owner of the post.
这里，posts.user_id也是一个posts表字段，显示帖子的所有者。
How can I resolve this error? It seems it isn't logical to change mysql config (probably deleting ONLY_FULL_GROUP_BYmode), but minimum change in the query.
我该如何解决这个错误？更改 mysql 配置（可能是删除ONLY_FULL_GROUP_BY模式）似乎不合逻辑，但查询中的更改最少。
回答by vp_arth
Each non-aggregated field should be grouped.
每个非聚合字段都应分组。
It is standard behavior, which in mysql, depends on ONLY_FULL_GROUP_BY mode.
This mode is default enabled in >= 5.7.
这是标准行为，在 mysql 中，取决于 ONLY_FULL_GROUP_BY 模式。
此模式在 >= 中默认启用5.7。
select posts.id, post.user_id, count(likings.id) as likes_count
from posts
left join likings on likings.post_id = posts.id and likings.isActive= 1
group by posts.id, posts.user_id
order by likes_count desc
limit 5
Or, you can aggregateit:
或者，你可以aggregate：
select posts.id, MIN(post.user_id), count(likings.id) as likes_count
from posts
left join likings on likings.post_id = posts.id and likings.isActive= 1
group by posts.id
order by likes_count desc
limit 5
Other way - change sql_mode:
其他方式 - 改变sql_mode：
SET SESSION sql_mode = '';
Also, you can divide it to 2 query:
此外，您可以将其分为 2 个查询：
- Get aggregate and post ids
- Get posts data with fetched ids (with
IN clause)
- 获取聚合和发布 ID
- 使用获取的 id 获取帖子数据（带有
IN clause）
回答by Raugaral
In 5.7 the sqlmode is set by default to:
在 5.7 中，sqlmode 默认设置为：
ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
ONLY_FULL_GROUP_BY、NO_AUTO_CREATE_USER、STRICT_TRANS_TABLES、NO_ENGINE_SUBSTITUTION
To remove the clause ONLY_FULL_GROUP_BY you can do this:
要删除子句 ONLY_FULL_GROUP_BY 你可以这样做：
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
