Mysql to Postgresql Rails PG::GroupingError: ERROR:

Question

I just deployed my app and had to switch from mysql to postgresql. Now when executing my query I get following error:

ActiveRecord::StatementInvalid (PG::GroupingError: ERROR:  column "search_terms.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT "search_terms".* FROM "search_terms" WHERE (search_te...
               ^
: SELECT "search_terms".* FROM "search_terms" WHERE (search_terms.term like '%%') GROUP BY search_terms.game_id):

I have a table called search_terms with term:string and game_id:integer. A game can have multiple terms. So I want to group the terms that match by the game_id.

Does anyone know how to solve this?

In my model search_term.rb, I have defined this search function:

# Search Terms
  def self.search(query)
    where("search_terms.term like ?", "%#{query}%").group("search_terms.game_id")
  end

Thank you for any help.

Have you looked at any of the **Related** questions in the sidebar? Searched for ["\[postgresql\] must appear in the GROUP BY clause or be used in an aggregate function"](http://stackoverflow.com/search?q=%5Bpostgresql%5D+must+appear+in+the+GROUP+BY+clause+or+be+used+in+an+aggregate+function) perhaps? This problem happens quite often. — mu is too short, Jun 05 '16 at 21:48
Hi, checked the topics. One has the same question, but didnt get solved. — zer02, Jun 06 '16 at 06:57
I re-coded the search function using `select(:game_id).distinct` and then let it get fetched by `Game.where(id: my_distinct_ids)` — zer02, Jun 07 '16 at 10:43

score 0 · Answer 1 · edited May 23 '17 at 12:31

0

I think this is what you need to try.

 def self.search(query)
    where("search_terms.term like ?", "%#{query}%").group("search_terms.game_id", "search_terms.term")
  end

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

edited May 23 '17 at 12:31

Community

1
1

answered Jun 06 '16 at 03:49

ruby_newbie

3,190
3
18
29

Hi, I tried this and with search_terms.id inside group. It will produce a different a SQL statement and I will receive duplicates on game_id. – zer02 Jun 06 '16 at 07:00
`SELECT * FROM search_terms WHERE (search_terms.term like "%di%" ) GROUP BY search_terms.game_id` id 2 | game_id 1 id 3 | game_id 2 `SELECT * FROM search_terms WHERE (search_terms.term like "%di%" ) GROUP BY search_terms.game_id, search_terms.id` id 1 | game_id 1 id 2 | game_id 1 id 3 | game_id 2 `SELECT * FROM search_terms WHERE (search_terms.term like "%di%" ) GROUP BY search_terms.game_id, search_terms.term` id 2 | game_id 1 id 1 | game_id 1 id 3 | game_id 2 – zer02 Jun 06 '16 at 07:10

Mysql to Postgresql Rails PG::GroupingError: ERROR:

1 Answers1