CODEWITHSUNDEEP
ruby-on-rails
Meyer
Meyer

Reputation: 114

How to access the record attributes within Rails .where()

How would one go about accessing an object's attributes within .where()?

I am trying to run this line of code:

Task.where("(created_at <= ?", Time.now - <deadline>).update_all(expired: true)

The idea being that any task that was created_at a time older than the set deadline (Time-now - deadline) will have it's expire attribute set to true.

The problem is: deadline is an attribute of Task and each task will have it's own deadline. So I need .where() to check the following logic: for any given Task object, if created_at is less than Time.now minus (here is the part I am stuck on) than set it's expired attribute to true

As pseudo code: 

Task.where( Task's created_at attr is <= to the current time - Tasks's deadline attr ).update_all( set Task's expired attr to true )`

In summary:

I need to access an object's attribute as part of the logic within .where().

Some of my other tries:

EDIT:

  create_table "tasks", force: :cascade do |t|
    t.string   "name"
    t.integer  "user_id"
    t.datetime "created_at",                  null: false
    t.datetime "updated_at",                  null: false
    t.boolean  "expired",    default: false
    t.integer  "deadline",   default: 604800
  end

I am running rails in dev mode, so I think the DB is SQL, The deadline attribute should be an integer (created with 7.days or an actual value)

Upvotes: 0

Views: 369

Answers (2)

SteveTurczyn
SteveTurczyn

Reputation: 36860

Keep in mind that solutions are database-type dependent so what works for SQLite (the likely adapter if you've taken the rails defaults) may not work for MySQL or PostgreSQL. If you decide to deploy to Heroku, for example, your database will then be PostgreSQL.

You say that you're looking for a solution for your development environment, so it sounds like this is code you're developing and not yet deployed?

If so, I would suggest you change or DB layout to add a datetime column expires_at and add a callback method to set it when you're saving the record.

class Task < ApplicationRecord
  before_save :update_expires_at
  private
  def update_expires_at
    self.expires_at ||= Time.now + 7.days
  end
end

This will let you do...

Task.where("(expires_at <= ?", Time.now).update_all(expired: true)

But I'm not even sure if you need the expired column unless you may want to set a record to expired manually. You could just have a method in the class...

def expired?
  expires_at <= Time.now
end

and you can create a scope for expired or not expired

class Task < ApplicationRecord

  scope :expired, -> {where('expired_at <= ?', Time.now)}
  scope :active, -> {where('expired_at > ?', Time.now)}

And just use the scopes.

@my_expired_tasks = Task.expired

Upvotes: 1

Jay-Ar Polidario
Jay-Ar Polidario

Reputation: 6603

Depends on your database:

For Postgresql:

Task.where('created_at + (deadline * "1 second::INTERVAL") <= ?', Time.now)

For MySql:

Task.where('DATE_ADD(created_at, INTERVAL deadline SECOND) <= ?', Time.now)

SQL Server:

Task.where('DATEADD(second, deadline, created_at) <= ?', Time.now)

NOTE: The above is untested. I just followed the syntax of the equivalent of "adding dates function" for the different databases above from their own respective documentation pages

  • I don't know of any "ActiveRecord" approach for this.

  • Arel can work but falls short when converting deadline from seconds to "duration"; you might find a way to continue the following incomplete expression:

     Task.where((Task.arel_table[:created_at] + Task.arel_table[:deadline]).lteq Time.now)

Upvotes: 1

Related Questions