sphinx指数与多对多关系

I am trying to set up a Sphinx index with a basic many-to-many relation between artworks and genres:

我正在尝试建立一个Sphinx索引，其中包含艺术作品和流派之间的基本多对多关系：

artworks
---------------
id
title
description

genres
---------------
id
name

artwork_genres
---------------
artworks_id
genres_id

In my sphinx config file I have something like

在我的sphinx配置文件中，我有类似的东西

source src_artwork {
    ...
    sql_query    = SELECT id, title, description FROM artworks
    sql_attr_multi = uint tag from query; SELECT id,name FROM genres
}

This is from the docs, as far as I can understand, on multi-valued attributes and sql_attr_multi

据我所知，这是关于多值属性和sql_attr_multi的文档

But obviously there is no mention of the tie table in there and I can't understand how that is brought into the config. I'd simply like for a search on "Impressionism" to result in artworks belonging to that genre (weighted as appropriate if the term is seen in the other fields)

但显然没有提到那里的领带表，我无法理解它是如何进入配置的。我只是想搜索一下“印象派”，以产生属于该类型的艺术作品（如果在其他领域中看到该术语，则适当加权）

1 个解决方案

#1

I would consider ignoring the attributes feature in this case. The simplest way to create a genre field by which to search artworks is to "de-normalise" the genres table into the sql_query.

在这种情况下，我会考虑忽略属性功能。创建用于搜索艺术品的类型字段的最简单方法是将类型表“去规范化”到sql_query中。

In the FROM clause of your SQL query, you would JOIN the genres table to the artworks via the linking table. In the SELECT clause, you can then GROUP_CONCAT genres.name into a column, which becomes a Sphinx field to search on.

在SQL查询的FROM子句中，您可以通过链接表将类型表加入到艺术作品中。在SELECT子句中，您可以将GROUP_CONCAT genres.name放入一列，该列成为要搜索的Sphinx字段。

Your sql_query might look like this:

您的sql_query可能如下所示：

source src_artwork {
        ...
    sql_query    = SELECT a.id, a.title, a.description, GROUP_CONCAT( DISTINCT g.name SEPARATOR ' ') AS genre \
        FROM artworks AS a \
        LEFT JOIN artwork_genres AS ag ON ag.artworks_id = a.id \ 
        LEFT JOIN genres AS g ON g.id = ag.genres_id
        GROUP BY a.id;
}

Then a sphinx search for artworks looking for "impressionism" in the @genre field will return the "row".

然后，狮身人面像搜索在@genre字段中寻找“印象派”的艺术品将返回“行”。

#1