Thanks Pat, I followed you suggestion and it seems to be working now.
I searched for birthdate to age conversion in mysql and found the
following link, and used it to store the ages in sphinx:
http://dev.mysql.com/doc/refman/5.0/en/date-calculations.html
Thanks again Pat, I really appreciate your help!
If anybody is interested, here is the current code for location and
age based search using geokit and Thinking Sphinx:
in model:
define_index do
# fields
...
# attributes
has created_at, updated_at
has "(YEAR(CURDATE())-YEAR(birthdate))- (RIGHT(CURDATE(),5)<RIGHT
(birthdate,5))", :as => :age, :type => :integer
has 'RADIANS(latitude)', :as => :latitude, :type => :float
has 'RADIANS(longitude)', :as => :longitude, :type => :float
end
in controller:
miles_to_meters = (1609.344*(params[:miles]).to_f)
zip = params[:zip_code]
location = GoogleGeocoder.geocode("#{zip}")
if location.success
latitude = ((location.lat)* (Math::PI / 180.0)).to_f
longitude = ((location.lng) * (Math::PI / 180.0)).to_f
end
min_age = params[:min_age].to_i
max_age = params[:max_age].to_i
@user = User.search :geo => [latitude, longitude],
:with => {"@geodist" =>
(0.0..miles_to_meters), :age => min_age..max_age},
:sort => "@geodist ASC"
Pat Allan wrote:
> Oh, I missed that you were using times, not an integer representation
> of years. Sorry.
>
> There's one issue with how you're going about this, and it's not your
> fault, or Thinking Sphinx's. As Greg pointed out, Sphinx expects
> integers for timestamps, and calculates them using UNIX_TIMESTAMP in
> MySQL (and an equivalent approach in PostgreSQL). These values are 0
> for the 1st of January 1970, so anything *before* that gets treated as
> zero as well. Not negative integers.
>
> I've not yet figured out a good way around this yet. It may be worth
> you actually converting the times to ages and storing those as
> integers instead - that is, storing the ages in Sphinx, not the db.
> Here's a complete and utter guess of SQL that should at least give
> some clue to what's needed:
>
> has "YEAR(DATEDIFF(birthdate, NOW()))", :as => :age, :type
> => :integer
>
> Sorry to make things difficult for you.
>
> --
> Pat
>
> On 13/06/2009, at 8:29 AM, temen wrote:
>
> >
> > Greg, Pat, thanks for your replies!
> >
> > Pat, I made the changes you suggested (combined the :with filters) and
> > am still having the same issue. The age range was correct though
> > (e.g.: max_age = 40 and min_age = 20). So
> >
> > :birthdate => 40.years.ago..20.years.ago doesn't work, but
> > :birthdate => 30.years.ago..20.years.ago works.
> >
> > Any ideas?
> > Thanks again.
> >
> > Pat Allan wrote:
> >> I think there's two issues here... your range is around the wrong
> >> way,
> >> and you need both filters in the same option (one is going to
> >> overwrite the other):
> >>
> >> User.search :geo => [latitude, longitude],
> >> :sort => "@geodist ASC"
> >> :with => {
> >> "@geodist" => 0.0..miles_to_meters,
> >> :birthdate => min_age..max_age
> >> }
> >>
> >> Cheers
> >>
> >> --
> >> Pat
> >>
> >> On 12/06/2009, at 7:34 PM, Greg Weber wrote:
> >>
> >>>
> >>> Comparisons can only be done with integers. To convert a datetime
> >>> column to an integer, sphinx will use UNIX_TIMESTAMP, which counts
> >>> up
> >>> from 0, starting around 1970. It seems like the solution to this
> >>> would
> >>> be to cast the datetime as a string instead of a timestamp. Not sure
> >>> how that is accomplished.
> >>>
> >>> On Jun 12, 3:15 pm, temen <[email protected]> wrote:
> >>>> Hi,
> >>>>
> >>>> I am using Thinking Sphinx with geokit for location and age based
> >>>> search, and everything seems to be working until i enter max_age
> >>>> greater or equal to 40, where it then returns an empty set.
> >>>>
> >>>> Thanks.
> >>>>
> >>>> min_age = params[:min_age].to_i.years.ago
> >>>> max_age = params[:max_age].to_i.years.ago
> >>>> ...
> >>>>
> >>>> User.search :geo => [latitude, longitude],
> >>>> :with => {"@geodist" =>
> >>>> (0.0..miles_to_meters)},
> >>>> :sort => "@geodist ASC",
> >>>> :with => { :birthdate => max_age..min_age }
> >>>>
> >
> > >
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
