使用thinking_sphinx搜索并过滤结果 [英] Searching with thinking_sphinx and filtering results

问题描述

我有这样一个场景，我认为它非常基本，但发现我无法真正实现我的需求.这就是为什么我要向thinking_sphinx 的专家提出这个问题.

I have this scenario where I thought it would be pretty basic, but found out that I can't really achieve what I need. This is why I have this question for a thinking_sphinx's expert.

场景是这样的:我需要在公司列表中进行搜索，只返回那些地址(公司可以有很多地址)属于特定城市或根本不属于特定城市的人(我可以这样做)).

The scenario is this: I need do a search within a list of companies and only return those who has an address (there can be many address by company) which belongs to a particular city or none at all (this I can do).

我有以下型号:

class Company < ActiveRecord::Base
    has_many :company_addresses

    define_index
      indexes :name
      indexes :description
      indexes :keywords
    end
end

和

class CompanyAddress < ActiveRecord::Base
end

CompanyAddress 具有 city_id 属性.如果不遍历 sphinx 搜索返回的所有记录，是否有办法更轻松地实现相同的目标?

The CompanyAddress has a city_id property. Without looping through all returned records from a sphinx search, is there a way to achieve the same thing more easily?

我使用的是 Rails 3.0.3 和 thinking_sphinx.

I'm using Rails 3.0.3 and thinking_sphinx.

推荐答案

您需要添加一个指向公司 city_id 值的属性:

You'll want to add an attribute pointing to the city_id values for the company:

has company_addresses.city_id, :as => :city_ids

然后您可以过滤属于特定城市的公司:

And then you can filter on Companies belonging to a specific city:

Company.search 'foo', :with => {:city_ids => @city.id}

如果您想同时匹配特定城市或没有城市，那就有点棘手了，因为属性过滤器的 OR 逻辑充其量只是有点棘手.理想情况下，您想要的是包含 0 或所有城市 ID 的单个属性.这样做取决于您的数据库，因为 MySQL 和 Postgres 功能各不相同.

If you want both matching to a specific city or has no cities, that's a little trickier, as OR logic for attribute filters is more than a little tricky at best. Ideally what you want is a single attribute that contains either 0, or all city ids. Doing this depends on your database, as MySQL and Postgres functions vary.

不过，这是一个粗略的想法 - 这可能适用于 MySQL:

As a rough idea, though - this might work in MySQL:

has "IF(COUNT(city_id) = 0, '0', GROUP_CONCAT(city_id SEPARATOR ',')",
  :as => :city_ids, :type => :multi

Postgres 相当相似，但您可能需要使用 CASE 语句而不是 IF，并且您肯定希望为组使用几个函数串联:

Postgres is reasonably similar, though you may need to use a CASE statement instead of IF, and you'll definitely want to use a couple of functions for the group concatenation:

array_to_string(array_accum(city_id, '0')), ',')

(array_accum 由 Thinking Sphinx 提供，因为在 PostgreSQL 中没有直接等效的 GROUP_CONCAT).

(array_accum is provided by Thinking Sphinx, as there was no direct equivalent of GROUP_CONCAT in PostgreSQL).

无论如何，如果您需要这种方法，并且想通了所有 SQL，那么您的查询将类似于:

Anyway, if you need this approach, and get the SQL all figured out, then your query looks something like:

Company.search 'foo', :with => {:city_ids => [0, @city.id]}

这将匹配 0(表示没有城市)或特定城市.

This will match on either 0 (representing no cities), or the specific city.

最后:如果您不在正常字段和属性中的任何地方引用 company_addresses 关联，则需要强制加入您的 define_index:

Finally: if you don't reference the company_addresses association anywhere in your normal fields and attributes, you'll need to force to join in your define_index:

join company_addresses

希望这能提供足够的线索 - 请随时在此处或在 Google 群组.

Hopefully that provides enough clues - feel free to continue the discussion here or on the Google Group.

这篇关于使用thinking_sphinx搜索并过滤结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！