Postgres: error when using GROUP BY and ORDER (on heroku)

Question

I am trying to solve my heroku problem which seems to have a problem

We are sorry, but something went wrong. We have been notified of this issue and we will review it shortly.

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

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 

Answer 1

Your immediate problem is that you are creating invalid SQL for PostgreSQL:

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

Your ORDER BY does not match the rest of your request. You cannot use a column in a grouped query, if this column is also not grouped, or if the column is displayed in an aggregate function, this means an error message. The reason is that PostgreSQL will not know which row created_at use when a group of rows is combined into a GROUP BY clause; some databases will simply select a row on their own, PostgreSQL prefers to be strict and wants you to eliminate the ambiguity.

Try to specify the order yourself:

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

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

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

By the way, you really should not do this thing in the view, you can transfer it to your controller.

Your real problem is that you are developing on top of one database when deploying to another. I would recommend switching your development environment to PostgreSQL 8.3 (if you are deploying to a shared Heroku database) or PostgreSQL 9.0 (if you are deploying to a dedicated database).

Answer 2

Have you developed your application in MySQL? Heroku does not include MySQL. It uses postgreSQL. Look at this question here - is PostgreSQL GROUP BY different from MySQL?

I quote -

MySQL, fully compliant with GROUP BY standards, can be emulated by Postgres DISTINCT ON. Consider this:

mysql:

  SELECT a,b,c,d,e FROM table GROUP BY a 

This supplies 1 row per value (which you really don't know). Well, actually you can guess, because MySQL does not know about hash aggregates, so it will probably use sorting ... but it will only sort by a, so the row order may be random. If instead of sorting it uses the multi-column index. Well, in any case, this is not indicated in the request.

postgres:

  SELECT DISTINCT ON (a) a,b,c,d,e FROM table ORDER BY a,b,c 

This provides 1 row per a value, this row will be the first in the sorting according to the ORDER BY specified in the query. Plain.