Rails 3.1 with Postgresql: Group by Must Be Used in an Aggregate Function

Rails 3.1 with PostgreSQL: GROUP BY must be used in an aggregate function

The sql generated by the expression is not a valid query, you are grouping by user_id and selecting lot of other fields based on that but not telling the DB how it should aggregate the other fileds. For example, if your data looks like this:

a  | b
---|---
1  | 1
1  | 2
2  | 3

Now when you ask db to group by a and also return b, it doesn't know how to aggregate values 1,2. You need to tell if it needs to select min, max, average, sum or something else. Just as I was writing the answer there have been two answers which might explain all this better.

In your use case though, I think you don't want a group by on db level. As there are only 10 arts, you can group them in your application. Don't use this method with thousands of arts though:

 arts = Art.all(:order => "created_at desc", :limit => 10)
 grouped_arts = arts.group_by {|art| art.user_id}
 # now you have a hash with following structure in grouped_arts
 # { 
 #    user_id1 => [art1, art4],
 #    user_id2 => [art3],
 #    user_id3 => [art5],
 #    ....
 # }

EDIT: Select latest_arts, but only one art per user

Just to give you the idea of sql(have not tested it as I don't have RDBMS installed on my system)

SELECT arts.* FROM arts
WHERE (arts.user_id, arts.created_at) IN 
  (SELECT user_id, MAX(created_at) FROM arts
     GROUP BY user_id
     ORDER BY MAX(created_at) DESC
     LIMIT 10)
ORDER BY created_at DESC
LIMIT 10

This solution is based on the practical assumption, that no two arts for same user can have same highest created_at, but it may well be wrong if you are importing or programitically creating bulk of arts. If assumption doesn't hold true, the sql might get more contrieved.

EDIT: Attempt to change the query to Arel:

Art.where("(arts.user_id, arts.created_at) IN 
             (SELECT user_id, MAX(created_at) FROM arts
                GROUP BY user_id
                ORDER BY MAX(created_at) DESC
                LIMIT 10)").
    order("created_at DESC").
    page(params[:page]).
    per(params[:per])

PostgreSQL -must appear in the GROUP BY clause or be used in an aggregate function

@myestate1 = Estate.where(:Mgmt => current_user.Company)
@myestate = @myestate1.select("DISTINCT(user_id)")

this is what I did.

PostgreSQL - group by clause or be used in an aggregate function (error)

This solved my problem :)

Dailypost Model

def self.tag_counts
  Tag.select("tags.id, tags.name,count(taggings.tag_id) as count").
    joins(:taggings).group("taggings.tag_id, tags.id, tags.name")
end    

AplicationHelper

def tag_cloud(tags, classes)
  max = tags.sort_by(&:count).last
  tags.each do |tag|
    index = tag.count.to_f / Integer(max.count) * (classes.size - 1)
    yield(tag, classes[index.round])
  end
end

PostgreSQL error when grouping and search with full text search in SmartListing Gem

After hours searching the only solution for this problem was make the group after the full text search and add a variable named 'rank' to the query in the SELECT, like this:

search = User.find(id).prospects
if search_full_text.present? 
    search = search.search_query("Say my name")
                   .select("prospects.*").group("prospects.id, rank")
else
    search = search.select("prospects.*").group("prospects.id")
end

SQLite to Postgres (Heroku) GROUP BY

I arrived at a functional solution with the use of DISTINCT ON:

@messages = Message.select("DISTINCT ON (messages.conversation_id) * ")
                   .where("messages.sender_id = (?) OR messages.recipient_id = (?)", current_user.id, current_user.id)
                   .group("messages.conversation_id, messages.updated_at, messages.id, messages.sender_id, messages.recipient_id, messages.sender_deleted, messages.recipient_deleted, messages.body, messages.read_at, messages.ancestry, messages.ancestry_depth, messages.created_at")

However, this wont work in SQLite. Downloading Postgres and using it directly rather than having to use SQLite code in development and Postgres code in production (Heroku) is recommended.

Rails many-to-one relationship aggregate functions (group, count) active record

class Event
  def stats
    {
      event_id:      id,
      event:         self,
      comment_stats: {
        pending:  comments.where(status: Comment.statutes[:pending]).count,
        approved: comments.where(status: Comment.statutes[:approved]).count
      }
    }
  end
end

Usage:

event = Event.find(params[:id])
event.stats
#=> { event_id: 1, event: <Event object>, comment_stats: { pending:  10, approved: 20 } }
event.stats[:comment_stats]
#=> { pending:  10, approved: 20 }

Group and count activity by week in either Ruby, Activerecord, or Postgresql

I borrowed the test table from @ideamotor and simplified it. Type of activity is irrelevant, counting each activity as 1:

CREATE TEMP TABLE log(usr text, day date);
INSERT INTO log VALUES 
  ('bob' , '2012-01-01')
 ,('bob' , '2012-01-02')
 ,('bob' , '2012-01-14')
 ,('susi', '2012-01-01')
 ,('susi', '2012-01-14');

Query (won't get much more succinct than this):

SELECT usr, to_char(day, 'YYYY-WW') AS week, count(*) AS activity
FROM   log
GROUP  BY 1, 2
ORDER  BY 1, 2;

Result:

usr  | week     | activity
-----+----------+---------
bob  | 2012-01  | 2
bob  | 2012-02  | 1
susi | 2012-01  | 1
susi | 2012-02  | 1

to_char() makes this very simple. I quote the manual here:

WW week number of year (1-53) (The first week starts on the first day of the year.)

As alternatice consider:

IW ISO week number of year (01 - 53; the first Thursday of the new year is in week 1.)

---

Related Topics