0

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?

Bazley
  • 2,699
  • 5
  • 36
  • 61
  • 2
    Possible duplicate of [Is there ANY\_VALUE capability for mysql 5.6?](http://stackoverflow.com/questions/37089347/is-there-any-value-capability-for-mysql-5-6) – e4c5 Aug 16 '16 at 15:17
  • 2
    When GROUP BY (and SELECT DISTINCT and UNION), only select list items may be specified in the ORDER BY. – jarlh Aug 16 '16 at 15:18

2 Answers2

1

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 ")
Deepak Mahakale
  • 22,834
  • 10
  • 68
  • 88
  • Sorry to un-accept your answer, but: the change you suggested worked initially, but it is now complaining that `rels_unordered.id must appear in the GROUP BY clause or be used in an aggregate function`. Would it be better to avoid a `GROUP BY` clause altogether, and instead include `relationships.created_at` in the aggregate function? How would that be coded? – Bazley Aug 23 '16 at 15:07
0

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.

Jackson
  • 217
  • 1
  • 15
  • NB: While it should lose the conflict it can also eliminate desired data from your output, if that happens sample tables would help get you the right syntax – Jackson Aug 16 '16 at 15:26