Indexing errors

Nico's Avatar

Nico

16 Aug, 2011 12:00 PM

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

  1. 1 Posted by Nico on 16 Aug, 2011 12:00 PM

    Nico's Avatar

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

  2. Support Staff 2 Posted by Pat Allan on 16 Aug, 2011 12:02 PM

    Pat Allan's Avatar

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

  3. 3 Posted by Nico on 16 Aug, 2011 01:17 PM

    Nico's Avatar

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

  4. 4 Posted by Nico on 16 Aug, 2011 01:30 PM

    Nico's Avatar

    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).'

  5. 5 Posted by Nico on 16 Aug, 2011 01:32 PM

    Nico's Avatar

    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

  6. 6 Posted by Nico on 16 Aug, 2011 04:09 PM

    Nico's Avatar

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

  7. 7 Posted by Nico on 16 Aug, 2011 04:10 PM

    Nico's Avatar

    Can you close the discussion? I can't.

  8. Support Staff 8 Posted by Pat Allan on 17 Aug, 2011 06:03 AM

    Pat Allan's Avatar

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

  9. Pat Allan closed this discussion on 17 Aug, 2011 06:03 AM.

Comments are currently closed for this discussion. You can start a new one.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac