php - 任何组合全文查询结果的方法

Question

我有一个全文 MySQL 查询，它运行得相当好——除了在某些情况下我最终会得到重复的结果。我有几种类型的数据需要搜索:建筑物、套房、 Assets 等，每个数据都在自己的表中。每种类型的数据还可以具有用户定义的自定义字段，其数据存储在不同的表中。这是我的结构。

CREATE TABLE `buildings` (
 `bid` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
 `status` enum('active','inactive') COLLATE utf8_unicode_ci NOT NULL,
 `source` enum('feed','manual') COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`bid`),
 FULLTEXT KEY `textsearch` (`name`)
)

CREATE TABLE `customfields` (
 `cfid` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
 `datatype` enum('integer','date','smtext','lgtext','numeric','enum','linked') COLLATE utf8_unicode_ci NOT NULL,
 `options` longtext COLLATE utf8_unicode_ci,
 `did` int(11) NOT NULL COMMENT 'Datasheet ID',
 PRIMARY KEY (`cfid`),
 KEY `did` (`did`)
)

CREATE TABLE `customfield_data` (
 `cfdid` int(11) NOT NULL AUTO_INCREMENT,
 `data_int` int(11) DEFAULT NULL,
 `data_date` datetime DEFAULT NULL,
 `data_smtext` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `data_lgtext` longtext COLLATE utf8_unicode_ci,
 `data_numeric` decimal(20,2) DEFAULT NULL,
 `linkid` int(11) DEFAULT NULL COMMENT 'ID value of specific item',
 `cfid` int(11) NOT NULL COMMENT 'Custom field ID',
 PRIMARY KEY (`cfdid`),
 KEY `data_smtext` (`data_smtext`(333)),
 KEY `linkid` (`linkid`),
 KEY `cfid` (`cfid`),
 FULLTEXT KEY `textsearch` (`data_smtext`,`data_lgtext`)
)

因此，建筑物可以有一个自定义字段，将其实际数据存储在 customfield_data 中。自定义字段通过 did(数据集 ID)被识别为特定于建筑物的字段。然后，cfid 字段将 customfield_data 链接到自定义字段，并使用 linkid 字段将数据链接到特定建筑物。

考虑到这一点，这是我的全文查询:

SELECT * FROM (
  SELECT (data_smtext + data_lgtext) AS data, linkid AS idvalue, did, MATCH(data_smtext, data_lgtext) AGAINST (?) AS relevance
  FROM customfield_data
  JOIN customfields ON customfield_data.cfid = customfields.cfid
  WHERE MATCH (data_smtext, data_lgtext) AGAINST (? IN BOOLEAN MODE)
  UNION
  SELECT name AS data, bid AS idvalue, 2 AS did, 3 * (MATCH(name) AGAINST (?)) AS relevance
  FROM buildings
  WHERE status = ? AND MATCH (name) AGAINST (? IN BOOLEAN MODE)
  UNION
  SELECT name AS data, bid AS idvalue, 2 AS did, 100 AS relevance
  FROM buildings
  WHERE bid LIKE ? AND status = ?
  UNION
  )
AS searchresults
WHERE relevance > 0
ORDER BY relevance DESC

正如我之前提到的，这一切都正常。我遇到的问题是，例如，如果一座建筑物名为 123 North Ave Tower，并且其地址(自定义字段)为 123 North Ave，然后我会得到重复的记录——如果我搜索“123 North Ave”，一条记录是姓名，另一条记录是地址。

它们都指向相同的 did(数据集 ID)和 linkid(在本例中是构建 ID)，所以我知道我可以通过在 PHP 中循环生成的数组、查找匹配项来解决我的问题扔掉重复项。但这会给我带来性能损失，也会降低相关性评分的有效性。理想情况下，我希望能够让查询将地址匹配的相关性添加到名称/ID 匹配的相关性，以便为我提供更准确的分数。现在，我将名称匹配乘以 3 倍，并将自定义字段匹配保留为 1 倍，以便更重视更准确的匹配，但将两者相加将是最佳选择。

感谢任何能给我指明正确方向的人!

Answer 1

我没有解决这个具体问题的方法，但我通过安装和配置 Sphinx 并让它完成我的肮脏工作来解决它。