在 Thinking Sphinx 中对 has_many 关系使用 crc32 调整 [英] Using crc32 tweak on has_many relations in Thinking Sphinx

问题描述

其实很奇怪.我有两个彼此有 has_many 关系的模型，这是我的模型

It's weird actually. I have two models that have has_many relation each other, here are my models

#model city
class City < ActiveRecord::Base
belong_to :state
end

#model state
class State < ActiveRecord::Base
has_many :city
end

我有状态索引

ThinkingSphinx::Index.define 'state', :with => :active_record do
  indexes state_name, :sortable => true

  #here is the problem
  has "CRC32(cities.city_name)", :as => :city_name, :type => :integer
end

我想使用 city_name 作为过滤器.我上面的代码不起作用，我在运行时收到一条错误消息

I want to use city_name as a filter. My code above doesn't work and i got an error message when run

rake ts:index

这里是错误信息

ERROR: index 'state_core': sql_range_query: Unknown column 'cities.city_name' in 'field list'

但是，当我将 city_name 放在如下所示的索引块中时，索引器运行良好！

but, when i put city_name in indexes block like below, the indexer runs well!

ThinkingSphinx::Index.define 'state', :with => :active_record do
  indexes state_name, :sortable => true
  indexes cities.city_name
  has "CRC32(cities.city_name)", :as => :city_name, :type => :integer
end

有什么建议吗?

推荐答案

Thinking Sphinx 无法判断您是否指的是 SQL 片段中的关联表 - 因此在您的第一个示例中，没有任何迹象表明它需要加入城市.

Thinking Sphinx can't tell if you're referring to association tables within SQL snippets - so in your first example, there's nothing indicating that it needs to join on cities.

索引定义中的 join 方法就是为此目的而存在的 - 因此，请尝试以下操作:

The join method within an index definition exists for this very purpose - so, try the following:

ThinkingSphinx::Index.define 'state', :with => :active_record do
  indexes state_name, :sortable => true

  has "CRC32(cities.city_name)", :as => :city_name, :type => :integer

  join cities
end

但是，有几点值得注意:首先，您可能还需要将 cities.city_name 添加到 GROUP BY 子句中，因为它不是任何聚合的一部分值:

However, it's worth noting a few things: firstly, you may also need to add cities.city_name to the GROUP BY clause, since it's not part of any aggregate values:

# within index definition
group_by 'cities.city_name

而且:您的 State 模型有许多城市，而不仅仅是一个，因此它实际上应该聚合成一组整数值，而不仅仅是一个.这意味着您不需要 group_by 调用，但您需要自己添加聚合行为.这取决于您使用的是 PostgreSQL 还是 MySQL:

But also: your State model has many cities, not just one, so it should actually be aggregated into a set of integer values, not just one. This means you don't need the group_by call, but you do need to add the aggregate behaviour yourself. This is done differently depending on whether you're using PostgreSQL or MySQL:

# PostgreSQL
has "array_to_string(array_agg(crc32(cities.name)), ',')",
  :as => :city_names, :type => :integer, :multi => true

# MySQL
has "GROUP_CONCAT(CRC32(cities.name) SEPARATOR ',')",
  :as => :city_names, :type => :integer, :multi => true

CRC32 不是 PostgreSQL 中的原生函数，因此您可能需要自己添加它.在 v3 之前的 Thinking Sphinx 为您做到了这一点，但我已经重写了它，因此不再需要 CRC32 函数.这主要是因为 CRC32 会导致冲突，并且无法逆转，因此这是一个不优雅且不完美的解决方案.因此，我认为使用字段进行字符串比较更好，但这取决于您是否在您的应用中首选.

CRC32 is not a native function in PostgreSQL, and so you may need to add it yourself. Thinking Sphinx prior to v3 did this for you, but I've rewritten it so the CRC32 function is no longer required. This is largely due to the fact that CRC32 can result in collisions, and it can't be reversed, and so it's an inelegant and imperfect solution. Hence, I think using fields for string comparison is better, but it's up to you for whether this is preferred in your app.

我会推荐这种方法:

ThinkingSphinx::Index.define :state, :with => :active_record do
  indexes state_name, :sortable => true

  has cities.id, :as => :city_ids
end

city = City.find_by_name('Melbourne')
State.search :with => {:city_ids => city.id}

它准确而优雅.

这篇关于在 Thinking Sphinx 中对 has_many 关系使用 crc32 调整的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！