:source => :query and delta indexing

[Greg DeVore]

We just added an with a :source => :query. This is greatly simplified
but it is something like this:

class Lesson < ActiveRecord::Base
define_index do
indexes content
has 'SELECT `lessons`.`id` * 1 + 0 AS `id`, GROUP_CONCAT(DISTINCT
`spaces`.`id` SEPARATOR \',\') AS `space_ids` FROM lessons (a bunch of
joins) GROUP BY lessons.id ORDER BY NULL',
:as => :admin_space_ids,
:source => :query
end
end

This works great for the normal indexing. But problems come up with
delta indexing. When the delta indexing happens the entire query above
is run. Basically, instead of updating the index for just the lesson
that was saved, it is returning the spaces.id for all lessons in the
database.

Looking at the .conf file it looks like for the standard SQL the
following is appended when the conf file is generated:

WHERE `lessons`.`id` >= $start AND `lessons`.`id` <= $end AND
`lessons`.`delta` = 1 GROUP BY `lessons`.`id` ORDER BY NULL

This sql is NOT added to the end of a sql_attr_multi attribute with
a :source of :query.

Am I doing something wrong or should ts be adding this sql to the end
of the sql_attr_multi definition?

Thanks for any help you can offer.

[Pat Allan]

Hi Greg

You're not doing anything wrong in particular, it's just that Thinking
Sphinx isn't flexible enough to add that WHERE clause to manually
defined SQL MVA's. You're welcome to try and patch it, but manual SQL
MVA's are temperamental at best (considering you need to add the
offset yourself), hence why it's not been something I've looked into
thus far.

--
Pat

[Greg DeVore]

I forked and pushed a patch that does what I need it to, just in case
you are interested. This basically adds a fourth source type
- :delta_enabled_query and tacks on the necessary delta index sql on
the end. Doesn't deal with offsets at all but seems to work for our
situation.

http://github.com/bluemango/thinking-sphinx

Thanks,
Greg