Hello Temen, I had the same issue and found a solution. The purists would probably find it a little messy but it worked for me:
http://groups.google.com/group/thinking-sphinx/browse_thread/thread/703b1b039abebd58/effa60171cb190d9?hl=en&lnk=gst&q=Kevin#effa60171cb190d9 Regards, Kevin. On Jun 13, 7:35 pm, temen <[email protected]> wrote: > 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 -~----------~----~----~----~------~----~------~--~---
