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.
