delta indexes and sorting by attributes

[agib]

(in 3e5fc7370b93e543ffd6ca2e0f240bec47baf4b9)

So I'm a bit unclear on how sphinx and TS handle delta indexes. I have
a couple of issues:

1. My string type attributes don't seem to work for filtering with
delta indexes but if I do something like has "contexts.state_status =
'published'", :as => :published, :type => :boolean it does.
2. true isn't converted into a MySQL boolean correctly.
3. Integer type attributes don't seem to work for sorting search
results that come from a delta index. Including :order in the search
params throws out all the delta results which would otherwise return
without an :order key.

Please see the code below for details.

Thanks,
Aaron

class Context < ActiveRecord::Base

define_index do
indexes :name, :sortable => true
indexes :desc

has "contexts.state_status = 'published'", :as
=> :published, :type => :boolean

has state_status, :type => :string
has votes_count
set_property :delta => true
end
end

# I have a few contexts called pub 1, pub 2, pub 3, and pub 4... they
are all state_status = 'published'

Context.search(:conditions => {
:state_status => 'published'
})

#=> []

Context.search(:conditions => {
:published => true
})

#=> error => cannot convert true to float

Context.search(:conditions => {
:published => 1
})

#=> [<Context ...>, <Context ...>, <Context ...>, <Context ...>]

Context.search(:order => 'votes_count DESC')

#=> []

# exec: rake ts:stop && rake ts:index && rake ts:start

Context.search(:order => 'votes_count DESC')

#=> [<Context ...>, <Context ...>, <Context ...>, <Context ...>]

[Pat Allan]

Hi Aaron

String filters and attributes don't work - this seems to be a common
misconception at the moment. I've had a workaround suggested, and
there is a patch out there in the wild, but I've not had time to
review it properly and merge it in.

This thread has a bit more information, might be helpful in
understanding:
http://groups.google.com/group/thinking-sphinx/browse_thread/thread/7e8bb5ec3afc25f7

As for delta indexes and sorting, yup, that's a limitation in Sphinx.
It calculates the ordinal values separately between indexes. There's
one way I've thought of fixing it, but Sphinx doesn't allow live
updates to the ordinal attributes, just normal ints, booleans and
timestamps.

Sorry I'm not providing you with much in the way of good news.

--
Pat

[James Healy]

Pat Allan wrote:
> As for delta indexes and sorting, yup, that's a limitation in Sphinx.
> It calculates the ordinal values separately between indexes. There's
> one way I've thought of fixing it, but Sphinx doesn't allow live
> updates to the ordinal attributes, just normal ints, booleans and
> timestamps.

I wrestled with this issue for a while and came up with a solution that
will suit some people, depending on the use case. Pat was kind enough to
merge it, so if it suits you it's available to use.

The search() method accepts a sql_order option, which will order the
matching results based on a field in your database instead of a field
in the sphinx index. If you're trying to sort by a string and you're
getting delta index records followed by main index records, this may
suit you.

An example from my codebase:

Edition.search("some keywords", :sql_order => "`title`", :limit => 1000)

The one caveat is that the ordering is done on a per page basis. For a
HTML interface with pagination this behaviour is probably not what you
want.

In my case the search is for an order form with up to 1000 products, so
I'm not using pagination and the ordering is appropriate.

-- James Healy <jimmy-at-deefa-dot-com> Wed, 11 Mar 2009 16:17:50 +1100