How to create archive list?

Rails
#1

Hello,

I want to do archive list like in any blogs? But I cannot really think
how it’s done?

Do they group the created_at and then extract the column, but this would
work since created_at have different dates all the long way down?

I hope someone can help :slight_smile:

Thanks
Regards,
Jamal

#2

It might vary between databases, but in MySQL you could do something
like:

:select => “DATE_FORMAT(’%m/%Y’) as month_year,COUNT(posts.id) as
count”
:group => “month_year”
:order => “month_year”

That’s just off the top of my head, but the idea is to use a GROUP BY
clause on month.

On May 23, 12:34 pm, Jamal S. [email protected]

#3

dasil003 wrote:

It might vary between databases, but in MySQL you could do something
like:

:select => “DATE_FORMAT(’%m/%Y’) as month_year,COUNT(posts.id) as
count”
:group => “month_year”
:order => “month_year”

That’s just off the top of my head, but the idea is to use a GROUP BY
clause on month.

On May 23, 12:34 pm, Jamal S. [email protected]

I’m using MySQL database, and I cannot really get your example to
work…

I have column called “created_at”

def archive
@archive = Post.find(:all,
:select => “DATE_FORMAT(’%m %Y’) as month_year,
COUNT(posts.id) as count”,
:group => “month_year”,
:order => “month_year”)
end

and I’m getting error as follow:

Mysql::Error: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near ‘) as month_year, COUNT(posts.id) as count FROM posts GROUP BY
month_year ORDER’ at line 1: SELECT DATE_FORMAT(’%m %Y’) as month_year,
COUNT(posts.id) as count FROM posts GROUP BY month_year ORDER BY
month_year

#4

I think I got it right now :slight_smile:

I forgot to add the column name to date_format as below

date_format(date_column, ‘%M %D, %Y’)