CODEWITHSUNDEEP
ruby-on-rails-3postgresqlactiverecord
yellowreign
yellowreign

Reputation: 3638

ActiveRecord/PostgreSQL - GROUP BY clause or be used in an aggregate function

I'm trying to do this Railscast on Tagging with my Rails 3.2 app on Postgres. I have it working excepting for the tag cloud functionality. However, I'm adding a layer by looking to form tag clouds based on tags an user created.

I'm trying to adapt/revise the code from the Railscast:

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

but when I run this, I get the PG error:

PG::Error: ERROR:  column "tags.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT tags.*, count(taggings.tag_id) a...

Here's how the models look:

user.rb

has_many :trades
has_many :taggings, :through => :trades
has_many :tags, :through => :taggings

trade.rb

has_many :taggings
has_many :tags, :through => :taggings

tagging.rb

belongs_to :tag
belongs_to :trade

tag.rb

has_many :taggings
has_many :trades, through: :taggings

If someone could help lend a hand in revising the query to do a count on a user's tags that would be most appreciated. Thanks!

Upvotes: 2

Views: 2369

Answers (1)

Moustafa Samir
Moustafa Samir

Reputation: 2268

That's because in the Rails Cast you sent SQLite is used as a database while you're using postgres ; So the problem is that database like SQLite and MySQL let you add fields that are not in the Group BY clause but postgres don't.

So You've two options

  1. you use counter_cache option in belong_to association in your tagging model.

  2. replace join with includes like the following.

    Tagging.includes(:tag).select("taggings.tag_id, count(taggings.tag_id) as count").group("taggings.tag_id")

Upvotes: 3

Related Questions