Missing CRC32 support on Heroku shared database

Roger Kind Kristiansen's Avatar

Roger Kind Kristiansen

18 Aug, 2011 05:20 AM

I've set up Flying Sphinx, and I think I have just one outstanding issue.

I have a string that I use as filter, and up until now I've CRC-ed that value when indexing, using Postgres' CRC32() function (as documented at http://freelancing-god.github.com/ts/en/common_issues.html#string_f...). This function is seemingly not a built in function, as Heroku does not offer it on its shared databases, which gives me the following error by email when trying to index:

ERROR: index 'doctor_core': sql_range_query: ERROR:  function crc32(character varying) does not exist
LINE 1: ...nicipalities"."name", '') AS "municipality_sort", CRC32(sex)...

My indexing is as follows:

define_index do

indexes :firstname,  :sortable => true
indexes :lastname,   :sortable => true
indexes :sex,        :sortable => true
indexes practice(:name),    :as => :practice_name,    :sortable => true
indexes practice(:address), :as => :practice_address, :sortable => true

# Added to this model after denormalization
indexes county(:name),        :as => :county,       :sortable => true
indexes municipality(:name),  :as => :municipality, :sortable => true

# Can only filter on integers, thus CRC32-ing
has "CRC32(sex)", :as => :sex_filter, :type => :integer, :sortable => true

end

And as you can see, the field I am sorting on is SEX. This field is simply a string, with "M" for male and "F" for female.

Some pointers on how to work around this would be much appreciated. :-)

  1. 1 Posted by Roger Kind Kris... on 16 Sep, 2011 09:47 PM

    Roger Kind Kristiansen's Avatar

    I just started looking into this, but haven't been able to find a solution yet.

    These docs just state that you are looking at workarounds. Has any progress been made?

    Cheers,
    Roger

  2. Support Staff 2 Posted by Pat Allan on 17 Sep, 2011 04:32 AM

    Pat Allan's Avatar

    Hi Roger

    Sorry, I'd replied to this a month ago, but it seems like Tender hid the response from anyone who isn't considered 'staff' (at least, I think it's done that - let me know if I'm wrong). Here's my response from last time:

    I think the best approach here is to use a CASE statement and a constant:

    Gender = {'F' => 0, 'M' => 1}
    
    has "CASE sex WHEN 'F' THEN #{Gender['F']} WHEN 'M' THEN #{Gender['M']} END", 
      :as => :sex_filter, :type => :integer
    

    No need to add the :sortable flag - all attributes are sortable.

    And then in searching, something like this:

    :with => {:sex_filter => Doctor::Gender[params[:sex]]}
    

    Let me know how you go.

    Pat

  3. 3 Posted by Roger Kind Kris... on 17 Sep, 2011 08:31 AM

    Roger Kind Kristiansen's Avatar

    Thanks again Pat, that works like a charm. :)

  4. Support Staff 4 Posted by Pat Allan on 17 Sep, 2011 08:34 AM

    Pat Allan's Avatar

    Great to hear - sorry it took a while for me to realise the original response wasn't visible.

  5. Pat Allan closed this discussion on 17 Sep, 2011 08:34 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