Re: filtering on associated attributes in postgres

[Josh Sharpe]

Okay, it's worth noting this has nothing do with the join/association - some problem with the string attribute.

  define_index do

    indexes :name, :sortable => true

    has "CRC32(city)", :as => 'city', :type => :string

    set_property :delta => :datetime, :threshold => 3.minutes

  end

Then, ts:rebuild results in this:

using config file '/Users/jsharpe/app/config/development.sphinx.conf'...

indexing index 'competitor_page_core'...

ERROR: index 'competitor_page_core': sql_range_query: ERROR:  invalid input syntax for integer: ""

LINE 1: ..."name", '') AS "name_sort", COALESCE(CRC32(city), '') AS "ci...

                                                             ^

 (DSN=pgsql://jsharpe:***@localhost:5432/app_development).

total 0 docs, 0 bytes

total 10.019 sec, 0 bytes/sec, 0.00 docs/sec

On Friday, November 2, 2012 2:09:27 AM UTC-4, Josh Sharpe wrote:

I seem to be unable to filter on associated, string, attributes using postgres:

class CompetitorPage < ActiveRecord::Base

  belongs_to :state

  define_index do

    indexes :name, :sortable => true

    join state

    has "CRC32(states.abbreviation)", :as => 'state_name', :type => :string

    group_by "states.abbreviation"

    set_property :delta => :datetime, :threshold => 3.minutes

  end

end

Getting this from ts:rebuild

01:49:16 sphinx.1       | ERROR: index 'competitor_page_core': sql_range_query: ERROR:  invalid input syntax for integer: ""

01:49:16 sphinx.1       | LINE 1: ..."name_sort", COALESCE(CRC32(states.abbreviation), '') AS "st...

... which is postgres chocking out on the mixed data types being feed to COALESCE (CRC(...) being an integer and '' being a string)

So I changed my index to be this:

  define_index do

    indexes :name, :sortable => true

    join state

    has "CAST(CRC32(states.abbreviation) AS text)", :as => 'state_name', :type => :string

    group_by "states.abbreviation"

    set_property :delta => :datetime, :threshold => 3.minutes

  end

And that allows ts:rebuild to complete, but then i can't search on state_name:

 ?> CompetitorPage.search(:with => {'state_name' => "NY"})

  Sphinx Query (3.0ms)  

  Sphinx  Found 0 results

=> []

And they do exist:

 >> CompetitorPage.all.map{|c| c.state.abbreviation}.group_by{|a| a}["NY"].length

=> 3

What'd I miss?

[Pat Allan]

Hi Josh

You'll need to change a couple of things. Firstly, the attribute type is an integer - the CRC32'd value of the city (in your second example). And secondly, you'll need to filter by the CRC32'd value itself. So:

has "CRC32(city)", :as => :city, :type => :integer

And then:

Model.search :with => {:city => 'Melbourne'.to_crc32}

That should do the trick.

--
Pat

> --
> You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group.
> To view this discussion on the web visit https://groups.google.com/d/msg/thinking-sphinx/-/8QWwKNB0H00J.
> To post to this group, send email to thinkin...@googlegroups.com.
> To unsubscribe from this group, send email to thinking-sphi...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/thinking-sphinx?hl=en.