How to add ActiveRecord like conditions in our search

[Oscar Del Ben]

Hello, suppose I want to search for an user with login foo but with
created_at since 1 month ago. I would like to do something like:

User.search(:conditions => { :login => 'foo' }, :xxx => ['created_at
> ?', 1.month.ago])

Is there a way to do that without filtering the result later with ruby?

[Pat Allan]

Hi Oscar

You'll want to have login as a field and created_at as an attribute in
your define_index block:

define_index do
# ...
indexes login
has created_at
end

And then you can search like so:

User.search :conditions => {:login => 'foo'}, :with => {:created_at =>
(1.month.ago..Time.now)}

Cheers

--
Pat

[Oscar Del Ben]

Actually, I can't see how to add a > to the condition (created_at >
1.month.ago)

[Pat Allan]

Sphinx doesn't support greater than or less than comparisons - but it
does accept arrays and ranges - so what I'm doing in my example is
setting a range between 1 month ago and the current time.

--
Pat

[Henrik Nyh]

If I recall correctly, I had an issue with time range filters and
Ultrasphinx. No idea how Thinking Sphinx handles it, but mentioning it
as something to watch out for.

This is how I described the issue back then:
http://rubyforge.org/forum/message.php?msg_id=50481

(Ultra)sphinx assumed the times in the database were in the same time
zone as MySQL was configured for (typically machine-local time). It
did not care that Rails was configured to treat database dates as UTC.
Note that MySQL doesn't store time zones in the date columns, and
they're not (time zone agnostic) timestamps, so there must be some
external definition of the time zone.

So if I created a record at 10 am local time (Sweden), it might be
stored in the database as 8 am (UTC). Since Rails was configured to
treat database times as UTC, it knows this means 8 am UTC. But since
MySQL is configured for machine-local time, outside ActiveRecord it
thinks it means 8 am Swedish time.

Ultrasphinx would make the same misassumption, and so filtering could
be off by a few hours.

Again, haven't looked at how Thinking Sphinx deals with this, but it's
something to be aware of. So either make sure MySQL and Rails are
configured for the same database time zone, or add offsets to time
filters.

My workaround was to do
UTC_OFFSET = Time.now.utc_offset
in an initializer (before changing ENV['TZ'], if you do that) and then
modifying the values in the range by that.

[Pat Allan]

Well, there's another future feature - smarter date handling.

Added to my task list.

--
Pat

[Oscar Del Ben]

Ok it would be great to support > operator because of the particular
case of > Time.now where we can't use ranges.
Thanks for helping

[Pat Allan]

Oscar: you'll need to pass on that feature request to the Sphinx team,
as it's not something Thinking Sphinx can do until Sphinx itself
supports it.

Sorry

--
Pat

[Henrik Nyh]

Just to be clear, whereas Sphinx doesn't support the > operator in
conditions, you can still fake it with ranges:

Time.now..Time.mktime(2038)

2038 is the last year Ruby's Time class supports. I'm not sure what
limitations MySQL and Sphinx have in that respect. If you need a
higher end date and they support it, I'm sure you could use the actual
timestamps instead of Times.

[Oscar Del Ben]

Yeah, it's right. Thanks