Convert sql for archives to Rails ActiveRecord

Question

I'm having this class method inside my Post model for archives

class Post < ActiveRecord::Base
  def self.archives
    # fetch archives
  end
end

How can I convert this sql query to ActiveRecord query in order to put it in the self.archives method?

SELECT YEAR(`created_at`) AS `year`, MONTHNAME(`created_at`) AS `month`, COUNT(`id`) AS `total` 
FROM `posts` 
GROUP BY `year`, `month` 
ORDER BY `year` DESC, MONTH(`published_at`) DESC

why not wrap it inside an `ActiveRecord::Base.connection.execute()`? — Tamer Shlash, Sep 19 '17 at 10:26

score 0 · Accepted Answer · answered Sep 19 '17 at 10:31

0

You can try something like this:

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

answered Sep 19 '17 at 10:31

Māris Cīlītis

173
1
7

`Mysql2::Error: Expression #2 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'railsblog_development.posts.published_at' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by: SELECT YEAR(published_at) AS year, MONTHNAME(published_at) AS month, COUNT(id) AS total FROM posts GROUP BY year, month ORDER BY year DESC, MONTH(published_at) DESC` – ltdev Sep 21 '17 at 21:44
You can disable this. Check answer: https://stackoverflow.com/questions/23921117/disable-only-full-group-by – Māris Cīlītis Sep 25 '17 at 05:42

Convert sql for archives to Rails ActiveRecord

1 Answers1