跨多个相关表的相关性搜索 [英] relevance search across multiple related tables

问题描述

我有一个名为 cards 的表，它有相关表 brigades 和标识符。一张卡可以有多个旅和标识符。我想进行一个单独的搜索，比如'紫色国王'，其中'紫色'是一个旅，'国王'是一个标识符，并且找到具有这些旅和标识符的卡片。对类似问题的回答 https://stackoverflow.com/a/9951200/633513 ，展示了如何全文搜索多个表。我想做同样的事情，除了我只想要相关的比赛。这是可能的吗？

I have a table called cards which has related tables brigades and identifiers. A single card can have multiple brigades and identifiers. I want to take a singe search such as 'purple king' where 'purple' is a brigade and 'king' is an identifier, and find cards with those brigades and identifiers. This answer to a similar question, https://stackoverflow.com/a/9951200/633513, showed how you can fulltext search across multiple tables. I'd like to do the same thing, except i just want related matches. Is this possible?

表结构：

Cards: id as INT, title as VARCHAR(50)
Brigades: id as INT, brigade as VARCHAR(50)
Identifier: id as INT, identifier as VARCHAR(50)

加入表格：

CardBrigades: id as INT, card_id as INT, brigade_id as INT
CardIdentifiers: id as INT, card_id as INT, identifier_id as INT

样本标识符：

Angel
Animal
Archer
Army
Assyrian
Babylonian
Based on Prophecy
Beast
Bows, Arrows, Spears, Javelins and Darts
Canaanite
'Capture' in Special Ability
'Censer' in Title
Chief Priest
Child of Leah
Commander
Connected to David
Connected to Demons
'Covenant' in Title
'David' in Title
'David' in Verse
Deacon
Prince

抽样旅：

Sample Brigades:

None
Black
Brown
Crimson
Gold (Evil)
Gray
Orange
Pale Green
Multi (Evil)
Blue
Gold (Good)
Green
Purple
Red
Silver
Teal
White
Multi (Good)
Multi

推荐答案

基于你发布的链接中的答案，你可以做这样的事情

Based on the answer in the link you posted you could do something like this

SELECT id,SUM(relevance) as total_relevance FROM (
SELECT 
    id, 
    (MATCH(title) AGAINST ('search string')) AS relevance
    FROM Cards
UNION
SELECT 
    Cards.id,
    (MATCH(brigade) AGAINST ('search string')) AS relevance
    FROM Brigades 
    INNER JOIN CardBrigades ON Brigades.id=brigade_id
    INNER JOIN Cards ON card_id=Cards.id 
UNION
SELECT 
    Cards.id,
    (MATCH(identifier) AGAINST ('search string')) AS relevance
    FROM Identifier 
    INNER JOIN CardIdentifier ON Identifier.id=identifier_id
    INNER JOIN Cards on card_id=Cards.id 
) AS combined_search 
GROUP BY id
HAVING total_relevance > 0

我不确定这样做会有多好。您可能更愿意考虑Solr，Lucene或NoSQL存储引擎等其他解决方案。

I'm not sure how well this would perform. You might be better off looking at another solution such as Solr, Lucene or even a NoSQL storage engine.

这篇关于跨多个相关表的相关性搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！