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]<thinking-sphinx%[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]<thinking-sphinx%[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]<thinking-sphinx%[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]<thinking-sphinx%[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.
