CODEWITHSUNDEEP
ruby-on-railsactiverecordtimestamp
LikeMaBell
LikeMaBell

Reputation: 1609

How to compare created_at reliably for Rails Activerecords

Basically, my query is saying that a record's created_at is greater than its own created_at.

irb(main):025:0> u = User.first

User Load (1.0ms)  SELECT "users".* FROM "users" LIMIT 1
=> #<User id: 1, email: "[email protected]", encrypted_password: "stuff...", created_at:
"2012-02-01 18:56:45", updated_at: "2012-03-17 21:10:13">

irb(main):026:0> User.where("created_at > ?", u.created_at).include? u

User Load (1.0ms)  SELECT "users".* FROM "users" WHERE (created_at > '2012-02-
01 18:56:45.740392')
=> true

The query shown makes it clear that it's a datetime formatting issue...when it builds the query it's rounding the fractions of a second. Can I modify the query to get it to order by created_at predictably/consistently?

I've poked around some other questions that discuss ActiveRecord precision and suggest strftime, etc., but I can't get it to work reliably. I'm using SQLite in dev (my console quote above) and Postgres in production.

Note: The broader goal here is to add #next and #previous methods to most/all of my resources so I can iterate through them more easily in my admin menus. If there's another way to achieve a default order that's reliable I'm open to it.

Both methods work properly as coded below when I pass non-timestamp arguments like :name, but #next returns the same object if I pass no argument (defaulting to :created_at)

def previous(column = :created_at)
 self.class.first(:conditions => ["#{column} < ?", self.send(column)], :order => "#{column} desc")
end

def next(column = :created_at)
 self.class.first(:conditions => ["#{column} > ?", self.send(column)], :order => "#{column} asc")
end

Upvotes: 2

Views: 757

Answers (2)

LikeMaBell
LikeMaBell

Reputation: 1609

And this will resolve my comment about skipping over records with equal values for the field being sorted on.

def previous(column = :id)
 self.class.first(:conditions => ["#{column} = ? AND id < ?", self.send(column), self.id], :order => 'id desc') ||
  self.class.first(:conditions => ["#{column} < ?", self.send(column)], :order => "#{column} desc")     
end

def next(column = :id)
 self.class.first(:conditions => ["#{column} = ? AND id > ?", self.send(column), self.id], :order => 'id asc') ||
  self.class.first(:conditions => ["#{column} > ?", self.send(column)], :order => "#{column} asc")
end

Upvotes: 0

olamork
olamork

Reputation: 179

For that case you should just use the id and don't worry about the date (it'll work more or less the same anyway). It's naturally ordered, indexed, etc.

def previous(column = :id)
  self.class.first(:conditions => ["#{column} < ?", self.send(column)], :order => "#{column} desc")
end

def next(column = :id)
  self.class.first(:conditions => ["#{column} > ?", self.send(column)], :order => "#{column} asc")
end

Upvotes: 1

Related Questions