如何解决“不在GROUP BY中"的问题； mysql查询中的错误 [英] How to resolve "isn't in GROUP BY" error in mysql query

问题描述

我有两个模型:posts和likings，它们具有一对多的关系(因此，一个帖子有很多喜欢).喜好模型还具有一个isActive字段，该字段显示喜好是主动还是被动.

I have two models: posts and likings which have one-to-many relationship (so, one post has many likes). Likings model has also an isActive field which shows liking is active or passive.

我想获得(排序)收到最多活跃"赞的5个帖子(仅考虑isActive字段为true的赞).

I want to get (sort) top 5 posts which had received maximum "active" likes (only likes whose isActive field is true would be considered).

这是查询:

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

这是该laravel查询的结果:

which is resulted from this laravel query:

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

这是错误:

SQLSTATE[42000]: Syntax error or access violation: 1055
'database.posts.user_id' isn't in GROUP BY

在这里，posts.user_id也是posts表的字段，并显示帖子的所有者.

Here, posts.user_id is also a field of posts table and shows the owner of the post.

如何解决此错误?更改mysql配置(可能删除ONLY_FULL_GROUP_BY模式)似乎不合逻辑，但查询中的更改最少.

How can I resolve this error? It seems it isn't logical to change mysql config (probably deleting ONLY_FULL_GROUP_BY mode), but minimum change in the query.

推荐答案

每个未聚合的字段都应进行分组.

Each non-aggregated field should be grouped.

这是标准行为，在mysql中取决于ONLY_FULL_GROUP_BY模式.
默认情况下，在> = 5.7中启用了此模式.

It is standard behavior, which in mysql, depends on ONLY_FULL_GROUP_BY mode.
This mode is default enabled in >= 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

或者，您可以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

其他方式-更改sql_mode:

SET SESSION sql_mode = '';

此外，您可以将其分为2个查询:

Also, you can divide it to 2 query:

1. 获取汇总并发布ID
2. 使用获取的ID(使用IN clause)获取帖子数据

1. Get aggregate and post ids
2. Get posts data with fetched ids (with IN clause)

这篇关于如何解决“不在GROUP BY中"的问题； mysql查询中的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！