CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-5mysql2
dev404
dev404

Reputation: 1098

How do you group by years from 'created_at', using ActiveRecord?

In SQL, I'd use something like this:

SELECT year(created_at)
FROM videos
GROUP BY year(created_at)

How can I run this query with Rails?

I've tried this:

Video.all.group_by { |m| m.created_at.year }

... but this will return ALL my records grouped by the corresponding years, which is not entirely bad, except it's returning hundreds of records, when I only need to know the years.

Any pointers?

In case you're wondering I'm using Rails 5.1.5 and MariaDB (10.2.14-MariaDB-10.2.14+maria~xenial-log).

Upvotes: 0

Views: 2064

Answers (6)

Ankur Pohekar
Ankur Pohekar

Reputation: 139

If you want only years from data you can do

videos = Video.group("created_at").select("videos.created_at")

it will fetch only created_at column

and you can access year from created_at column by

videos.first.created_at.year

Upvotes: 0

mohnstrudel
mohnstrudel

Reputation: 649

I'm using

Event.group("strftime('%Y', created_at)")

For example: 

Event.group("strftime('%Y', created_at)").count

has the output: 

{"2019"=>456}

You can even group by a child's created_at this way:

Application.group("strftime('%Y', events.created_at)")

This would require, that an applications has many events in my particular case.

Upvotes: 0

Sergio Tulentsev
Sergio Tulentsev

Reputation: 230346

Not everything can be expressed in activerecord-speak without losing efficiency here or there. Or some things can't be expressed at all. That's why you can run arbitrary SQL:

Video.connection.execute("select year(created_at)....")

Having actually read your SQL, you want distinct years of the records? So, perhaps this? Should be even faster (benchmarking needed).

SELECT DISTINCT YEAR(created_at)  FROM videos

Upvotes: 1

Sergio Tulentsev
Sergio Tulentsev

Reputation: 230346

Turns out, pluck accepts expressions, not only column names. So you can do this for max efficiency (at the cost of portability).

Video.distinct.pluck("year(created_at)") # mysql
Video.distinct.pluck("extract(year from created_at)") # postgresql

Same efficiency as running raw sql (from my other answer), but looks a bit more ActiveRecord-y.

If you want a portable solution, then I can't think of anything better than Mark's answer.

Upvotes: 3

John Baker
John Baker

Reputation: 2398

You can also do:

Video.all.map {|u| u.created_at.year}

This would return a array containing the years in which each Video was created.

Upvotes: 0

Mark Merritt
Mark Merritt

Reputation: 2695

You could do something like this to get an array of unique years...

Video.pluck(:created_at).map{ |dt| dt.year }.uniq

Upvotes: 3

Related Questions