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

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.

Solution

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