CODEWITHSUNDEEP
sqlruby-on-rails
Bazley
Bazley

Reputation: 2847

SQL ORDER BY clause causing GROUP BY/aggregate error

I get this error:

PG::GroupingError: ERROR:  column "relationships.created_at" must appear in the GROUP BY clause or be used in an aggregate function

from this query:

last_check = @user.last_check.to_i
@new_relationships = User.select('*')
                         .from("(#{@rels_unordered.to_sql}) AS rels_unordered")
                         .joins("
                    INNER JOIN  relationships
                    ON          rels_unordered.id = relationships.character_id
                    WHERE       EXTRACT(EPOCH FROM relationships.created_at) > #{last_check}
                    ORDER BY    relationships.created_at DESC
                    ")

Without the ORDER BY line, it works fine. I don't understand what the GROUP BY clause is. How do I get it working and still order by relationships.created_at?

EDIT

I understand you can GROUP BY relationships.created_at. But isn't grouping unnecessary? Is the problem that relationship.created_at is not included in the SELECT? How do you include it? If you've already done an INNER JOIN with relationships, why the hell isn't relationships.created_at included in the result??

I've just realised this is all happening because the logs show the query begins with SELECT COUNT(*) FROM..... So the COUNT is the aggregate function. But I never requested a COUNT! Why does the query start with that?

EDIT 2

Ok, this seems to be happening because of lazy querying. The first thing that happens to @new_relationships is @new_relationships.any? This affects the query and turns it into a count. So I suppose the question is, how do I force the query to run as originally intended? And also to check if @new_relationships is empty without affecting the sql query?

Upvotes: 0

Views: 359

Answers (2)

Deepak Mahakale
Deepak Mahakale

Reputation: 23661

You just need to add group by along with your order by clause

last_check = @user.last_check.to_i     
@new_relationships = 
  User.select('"rels_unordered".*')
      .from("(#{@rels_unordered.to_sql}) AS rels_unordered")
      .joins("INNER JOIN relationships   
              ON rels_unordered.id = relationships.character_id 
              WHERE EXTRACT(EPOCH FROM relationships.created_at) > #{last_check} 
              GROUP BY relationships.created_at
              ORDER BY relationships.created_at DESC ")

Upvotes: 1

Jackson
Jackson

Reputation: 225

It's asking for a GROUP BY after your FROM clause in your EXTRACT (essentially a subselect). There's ways around it, but I've found it's often easier to make a GROUP BY work. Try: ...FROM relationships.created_at GROUP BY id or whatever indexing column you are using from that table. It seems like your ORDER BY is conflicting with itself. By grouping the subselect data it should lose its conflict.

Upvotes: 0

Related Questions