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?