CODEWITHSUNDEEP
ruby-on-railsactiverecord
maki
maki

Reputation: 535

ActiveRecord where method call optimisation

I have a piece of code witch looks like this:

Post.all.reject {|p| p.created_at.beginning_of_month != params[:date].to_date}

Is there a method to write the same code using where method and to not get all elements?

Upvotes: 1

Views: 77

Answers (3)

tihom
tihom

Reputation: 8003

mysql has a MONTH function to get the month of a datetime column.

 Post.where("MONTH(created_at) != ?", params[:date].to_date.month)

Upvotes: 3

m_x
m_x

Reputation: 12564

AFAIK, there is no database-agnostic solution to this, because you need to extract the month from the date. So, in raw SQL you would have :

date = params[:date].to_date
Post.where("MONTH(created_at) != ? AND YEAR(created_at) = ?", [date.month, date.year])

Now it is possible to cheat a bit with normalization in order to use a db-agnostic solution. Just add some created_at_month and created_at_year columns to your model, along with this callback :

after_create :denormalize_created_at
def denormalize_created_at
  assign_attributes created_at_month: created_at.month, 
                    created_at_year:  created_at.year
  save validate: false 
end

Now you can do:

Rails < 4 :

date = params[:date].to_date
Post
  .where(Post.arel_table[:created_at_month].not_eq date.month)
  .where(created_at_year: date.year)

Rails 4+ :

date = params[:date].to_date
Post.not(created_at_month: date.month).where(created_at_year: date.year)

Upvotes: 3

davegson
davegson

Reputation: 8331

If you want to use where, I'd go by:

# x-month being a date from your desired month.
# .. defines the range between the beginning and the end
Post.where(:created_at => x-month.beginning_of_month..x-month.end_of_month)

Upvotes: 4

Related Questions