CODEWITHSUNDEEP
mysqlruby-on-rails
ltdev
ltdev

Reputation: 4457

Mysql2::Error: Expression #2 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column

I'm getting the following error:

Mysql2::Error: Expression #2 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'database_name_development.posts.created_at' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by: SELECT YEAR(created_at) AS year, MONTHNAME(created_at) AS month, COUNT(id) AS total FROMpostsGROUP BY year, month ORDER BY year DESC, MONTH(created_at) DESC

This is my model method

def self.archives
    Post.unscoped.select("YEAR(created_at) AS year, MONTHNAME(created_at) AS month, COUNT(id) AS total")
        .group("year, month")
        .order("year DESC, MONTH(created_at) DESC")
end

How can I fix this?

Upvotes: 1

Views: 1878

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270599

Beginning with MySQL 5.6, the old default behavior of allowing columns in SELECT expressions (or related ORDER BY) which did not also appear in GROUP BY when using aggregates changed to be more strict (and more like other RDBMS).

Since you have MONTH(created_at) in the ORDER BY to sort months numerically but do not have an equivalent expression in SELECT and GROUP BY, you violate MySQL's requirements for GROUP BY. The solution is to add the numeric MONTH() expression to the SELECT and the GROUP BY so the grouping can properly aggregate. This should not affect performance.

def self.archives
    # Include a MONTH() expression in SELECT
    Post.unscoped.select("YEAR(created_at) AS year, MONTHNAME(created_at) AS month, MONTH(created_at) AS monthnum, COUNT(id) AS total")
        # Group with it as well
        .group("year, month, monthnum")
        # sort with the column alias
        .order("year DESC, monthnum DESC")
end

It may be possible to leave MONTH(created_at) AS monthnum out of the .select() if it is added to .group() (since you do not actually need to return the numeric values). If that does work, you can't use the alias in .group() and .order() and instead must use the full expression. I am uncertain this will work without error, but believe it might.

def self.archives
    Post.unscoped.select("YEAR(created_at) AS year, MONTHNAME(created_at) AS month, COUNT(id) AS total")
        # Add the expression to GROUP BY but not SELECT
        .group("year, month, MONTH(created_at)")
        .order("year DESC, MONTH(created_at) DESC")
end

Upvotes: 1

Related Questions