To write this MySQL query like ActiveRecord

I'd like to count the number of blog posts daily and take a tally, but the way I write the group is redundant.

Currently, the results of this query are as good as you would like, but could you write more like ActiveRecord?
Post.group('DATE_FORMAT(CONVERT_TZ(created_at, "+00:00", "+09:00"), "%Y/%m/%d")'.count

ruby-on-rails ruby rails-activerecord

2022-09-30 16:58

1 Answers

ActiveRecord doesn't have that much abstraction function, so I think you have to give up and write SQL statements.

The where method can only be completed in the Ruby world using AREL's Arel::Nodes::NamedFunction, but it does not seem to be available in the group at this time.

It looks like it's going to workCode:

Post.group(
  Arel::Nodes::NamedFunction.new("DATE_FORMAT",[
    Arel::Nodes::NamedFunction.new("CONVERT_TZ",[
      Post.arel_table [:created_at],
      "+00:00",
      "+09:00"
    ]),
    "%Y of %m of %d"
  ])
) .count

2022-09-30 16:58

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656