(rails) postgres error (column "posts.id" must appear in the GROUP BY clause)

Question

Welcome.....

I want to get the most user post on my rails app

@section.posts.select("posts.*, count(posts.user_id) as user_posts_count").group('posts.user_id')
.order("user_posts_count DESC").limit(10)

But I have issue with postgreSQL while my code work without any issue on sqlit3 and mysql2

PG::GroupingError: ERROR: column "posts.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT posts.*, count(posts.user_id) as user_posts_count FR...

I need to use postgreSQL because it is the best for heroku but i don't know why I get this error on PostgreSQL

I hope if anyone help me >>>

That error is telling you exactly what is wrong. Every column in your select() must also be in your group(). Excluding of course calculations like count(), sum(), avg(), etc. You probably only want to select something like user_id. @section.posts.select("user_id, count(user_id) as user_posts_count").group(:user_id).order("user_posts_count DESC").limit(10). — Dan
– Dan, Commented Nov 21, 2016 at 20:47
thank you brother its work now ,I think I still need more experience thank you again , but why is work with mysql and sqlite while doesn't work on postgreSQL — Mohammad Khojaly
– Mohammad Khojaly, Commented Nov 22, 2016 at 7:45

maxgomes · Accepted Answer · 2016-11-21 15:45:42Z

1

remove the id from the group, you should add all others fields in group by, using .* is a bad pratice, pls don't use .* in select that have for example, a count()

answered Nov 21, 2016 at 15:45

maxgomes

3293 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

(rails) postgres error (column "posts.id" must appear in the GROUP BY clause)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related