使用thinking_sphinx进行搜索并过滤结果

发布于 2024-10-05 21:52:29 字数 578 浏览 1 评论 0原文

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

场景是这样的：我需要在公司列表中进行搜索，并且只返回那些拥有属于特定城市的地址（公司可以有很多地址）或根本没有地址的公司（我可以这样做）。

我有以下模型：

class Company < ActiveRecord::Base
    has_many :company_addresses

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

CompanyAddress

class CompanyAddress < ActiveRecord::Base
end

有一个 city_id 属性。如果不循环遍历 sphinx 搜索返回的所有记录，有没有办法更轻松地实现相同的目标？

我正在使用 Rails 3.0.3 和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).

I have the following models :

class Company < ActiveRecord::Base
    has_many :company_addresses

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

and

class CompanyAddress < ActiveRecord::Base
end

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?

I'm using Rails 3.0.3 and thinking_sphinx.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

記柔刀 2024-10-12 21:52:29

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

has company_addresses.city_id, :as => :city_ids

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

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

如果您希望两者都匹配特定城市或没有城市，这有点棘手，因为属性过滤器的 OR 逻辑充其量只是有点棘手。理想情况下，您想要的是包含 0 个或所有城市 ID 的单个属性。执行此操作取决于您的数据库，因为 MySQL 和 Postgres 功能有所不同。

不过，作为一个粗略的想法，这可能适用于 MySQL：

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

Postgres 相当相似，尽管您可能需要使用 CASE 语句而不是 IF，而且您肯定会想要使用几个函数进行组串联：

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

（array_accum 由 Thinking Sphinx 提供，因为 PostgreSQL 中没有 GROUP_CONCAT 的直接等效项）。

无论如何，如果您需要这种方法，并弄清楚所有 SQL，那么您的查询将类似于：

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

这将匹配 0（代表没有城市）或特定城市。

最后：如果您没有在普通字段和属性中的任何地方引用company_addresses关联，则需要强制加入您的define_index：

join company_addresses

希望这提供了足够的线索 - 请随意继续讨论此处或Google 网上论坛。

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}

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.

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 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 is provided by Thinking Sphinx, as there was no direct equivalent of GROUP_CONCAT in PostgreSQL).

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]}

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

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: