Postgres: Error using GROUP BY and ORDER (on heroku)

Question

I am trying to solve my heroku problem which it seems to have problem of

We're sorry, but something went wrong. We've been notified about this issue and we'll take a look at it shortly.

Is there any mistake I have and how to overcome it? How can I interpret these Heroku logs?

ActionView::Template::Error (PGError: ERROR:  column "microposts.created_at" must appear     in the GROUP BY clause or be used in an aggregate function
2011-11-14T17:33:07+00:00 app[web.1]: : SELECT category FROM "microposts" GROUP BY category ORDER BY microposts.created_at DESC):
2011-11-14T17:33:07+00:00 app[web.1]:     2: <% @categories= Micropost.select("category").group("category")%>
2011-11-14T17:33:07+00:00 app[web.1]:     3: <% unless @categories.nil? %>
2011-11-14T17:33:07+00:00 app[web.1]:     4: 
2011-11-14T17:33:07+00:00 app[web.1]:     5: <ul><% @categories.each do |category| %>
2011-11-14T17:33:07+00:00 app[web.1]:     6: <li><%= link_to category.category, :controller =>"microposts", :category => category.category, :method => 'category_list' %></li>
2011-11-14T17:33:07+00:00 app[web.1]:     7: <% end %>
2011-11-14T17:33:07+00:00 app[web.1]:     8: </ul>

micropost model (New added)

 class Micropost < ActiveRecord::Base
belongs_to :users
default_scope :order => 'microposts.created_at DESC'


attr_accessible :title,:content,:category

validates :user_id, :presence => true
validates :title,    :presence => true,
                     :length => {:maximum =>500}                            
validates :content,  :presence => true,
                     :length => {:maximum =>3000}                           
validates :category, :presence => true
end

I suggest you change the title to "Postgres: Error using GROUP BY and ORDER (on heroku)", which would be much more informative. — Mark Thomas
– Mark Thomas, Commented Nov 14, 2011 at 17:53

mu is too short · Accepted Answer · 2011-11-15 18:16:02Z

4

Your immediate problem is that you're producing invalid SQL for PostgreSQL:

SELECT category FROM "microposts" GROUP BY category ORDER BY microposts.created_at DESC

Your ORDER BY doesn't match the rest of your query. You can't use a column in a grouped query unless that column is also grouped or if the column appears in an aggregate function, that's what the error message means. The reason is that PostgreSQL won't know which row's created_at to use when a group of rows are combined by the GROUP BY clause; some databases will just silently pick a row on their own, PostgreSQL prefers to be strict and wants you to remove the ambiguity yourself.

Try specifying the order yourself:

@categories = Micropost.select("category").group("category").order("category")

Another option is to use DISTINCT instead of GROUP BY to avoid duplicates:

@categories = Micropost.select('DISTINCT(category)')

BTW, you really shouldn't be doing that sort thing in a view, you might want to move that to your controller.

Your real problem is that you're developing on top of one database while deploying on another. I'd recommend that you switch your development environment to PostgreSQL 8.3 (if you're deploying to a Heroku shared database) or PostgreSQL 9.0 (if you're deploying to a dedicated database).

edited Nov 15, 2011 at 18:16

answered Nov 14, 2011 at 17:54

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

5 Comments

shoujo_sm Over a year ago

I get same error, is it any other way to display the post in descending order and also displaying the microposts with the same category among all those microposts of different categories? Please help me. I am actually new into this. Thank you for all your help. I am grateful. my new logs problems : pastie.org/2867385

mu is too short Over a year ago

@Amy: Do you have any idea where the ORDER BY is coming from?

mu is too short Over a year ago

@Amy: I added another option that might work for you: Micropost.select('DISTINCT(category)')

shoujo_sm Over a year ago

Yes, my ORDER_BY is implement in the micropost model to enable me to retrieve all the post in descending order. I get it from some tutorial book.

mu is too short Over a year ago

@Amy: You should be able to add a method inside Micropost that doesn't use your ORDER BY. Or don't make it a default and order things explicitly when you need to.

Community · Accepted Answer · 2017-05-23 11:56:53Z

Have you been developing your app on MySQL? Heroku doesn't include MySQL. It uses postgreSQL. Look at this question here - PostgreSQL GROUP BY different from MySQL?

I quote -

MySQL's totally non standards compliant GROUP BY can be emulated by Postgres' DISTINCT ON. Consider this :

mysql :
  SELECT a,b,c,d,e FROM table GROUP BY a
This delivers 1 row per value of a (which one, you don't really know). Well actually you can guess, because MySQL doesn't know about hash aggregates, so it will probably use a sort... but it will only sort on a, so the order of the rows could be random. Unless it uses a multicolumn index instead of sorting. Well, anyway, it's not specified by the query.

postgres :
   SELECT DISTINCT ON (a) a,b,c,d,e FROM table ORDER BY a,b,c
This delivers 1 row per value of a, this row will be the first one in the sort according to the ORDER BY specified by the query. Simple.

Collectives™ on Stack Overflow

Postgres: Error using GROUP BY and ORDER (on heroku)

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related