How do I run a Cron job which updates a particular column value once per day in ruby on rails?

Question

Database - Postgres

So I am very new to Ruby on rails and a Jr. developer so not very much aware of this task.

I have a PLSQL query which checks if there is a child whose age is more than 5 from the current date. so this is connected to. another table people. You can read the query, (name of the original tables have been changed, people and children are demo based)

I want to run a cron job once per day which checks this.

I have gone through many questions, but I am not able to understand, that how and where should I write my PLSQL query and how to use it in my cron job using whenever gem.

select ch.person_id as child_id ,
(abs(current_date::date - 
to_char(to_timestamp(ppl.date_of_birth),'YYYY-MM-DD')::timestamp::date) / 365) as current_age 
from children as ch inner join people as ppl
on ch.person_id = ppl.id
where 
((abs(current_date - 
to_char(to_timestamp(ppl.date_of_birth),'YYYY-MM-DD')::timestamp::date)) / 365 >= 5) limit 500;

I want to run this query every single day once as a cron job.

So how do I do this using whenever gem? Where and how do I write this query in my project? How do I connect this query to my cron job?

I am stuck in this for days, thanks in advance.

score 0 · Accepted Answer · answered Oct 19 '22 at 05:34

The Whenever gem allows multiple ways of writing your cronjobs. Though I personally prefer rake-tasks, as this also makes them easily executed manually. Or easy migrated, if for example you change deployment to Kubernetes and will use a different solution for cronjobs.

Therefore, this example uses a rake task.

Create a file like this: lib/tasks/children.rake

namespace :children do
  desc 'Add a valid description'
  task(older_than_five: :environment) do
    sql = 'select ch.person_id as child_id, ' \
          '(abs(current_date::date - 
to_char(to_timestamp(ppl.date_of_birth),'YYYY-MM-DD')::timestamp::date) / 365) as current_age' \
          'from children as ch inner join people as ppl' \
          'on ch.person_id = ppl.id' \
          'where ' \
          '((abs(current_date - 
to_char(to_timestamp(ppl.date_of_birth),'YYYY-MM-DD')::timestamp::date)) / 365 >= 5) limit 500;'

    results = ActiveRecord::Base.connection.execute(sql)

    # Do something with the results
  end
end

Then you want the following in your schedule.rb

every 1.day, at: '4:30 am' do
  rake "children:older_than_five"
end

By the way, I don't really understand your data structure, as you changed all the relation names. But looking at the example query, you could also use Active record to write this query.

It would be something like this:

class Child < ApplicationRecord
  belongs_to :person
  
  def current_age
    person.current_age
  end
end

class Person < ApplicationRecord
  
  def current_age
    Date.today.year - person.date_of_birth.year
  end
end

results = Child.includes(:people).where("people.date_of_birth >= ?", 5.years.ago).limit(500)

results.first # This is a Child object
results.first.person_id # You rename Child.person_id to child_id in your query
results.first.current_age

Thank you so much Dennis, I will try this. So I will be using the rake now as you mentioned and not the ActiveRecord. I will let you know once I am done. Just one question, after writing these in the rake task and also the schedule.rb file. How do I run this, any command to run this? Thanks again — Akash, Oct 19 '22 at 05:52
The wenever-gem needs to update the crontab, this is normally done during deployment: `whenever --update-crontab` Locally, you can test this with `rake children:older_than_five` without applying the schedule — Dennis van de Hoef - Xiotin, Oct 19 '22 at 05:54
On writing this SQL query in the children.rake file, the IDE (Ruby Mine) is showing that, "cannot find 'YYYY-MM-DD' ", what changes should I make while writing the query? — Akash, Nov 01 '22 at 05:26
the problem is that you use single quotes to define the string, therefore the single quotes around YYYY-MM-DD will be seen as end and start of the string and ruby will think its a variable/method. In this case you should use doublequotes to define the string. — Dennis van de Hoef - Xiotin, Nov 03 '22 at 07:03
Thanks a lot Dennis, I'm so glad we have seniors like you. Meanwhile I tried using the escape \ character, before and after the date and it worked fine. — Akash, Nov 03 '22 at 10:42

How do I run a Cron job which updates a particular column value once per day in ruby on rails?

1 Answers1