Ruby:如何在rails 3模型中获得所有可用的年份

i have a lot of articles in my rails 3 app. To put links on the index page, how can i get all available years ( from created_at attribute ) in an array to do something like this

我在我的rails 3应用程序中有很多文章。要在索引页面上放置链接，我如何才能让数组中的所有可用年份(从created_at属性)都执行这样的操作

years.do | year |
 = link_to year, articles_path(:year => year)

any ideas how to do this with minimum effort (regarding the DB querys )?

任何想法如何用最少的努力做到这一点(关于DB querys)?

Greetings, Adrian

问候,艾德里安

EDIT1: This might not be the best/fastest. But fl00r reminded me on pluck:

这可能不是最好的/最快的。但是fl00r让我想起了勇气:

@years = []

Article.pluck(:created_at).each do | year |
    @years << year.strftime("%Y")
end

@years = @years.uniq.reverse

3 个解决方案

#1

An alternative to the existing answers if you only want an array of years:

如果你只想要一系列的年份，一个替代现有答案的选择:

@years = Article.uniq.pluck("EXTRACT(YEAR FROM created_at)")

that generates the following SQL query:

生成以下SQL查询:

SELECT DISTINCT EXTRACT(YEAR FROM created_at) FROM `articles`

#2

@years = Article.select("YEAR(created_at) as year").group("YEAR(created_at)").pluck(:year)
@years.each do |year|
  = link_to year, articles_path(year: year)
end

or, as @seph mentioned

或者,正如@seph提及

@years = Article.select("DISTINCT YEAR(created_at) as year").pluck(:year)
@years.each do |year|
  = link_to year, articles_path(year: year)
end

#3

This solution is highly dependent on your database, but it should be fast.

这个解决方案高度依赖于您的数据库，但它应该很快。

Create a class method for selecting articles by the date portion of their created_at timestamp. Here's an example for SQLite:

创建一个类方法，根据created_at时间戳的日期部分选择文章。这里有一个SQLite的例子:

class Article
  def self.by_year(year)
    where(['strftime("%Y", created_at) = "?"', year])
  end
end

Then your controller can call that method like this:

然后你的控制器可以这样调用这个方法:

@articles = Article.by_year(params[:year])

Your view code has a slight syntax error (years.do should be years.each do), but other than that, it looks fine.

您的视图代码有一个小的语法错误(年)。做应该年。每个都是)，但除此之外，它看起来很好。

#1