How best to work around PostgreSQL's stricter grouping

Question

I am trying to convert from using MySQL to using PostgreSQL. I have this type of structure:

User(entity) -> Follow -> Business(entity) -> Story

The user needs to see all the news updates put out by the businesses they follow. The following query works great with MySQL because it simply shows all associated stories and groups by the story.id. Unfortunately, being that PostgreSQL is much more literal in the interpretation of the SQL standard, if I want to do the GROUP BY clause I need to ask for each field individually using the DISTINCT clause which.

Story.find(:all, :joins => { :entity => { :followers => :follower } }, :conditions => ['followers_follows.id = ?', 4], :group => 'stories.id')

PostgreSQL spits out: "ERROR: column "stories.entity_id" must appear in the GROUP BY clause or be used in an aggregate function"

Having to specify each field individually seems inelegant. If anybody can give me a clean way to get the same result as MySQL without having to resort to getting duplicate fields (removing the group by) or having to specify each individual field with along with the DISTINCT clause, I'd appreciate it!

Thanks!

Answer 1

Well, I certainly wouldn't say that PostgreSQL's interpretation of the SQL standard is too strict. In fact, it's the other way around.

Here's a possible solution:

Story.all( :joins => { :entity => { :followers=> :follower } },
           :conditions => ['followers_follows.id = ?', 4], 
           :group => Story.column_names.map { |c| "stories.#{c}" }.join(', ') )

But there are many alternative queries. I blogged about this a few weeks ago. Here's the post: http://awesomeful.net/posts/72-postgresql-s-group-by