0

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
shoujo_sm
  • 3,173
  • 4
  • 37
  • 60
  • 1
    I suggest you change the title to "Postgres: Error using GROUP BY and ORDER (on heroku)", which would be *much* more informative. – Mark Thomas Nov 14 '11 at 17:53

2 Answers2

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).

mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • 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 : http://pastie.org/2867385 – shoujo_sm Nov 15 '11 at 15:00
  • @Amy: Do you have any idea where the ORDER BY is coming from? – mu is too short Nov 15 '11 at 18:12
  • @Amy: I added another option that might work for you: `Micropost.select('DISTINCT(category)')` – mu is too short Nov 15 '11 at 18:16
  • 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. – shoujo_sm Nov 16 '11 at 00:42
  • @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. – mu is too short Nov 16 '11 at 01:08
2

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.

Community
  • 1
  • 1