Rails ActiveRecord where datetime not working

Question

I have a Model which has_one Schedule (from:datetime, from_a:datetime, to:datetime). I want to retrieve all models that have a schedule that fits the datetime the query is run.

For example, I have the model m that has a schedule of from: Sun, 15 Oct 2018 19:00:00 UTC +00:00, from_a: Sun, 14 Oct 2018 19:20:00 UTC +00:00 and to: Sun, 14 Oct 2018 20:00:00 UTC +00:00. If the current time is Sun, 14 Oct 2018 19:14:00 UTC +00:00 then and I search with only from then I would get m, but if the current time is Sun, 14 Oct 2018 20:01:00 UTC +00:00 and I search with only from I would get nothing.

Here is the code that I've tried:

scope :s, ->(time, type) { unscoped.joins(:schedule).where("\"schedule.#{type.to_s}\" < ? AND \"schedule.to\" > ?", time, time) }

and when I call the scope would be Model.s(DateTime.now, :from). The thing is that this code is not working as intended as it doesn't give me the correct result, nor the result that I expect.

UPDATE
This is the generated query for Model.s(DateTime.now, :from)

SELECT "models".* FROM "models" INNER JOIN "schedules" ON "schedules"."model_id" = "models"."id" WHERE ("schedules.from" < '2018-10-15 19:59:33.737073' AND "schedules.to" > '2018-10-15 19:59:33.737073')

Answer 1

You have several problems, the first of which hides the others:

1. Single quotes are used for string literals in SQL so 'schedule.from' and 'schedule.to' are just strings in SQL, not references to columns in the schedule table.
2. schedule should be schedules since Rails likes to use plurals for table names.
3. to and from are keywords in SQL and PostgreSQL (which you appear to be using) so they have to be (double) quoted when you use them as column names.

The first is fixed by removing the stray single quotes. The second by using a plural table name. The third by double quoting the offending identifiers. The result would be something like:

unscoped.joins(:schedule).where(%Q(schedules."#{type.to_s}" < ? and schedules."to" > ?), time, time)

or perhaps:

unscoped.joins(:schedule).where(%Q(schedules."#{type.to_s}" < :time and schedules."to" > :time), time: time)