Flying Sphinx: Discussion

Indexing errors

2011-08-16T12:00:07Z

Ah, some of these things might be related to switching from mysql to postgres...

Indexing errors

2011-08-16T12:02:58Z

Yup - if you get stuck with it, I'm happy to provide suggestions - though sharing your define_index block will certainly help with that :)

Indexing errors

2011-08-16T13:17:44Z

Ah, I see, I need to add some group_bys... trying that now ;-)

Indexing errors

2011-08-16T13:30:56Z

Okay, all works now, except this line in the job model's index:

has 'CRC32(country_string)', :as => :country_for_filtering, :type => :integer

'indexing index 'job_core'... ERROR: index 'job_core': sql_range_query: ERROR: function crc32(character varying) does not exist
LINE 1: ..."name", '') AS "job_name_sort", 1 AS "activated", CRC32(coun...

HINT: No function matches the given name and argument types. You might need to add explicit type casts.
(DSN=pgsql://lisuxygdfr:***@127.0.0.1:10323/lisuxygdfr). total 0 docs, 0 bytes
total 0.056 sec, 0 bytes/sec, 0.00 docs/sec
indexing index 'job_delta'...
ERROR: index 'job_delta': sql_range_query: ERROR: function crc32(character varying) does not exist
LINE 1: ..."name", '') AS "job_name_sort", 1 AS "activated", CRC32(coun...

HINT: No function matches the given name and argument types. You might need to add explicit type casts.
(DSN=pgsql://lisuxygdfr:***@127.0.0.1:10323/lisuxygdfr).'

Indexing errors

2011-08-16T13:32:47Z

Hmm I will have to find a workaround for that crc32 thing. However, when I take it out,
I get the following error when searching with thinking sphinx:

Sphinx Sphinx Daemon returned error: no such filter attribute 'user_id'

Here is the user's index definition:

define_index do

indexes(vorname,      :as => :first_name, :sortable => true)
indexes(tags.name,    :as => :tags)
indexes(company.name, :as => :company)
indexes(job.name,     :as => :job)

# only index last name if privacy is turned off
indexes("CASE WHEN privacy_name = 0 THEN '' ELSE nachname END", :as => :last_name, :sortable => true)

# Geo stuff. Sphinx expects radians, we store degrees thus the conversion
has('RADIANS(users.lat)', :as => :lat,  :type => :float)
has('RADIANS(users.lng)', :as => :lng,  :type => :float)

# Index user id so we can exclude myself from search
has(:id, :as => :user_id)

# make sure we only hit activated users in searches (this allows for filtering)
has('1', :as => :activated, :type => :integer)

has "CASE WHEN users.country = 'Deutschland' THEN '0' ELSE CASE WHEN users.country = 'Schweiz' THEN '1' ELSE '2' END END", :as => :country_for_order_de, :type => :integer
has "CASE WHEN users.country = 'Schweiz' THEN '0' ELSE CASE WHEN users.country = 'Deutschland' THEN '1' ELSE '2' END END", :as => :country_for_order_ch, :type => :integer
has "CASE WHEN users.country = 'Österreich' THEN '0' ELSE CASE WHEN users.country = 'Deutschland' THEN '1' ELSE '2' END END", :as => :country_for_order_at, :type => :integer

group_by "users.privacy_name", "users.nachname", "users.lat", "users.lng", 'users.country'

# settings
if Rails.env.production? or Rails.env.staging?
  set_property :delta => FlyingSphinx::DelayedDelta
end
set_property :latitude_attr => 'lat'
set_property :longitue_attr => 'lng'
set_property :enable_star => true
set_property :min_infix_len => 1
set_property :morphology => 'none'

end

Indexing errors

2011-08-16T16:09:44Z

Okay, fixed the last issue. Instead of using crc32 I simply assign an int to each country for filtering.

Indexing errors

2011-08-16T16:10:22Z

Can you close the discussion? I can't.

Indexing errors

2011-08-17T06:03:24Z

Consider it closed - great to hear you got it all sorted.