Home → Problems →

Missing CRC32 support on Heroku shared database

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

   

   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

   

   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

   

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

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

   

   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.