Hi Tom Great to know you got it all working. You're right, that code is pretty complex... I'd probably pull the date range generation out into a separate method, where you pass in two dates, it figures out which comes first, and returns a range. That should clean it up at least a little bit.
Cheers -- Pat On 20/02/2010, at 4:14 AM, Tom Cocca wrote: > Thanks again for the tips Pat, I got it working! > > I left the index as a datetime field: > has open_houses.start, :as => :open_house_start > > as I couldn't get the following to index as a multi attribute for the has > many (or at least I didn't think i did, it could easily have another error I > was having). > has "CAST(open_houses.start AS DATE)", :as => :house_start, :type => :datetime > > Now the search works as follows: > > Take example values: > saturday_open_house_date = "2010-02-20" > sunday_open_house_date = "2010-02-21" > > search logic: > > if saturday_open_house && sunday_open_house > if saturday_open_house_date < sunday_open_house_date > start_time = DateTime.parse(saturday_open_house_date).to_f.to_i > end_time = DateTime.parse(sunday_open_house_date + " 23:59:59").to_f.to_i > else > start_time = DateTime.parse(sunday_open_house_date).to_f.to_i > end_time = DateTime.parse(saturday_open_house_date + " > 23:59:59").to_f.to_i > > without_start_time = (DateTime.parse(sunday_open_house_date + " > 23:59:59") + 1.second).to_f.to_i > without_end_time = (DateTime.parse(saturday_open_house_date) - > 1.second).to_f.to_i > args[:without][:open_house_start] = without_start_time..without_end_time > end > elsif saturday_open_house > start_time = DateTime.parse(saturday_open_house_date).to_f.to_i > end_time = DateTime.parse(saturday_open_house_date + " 23:59:59").to_f.to_i > elsif sunday_open_house > start_time = DateTime.parse(sunday_open_house_date).to_f.to_i > end_time = DateTime.parse(sunday_open_house_date + " 23:59:59").to_f.to_i > end > args[:with][:open_house_start] = start_time..end_time > > Property.search(args) > > I had to use DateTimes to avoid the time zone issues set by the rails app as > the dates are stored in UTC in the DB. However a DateTime can't be directly > converted to an INT you need to go to Float then to Int. > > The search then takes the 00:00:00 time to the 23:59:59 time of either the > weekend (sat and sun) or saturday only or sunday only. > > And if today is "sunday" (2010-02-21) that means the saturday date is next > saturday (2010-02-27) so when this is the case you want the range of sunday > (2010-02-21 00:00:00) to next sat (2010-02-27 23:59:59) excluding everything > from monday (2010-02-22 00:00:00) to friday (2010-02-26 23:59:59) > > Its not pretty at all I'm not very proud of it but it works and thats all > that matters right now (as long as the clients requirements don't change - > cause then i'll probably have to re-write everything). > > If anybody has any ideas on how to clean this up I'd love to hear them > > Thanks again for all your help, I really appreciate the tip on without, that > saved me here. > ~ Tom > > > On Thu, Feb 18, 2010 at 11:53 PM, Pat Allan <[email protected]> wrote: > Hi Tom > > You are correct that Sphinx treats dates and datetimes as UNIX timestamps. > And you can have a manual SQL string for attributes... maybe the following? > > has "CAST(open_houses.start AS DATE)", :as => :house_start, :type => > :datetime > > You will probably need to pass in Datetimes to the filter, though... I wasn't > sure how TS would handle Dates. > > As for searching for two Sundays... no idea if this works, but you *could* > try searching over 8 days (from the start of the first Sunday to the end of > the second), and then have an excluding filter on the days inbetween... > > Property.search :with => {:house_start => (@first..(@last + 1.day))}, > :without => {:house_start => ((@first + 1.day)....@last)} > > Or something like that... no promises if Sphinx likes it, though. > > -- > Pat > > On 19/02/2010, at 9:09 AM, Tom Cocca wrote: > > > Hi Pat, > > > > Sorry it took me so long to get back to this to test it, I don't think I > > can use Date.new as it tries to run .to_i or .to_f on whatever you are > > passing in which means I either need to use Time or Datetime > > > > The issue I see here is that the column open_houses.start is a datetime > > column in mysql. I only care about the date portion. > > > > Sphinx stores dates and datetimes as unix timestamps it looks like. > > > > So how do you search on the full day? Like from 00:00:00 to 11:59:00 to > > ensure that you get that value that is a datetime timestamp in the range. > > > > Also, in addition to that, say I wanted to search on 2010-02-21 OR > > 2010-02-28 (two sundays) how can you do this query because I only want the > > two sundays with no other dates, so I can't really do a range. I would > > need 2 ranges I think. > > > > Or am I thinking about this all wrong? > > > > Is there any way to convert the datetime to a date before giving it to > > sphinx? > > > > Can you do an indexes "CAST(open_houses.start as DATE)" on a has_many > > relationship? Although that wouldn't allow me to use the :with arguments > > for TS correct? I would have to use conditions which doesn't allow for > > ranges or arrays for conditions? I could be wrong. > > > > Thanks, > > ~ Tom > > > > > > On Thu, Feb 11, 2010 at 9:02 PM, Pat Allan <[email protected]> > > wrote: > > Hi Tom > > > > I *think* (but it's worth examining the generated SQL in the conf file) > > that multiple dates will become a MVA of timestamp integers. > > > > So, using your index definition, to search on a couple of dates, you could > > try the following: > > > > Property.search :with => {:open_house_date => [Date.new(2010, 2, 12), > > Date.new(2010, 2, 13)]} > > > > The only change you may need to make is to add .to_i to the end of those > > dates, and hopefully that'll do the job. Let me know if I'm wrong :) > > > > Cheers > > > > -- > > Pat > > > > On 12/02/2010, at 1:53 AM, Tom Cocca wrote: > > > > > I am looking to index a datetime column on a has_many relationship: > > > > > > class Property < ActiveRecord::Base > > > has_many :open_houses > > > end > > > > > > The column I want to query on open_houses is :start > > > so In the property index I want to do something like: > > > has open_houses(:start), :as => :open_house_date > > > > > > But when I query on that relationship I don't want to query as a > > > datetime only the date portion. > > > > > > So in SQL I used to do something like the following: > > > > > > oh_dates = ["2010-02-12", "2010-02-13"] > > > Property.find(:all, :conditions => ["CAST(open_houses.start as date) > > > IN (?)", oh_dates]) > > > > > > How can I duplicate this w/ Thinking Sphinx? Or does the conversion > > > from datetime to date no longer matter? > > > > > > Thanks, > > > ~ Tom > > > > > > -- > > > You received this message because you are subscribed to the Google Groups > > > "Thinking Sphinx" group. > > > To post to this group, send email to [email protected]. > > > To unsubscribe from this group, send email to > > > [email protected]. > > > For more options, visit this group at > > > http://groups.google.com/group/thinking-sphinx?hl=en. > > > > > > > -- > > You received this message because you are subscribed to the Google Groups > > "Thinking Sphinx" group. > > To post to this group, send email to [email protected]. > > To unsubscribe from this group, send email to > > [email protected]. > > For more options, visit this group at > > http://groups.google.com/group/thinking-sphinx?hl=en. > > > > > > > > -- > > You received this message because you are subscribed to the Google Groups > > "Thinking Sphinx" group. > > To post to this group, send email to [email protected]. > > To unsubscribe from this group, send email to > > [email protected]. > > For more options, visit this group at > > http://groups.google.com/group/thinking-sphinx?hl=en. > > -- > You received this message because you are subscribed to the Google Groups > "Thinking Sphinx" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/thinking-sphinx?hl=en. > > > > -- > You received this message because you are subscribed to the Google Groups > "Thinking Sphinx" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/thinking-sphinx?hl=en. -- You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/thinking-sphinx?hl=en.
