Finding multiple time slots with Squeel gem

Question

I have a model Planning with a start_time attributes. Say I would like to get all the plannings that are between 9AM and 12PM OR between 6PM and 11PM.

Basically I would do:

Planning.where do 
     (start_time >= @start_time[0]) & (start_time <= @end_time[0]) 
     | 
     (start_time >= @start_time[1]) & (start_time <= @end_time[1])
end

The thing is that the number of time slots varies... Any thougths?

If it can help, I use Squeel gem.

Thanks in advance!

score 3 · Accepted Answer · answered Nov 24 '12 at 10:30

You can do whatever you want within the where block; yet you must return the actual query at the end, because this is what will be used as the where clause.

So, given an array of times like this:

times = [ [ '09:00:00', '12:00:00' ], [ '18:00:00', '23:00:00' ] ]

Here is a verbose solution:

Planning.where do
  query = nil

  times.each do |a,b|
    q = (start_time >= a) & (end_time <= b)

    if query
      query |= q
    else
      query = q
    end
  end

  query
end

Here is a more clever solution:

Planning.where do
  times.map { |a,b| (start_time >= a) & (end_time <= b) }.reduce(&:|)
end

Both do generate the following SQL:

SELECT "plannings".* FROM "plannings"
WHERE ((
  ("plannings"."start_time" >= '09:00:00' AND "plannings"."end_time" <= '12:00:00')
  OR
  ("plannings"."start_time" >= '18:00:00' AND "plannings"."end_time" <= '23:00:00')
))

Cool stuff - pity there are not much examples on complex queries with SQUEEL! — prikha, Apr 10 '13 at 09:15

Pierre Schambacher · Answer 2 · 2012-11-24T10:23:55.620

1

Can you copy and paste the SQL generated by your ruby code, please ?

EDIT

Ok now I understand your problem and question it was not clear. If you want to keep the code readable you should use ARel instead of squeel in this case (at least not the DSL not made for this). You should be able to apply a map function and then join everything with a OR.

edited Nov 24 '12 at 10:23

answered Nov 23 '12 at 08:50

Pierre Schambacher

528
5
17

Here is the query if I do like systho says. It'll put an 'AND' statement instead of an 'OR' `SELECT "courses".* FROM "courses" INNER JOIN "plannings" ON "plannings"."course_id" = "courses"."id" WHERE (("plannings"."start_time" >= '2000-01-01 09:00:00.000000' AND "plannings"."start_time" <= '2000-01-01 12:00:00.000000')) AND (("plannings"."start_time" >= '2000-01-01 12:00:00.000000' AND "plannings"."start_time" <= '2000-01-01 14:00:00.000000'))` – Nima Izadi Nov 23 '12 at 11:54

systho · Answer 3 · 2012-11-24T11:13:03.030

0

Squeel where() method is returning an AR:Relation, isnt'it ?

You should then be able to chain the where() calls :

finder = Planing.scoped 
time_slots.each do |start_time, end_time|
    finder = finder.where{(start_time >= my{start_time}) & (start_time <= my{end_time}) }
end

I haven't tried this code but I see no reason why it wouldn't work

EDIT : As you stated this will link the conditions with AND , not OR

can you try the following ?

Planning.where do 
    time_slots.inject(false) do |memo, time_slot| 
        memo | (start_time >= time_slot.first) & (start_time <= time_slot.last)
    end
end

This might be a little too much magick to work with the instance_eval of squeel but give it a try :)

edited Nov 24 '12 at 11:13

answered Nov 23 '12 at 08:31

systho

1,161
7
17

It doesn't work because it will put an "AND" statement in the query and not an 'OR': `(("plannings"."start_time" >= '2000-01-01 09:00:00.000000' AND "plannings"."start_time" <= '2000-01-01 12:00:00.000000')) AND (("plannings"."start_time" >= '2000-01-01 12:00:00.000000' AND "plannings"."start_time" <= '2000-01-01 14:00:00.000000'))` – Nima Izadi Nov 23 '12 at 11:52
I get `NoMethodError (undefined method `inject' for nil:NilClass)` :/ – Nima Izadi Nov 23 '12 at 17:20
I was supposing you could put all your start_time and end_time in an Array => time_slot = [[start_time1, end_time1], [start_time2, end_time2], [start_time3, end_time3]] – systho Nov 24 '12 at 11:14
That's what I did but I may have missed something. – Nima Izadi Nov 24 '12 at 13:30
Ok, anyway the answer from Julien was cleaner :) – systho Nov 24 '12 at 14:50

Finding multiple time slots with Squeel gem

3 Answers3