Active record query issues in Rails 3.2

Question

I have category model and category has many postings.

Problem: Sometimes postings are not visible under category in web even records are present in database

I investigated the query for the action in production enviroment by enabling config.log_level = :debug and restarted the nginx passenger server. Now I can see the records under the category. I was unable to reproduce the same issue again and it occurs rarely.

Note:

1. I didn't change any code in the project. The same code behave differently.
2. Rails is 3.2.22. Nginx passenger(5.1.1)

Model are as follows

class Category < ActiveRecord::Base
  has_many :postings, conditions: ['paid = ? AND start_date <= ? AND end_date >= ?', true, Date.current, Date.current]
end

class Posting < ActiveRecord::Base
  searchkick

  belongs_to :category

  class << self

    def payed
      where paid: true
    end

    def activated
     where :code => ""
    end

    def starts_on(date)
      where "start_date <= ?", date
    end

    def ends_after(date)
      where "end_date >= ?", date
    end

    def in_location(state,city)
      where(stateid: state.id, cityid: city.id)
    end

    def not_deleted
      where "active != false"
    end
end

Postings controller

def index
  @category = Category.find(params[:category_id])
  postings = @category.postings.payed.activated.not_deleted.starts_on(Date.current).ends_after(Date.current).order(:created_at)
  @postings = postings.in_location(current_state, current_city).page(params[:page])
end

From production.log, when accessing postings page /postings?category_id=25

Category Load (0.2ms) SELECT categories.* FROM categories WHERE categories.id = 25 LIMIT 1

(0.4ms) SELECT COUNT(*) FROM postings WHERE postings.category_id = 25 AND postings.paid = 1 AND postings.code = '' AND postings.stateid = 44 AND postings.cityid = 14823 AND (active != false) AND (paid = 1 AND listing_start_date <= '2017-03-13' AND listing_end_date >= '2017-03-13') AND (listing_start_date <= '2017-03-13') AND (listing_end_date >= '2017-03-13')

CACHE (0. SELECT COUNT(*) FROM postings WHERE postings.category_id = 25 AND postings.paid = 1 AND postings.code = '' AND postings.stateid = 44 AND postings.cityid = 14823 AND (active != false) AND (paid = 1 AND listing_start_date <= '2017-03-13' AND listing_end_date >= '2017-03-13') AND (listing_start_date <= '2017-03-13') AND (listing_end_date >= '2017-03-13')

Posting Load (0.4ms) SELECT postings.* FROM postings WHERE postings.category_id = 25 AND postings.paid = 1 AND postings.code = '' AND postings.stateid = 44 AND postings.cityid = 14823 AND (active != false) AND (paid = 1 AND listing_start_date <= '2017-03-13' AND listing_end_date >= '2017-03-13') AND (listing_start_date <= '2017-03-13') AND (listing_end_date >= '2017-03-13') ORDER BY created_at LIMIT 10 OFFSET 0

The above set of queries did not pick any records; and after enabling debug mode and restart/touch the nginx server the same query fetched available records

Is the problem caused by active record query/ Nginx/ cache?

Please help me to resolve this issue.

Floris

Found out problem caused by 'Date.current' in category model which returned previous date instead of current server date but in controller returned current server date. Eg. I touched nginx server today 2017-03-13, Date.current in both model and controller returns current server date 2017-03-13. If I try on next day 2017-03-14, Modal 'Date.current' still returns yesterday 2017-03-13 and controller Date.current returns current server date 2017-03-14.I didn't configure time zone in application.rb and its by default UTC but the server time is Central Time(US&Canada). did I miss any configuration? – Floris

Answer 1

Fixed the problem using Proc for association condition like

has_many :postings, conditions: proc { "payed = 1 AND start_date <= '#{Date.current.to_s(:db)}' AND end_date >= '#{Date.current.to_s(:db)}'"}

If you would have done association with dynamic condition like has_many :postings, conditions: ['paid = ? AND start_date <= ? AND end_date >= ?', true, Date.current, Date.current], there will be cases when the results you’ll get are not expected since the condition will have the day you started the Rails application and Date.current won’t be called again.

Thanks to Jose M.Gilgado. Reference: http://josemdev.com/articles/dynamic-conditions-associations-rails-3/

Floris