Re: [ts] Composite primary keys

[Pat Allan]

Hi Jan

At this point, I don't think I'll add the option for a computed document id - mainly due to the fact you're the first person to require this in the life of TS (at least, I can't remember anyone else making this request).

What could be a viable workaround though is to use a single integer column in your model that'll be unique for each record in that model, and tell Sphinx to use that as its primary key with this line in your model:

set_sphinx_primary_key :unique_id_column

Cheers

--
Pat

On 12/10/2012, at 12:34 AM, Jan Bromberger wrote:

> Hi Pat,
>
> when using thinking sphinx with the composite primary keys gem, one gets an exception like this when defining an index:
>
> NoMethodError (undefined method `to_sym' for [:source, :source_id]:CompositePrimaryKeys::CompositeKeys)
>
> I realize that sphinx needs to have an unsigned int as a primary key, but as long as one can compute one with SQL, it's okay. Could you provide a configuration option to compute the id? In my case, I have a finite set of sources - currently three -, so that (source_id * 3) + source would generate an unsigned unique id.
>
> The options would more or less directly be inserted into the sql_query and sql_query_info options of the generated configuration file.
>
> Without going into detail about my case, it would not be feasible for me to migrate to a single column primary key.
>
> Thanks Jan
>
> --
> 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/-/nfoGaH9JTOYJ.
> 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.

[Jan Bromberger]

Hi Pat,

seems nobody else on the interwebs is using composite primary keys. For reference and anyone else trying this:

I created a new BIGINT UNSIGNED column uiid and computed a 60bit hash from my concatenated primary id columns. See: http://greenash.net.au/thoughts/2010/03/generating-unique-integer-ids-from-strings-in-mysql/ but I had to substring 15 instead of 16 hex digits for it to work.

It works fine in rails console, but throws an error when I do the same search over apache/passenger:

riddle (1.5.3) lib/riddle/client/response.rb:25:in `next_int'
riddle (1.5.3) lib/riddle/client.rb:264:in `block (2 levels) in run'
riddle (1.5.3) lib/riddle/client.rb:263:in `each'
riddle (1.5.3) lib/riddle/client.rb:263:in `map'
riddle (1.5.3) lib/riddle/client.rb:263:in `block in run'
riddle (1.5.3) lib/riddle/client.rb:228:in `collect'
riddle (1.5.3) lib/riddle/client.rb:228:in `run'
riddle (1.5.3) lib/riddle/client.rb:338:in `query'
thinking-sphinx (2.0.13) lib/thinking_sphinx/search.rb:429:in `block (2 levels) in populate'
activesupport (3.2.3) lib/active_support/notifications.rb:123:in `block in instrument'
activesupport (3.2.3) lib/active_support/notifications/instrumenter.rb:20:in `instrument'
activesupport (3.2.3) lib/active_support/notifications.rb:123:in `instrument'
thinking-sphinx (2.0.13) lib/thinking_sphinx/search.rb:556:in `log'
thinking-sphinx (2.0.13) lib/thinking_sphinx/search.rb:565:in `log'
thinking-sphinx (2.0.13) lib/thinking_sphinx/search.rb:428:in `block in populate'
thinking-sphinx (2.0.13) lib/thinking_sphinx/search.rb:606:in `call'
thinking-sphinx (2.0.13) lib/thinking_sphinx/search.rb:606:in `retry_on_stale_index'
thinking-sphinx (2.0.13) lib/thinking_sphinx/search.rb:426:in `populate'
thinking-sphinx (2.0.13) lib/thinking_sphinx/search.rb:301:in `total_entries'
app/views/shops/configure.html.erb:26:in `_app_views_shops_configure_html_erb___2617149341727481671_2501897120'

It is fixed by creating an initializer with require 'riddle/2.1.0'.

See your post at: https://github.com/pat/riddle/issues/51 

I don't understand how this works, as I only see a 2.0.1, but it does work.

Thanks for your work and support Pat! Maybe you could see that this extension is loaded automagically.

Kind regards

Jan

[Pat Allan]

Hi Jan

You should be able to specify the version in your config/sphinx.yml file (or a bin_path value) instead of having the manual require - the issue crops up because Passenger doesn't operate with the same PATH values as your own user account, and thus doesn't automatically detect Sphinx.
http://pat.github.com/ts/en/common_issues.html#passenger

But great to hear things are working for you.

Cheers

--
Pat

> To view this discussion on the web visit https://groups.google.com/d/msg/thinking-sphinx/-/lVblr6uvZYMJ.