时间戳

首页 > 解决方案 > Sphinx MVA sql_attr_multi 结果总是在 FACET 查询的第一个字段上

sphinx - Sphinx MVA sql_attr_multi 结果总是在 FACET 查询的第一个字段上

问题描述

我正在尝试在 Sphinx 中实现过滤器，以便我可以从同一个连接表中过滤掉两个 MVA 属性的结果（我需要的是双重条件，但因为 MVA 只有 2 个字段..），但无论我做什么，似乎我一直得到相同的结果，因为在多属性的第一个字段上总是有一个匹配项？

也许还有另一种解决方案，但我似乎无法找到我想要的结果，我必须简化 4 个表：产品、类别、价格和位置。

数据设置

下面是数据结构的示例：

PRODUCT
+------+-------------+-----------------+---------------+
|  ID  | CATEGORY_ID | MANUFACTURER_ID |     TITLE     |
+------+-------------+-----------------+---------------+
| 1000 |        1000 |            1000 | Apple iPhone  |
| 1001 |        1000 |            1000 | Apple iPad    |
| 1002 |        1000 |            1000 | Apple iPod    |
| 1003 |        1001 |            1001 | Do not show   |
+------+-------------+-----------------+---------------+

CATEGORY
+------+-------+
|  ID  | TITLE |
+------+-------+
| 1000 | Apple |
| 1001 | Other |
+------+-------+

PRICE
+------+--------+---------+
|  ID  | USERID |  PRICE  |
+------+--------+---------+
| 1000 |   1000 |  359.00 |
| 1001 |   1001 | 1058.30 |
| 1002 |   1002 | 1078.00 |
| 1003 |   1003 | 1160.45 |
| 1004 |   1004 | 1180.00 |
| 1005 |   1000 | 1190.00 |
| 1006 |   1000 |  228.76 |
+------+--------+---------+

POSITION
+------+------------+--------+------+
|  ID  | PRODUCT_ID | USERID | RANK |
+------+------------+--------+------+
| 1000 |       1000 |   1000 |    1 |
| 1001 |       1001 |   1001 |    1 |
| 1002 |       1001 |   1002 |    2 |
| 1003 |       1001 |   1003 |    3 |
| 1004 |       1001 |   1004 |    4 |
| 1005 |       1001 |   1000 |    5 |
| 1006 |       1002 |   1000 |    1 |
+------+------------+--------+------+

狮身人面像设置：

source product
{
        type                            = mysql

        sql_host                        = localhost
        sql_user                        = ...
        sql_pass                        = ...
        sql_db                          = ...

        sql_query_pre                   = SET NAMES utf8
        sql_query                       = SELECT P.ID AS ID, C.ID AS SEARCH_CAT_ID, P.CATEGORY_ID, P.MANUFACTURER_ID, P.TITLE AS TITLE_SORT FROM CATEGORY C, PRODUCT P WHERE P.CATEGORY_ID=C.ID
        sql_attr_uint                   = CATEGORY_ID
        sql_attr_uint                   = MANUFACTURER_ID
        sql_attr_string                 = TITLE_SORT
        sql_attr_multi                  = uint POS_RANK from query; SELECT PRODUCT_ID, RANK FROM POSITION
        sql_attr_multi                  = uint POS_USERID from query; SELECT PRODUCT_ID, USERID FROM POSITION
}
index product
{
        source                   = product
        path                     = ...
        docinfo                  = extern
        min_word_len             = 1
}

MySQL转储：

CREATE TABLE IF NOT EXISTS `CATEGORY` (
  `ID` int(11) NOT NULL,
  `TITLE` varchar(255) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `CATEGORY` (`ID`, `TITLE`) VALUES
(1000, 'Apple'),
(1001, 'Other');

CREATE TABLE IF NOT EXISTS `POSITION` (
  `ID` int(11) NOT NULL,
  `PRODUCT_ID` int(11) NOT NULL,
  `USERID` int(11) NOT NULL,
  `RANK` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `USERID` (`USERID`),
  KEY `PRODUCT_ID` (`PRODUCT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `POSITION` (`ID`, `PRODUCT_ID`, `USERID`, `RANK`) VALUES
(1000, 1000, 1000, 1),
(1001, 1001, 1001, 1),
(1002, 1001, 1002, 2),
(1003, 1001, 1003, 3),
(1004, 1001, 1004, 4),
(1005, 1001, 1000, 5),
(1006, 1002, 1000, 1);

CREATE TABLE IF NOT EXISTS `PRICE` (
  `ID` int(11) NOT NULL,
  `USERID` int(11) NOT NULL,
  `PRICE` decimal(9,2) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `USERID` (`USERID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `PRICE` (`ID`, `USERID`, `PRICE`) VALUES
(1000, 1000, '359.00'),
(1001, 1001, '1058.30'),
(1002, 1002, '1078.00'),
(1003, 1003, '1160.45'),
(1004, 1004, '1180.00'),
(1005, 1000, '1190.00'),
(1006, 1000, '228.76');

CREATE TABLE IF NOT EXISTS `PRODUCT` (
  `ID` int(11) NOT NULL,
  `CATEGORY_ID` int(11) NOT NULL,
  `MANUFACTURER_ID` int(11) NOT NULL,
  `TITLE` varchar(255) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `CATEGORY_ID` (`CATEGORY_ID`),
  KEY `MANUFACTURER_ID` (`MANUFACTURER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `PRODUCT` (`ID`, `CATEGORY_ID`, `MANUFACTURER_ID`, `TITLE`) VALUES
(1000, 1000, 1000, 'Apple iPhone'),
(1001, 1000, 1000, 'Apple iPad'),
(1002, 1000, 1000, 'Apple iPod'),
(1003, 1001, 1001, 'Do not show');

ALTER TABLE `POSITION`
  ADD CONSTRAINT `POSITION_ibfk_1` FOREIGN KEY (`PRODUCT_ID`) REFERENCES `POSITION` (`ID`) ON DELETE CASCADE;

ALTER TABLE `PRODUCT`
  ADD CONSTRAINT `PRODUCT_ibfk_1` FOREIGN KEY (`CATEGORY_ID`) REFERENCES `CATEGORY` (`ID`) ON DELETE CASCADE;

测试 1

过滤器 USERID 的 SphinxQL 查询：

SELECT ID FROM product WHERE MATCH('1000') AND POS_USERID IN (1000) ORDER BY WEIGHT() DESC LIMIT 0,20 FACET POS_RANK LIMIT 5;

USERID 的结果（这次第一组似乎没问题，第二组返回所有内容）：

+------+
|  id  |
+------+
| 1000 |
| 1001 |
| 1002 |
+------+

+----------+----------+
| pos_rank | count(*) |
+----------+----------+
|        5 |        1 |
|        4 |        1 |
|        3 |        1 |
|        2 |        1 |
|        1 |        3 |
+----------+----------+

我所期望的：

测试 2

过滤器 USERID 1000 和 RANK 5 的 SphinxQL 查询：

SELECT ID FROM product WHERE MATCH('1000') AND POS_USERID IN (1000) AND POS_RANK IN (5) ORDER BY WEIGHT() DESC LIMIT 0,20 FACET POS_RANK LIMIT 5;

USERID 和 RANK 的结果（这次第一组似乎没问题，第二组返回所有内容）：

+------+
|  id  |
+------+
| 1001 |
+------+

+----------+----------+
| pos_rank | count(*) |
+----------+----------+
|        5 |        1 |
|        4 |        1 |
|        3 |        1 |
|        2 |        1 |
|        1 |        1 |
+----------+----------+

我所期望的：

测试 3

过滤器 USERID 1000 和 RANK 4 的 SphinxQL 查询：

SELECT ID FROM product WHERE MATCH('1000') AND POS_USERID IN (1000) AND POS_RANK IN (4) ORDER BY WEIGHT() DESC LIMIT 0,20 FACET POS_RANK LIMIT 5;

USERID 和 RANK 的结果（第一个返回产品，第二个返回所有内容）：

+------+
|  id  |
+------+
| 1001 |
+------+

+----------+----------+
| pos_rank | count(*) |
+----------+----------+
|        5 |        1 |
|        4 |        1 |
|        3 |        1 |
|        2 |        1 |
|        1 |        1 |
+----------+----------+

我所期望的：

我希望你们能理解我想要达到的目标并能帮助我吗？

标签： sphinxfacetsphinxql

解决方案

推荐阅读