Bazley Bazley - 3 months ago 6
MySQL Question

MySQL 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('"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}
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?

Answer

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 ")
Comments