relevance search across multiple related tables

Question

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://mcmap.net/q/858744/-mysql-fulltext-search-relevance-across-multiple-tables, 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?

Table Structures:

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

Join Tables:

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

Sample Identifiers:

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

Cheryllches

That table structure doesn't actually show the relation. Does Cards have columns for Brigades.id and Identifier.id values? Or does Bridgades of a Cards.id value? Something seems not right. – Gallopade

Can you provide some sample data? Do the VARCHAR fields just include 'purple' etc or might they also include 'purple heart' that you also want to match on. – Avraham

@Gallopade sorry, I had forgot about the join tables. I updated the question with them. – Cheryllches

@Avraham I updated the question with sample data – Cheryllches

Answer 1

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

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.

Avraham

I was able to get this, but how do i find one that are related? For example, the search above using this query would give me kings that aren't purple – Cheryllches

@Cheryllches I'm not sure you can achieve what you want perfectly in MySQL. You might be better off using a dedicated string search engine. – Avraham

what about finding how many hits on a single card id? then you could order by how many hits to find the mos relevant right? – Cheryllches

@Cheryllches I guess you could switch the HAVING clause to the sub-queries and then do a count. That might work. – Avraham

Answer 2

If you just want the related (relevant?) results you can use FULLTEXT search in BOOLEAN MODE as follows:

select 
    identifier, brigade, P.id as identifier_id, B.id as brigade_id, 
    match(identifier)  against ('purple prince') +
    match(brigade)     against ('purple prince')   as score
from Identifier P, Brigade B
where 
    match(identifier)  against ('purple prince' IN BOOLEAN MODE) and 
    match(brigade)     against ('purple prince' IN BOOLEAN MODE)
order by -score
;

(To make it simple I just show the the FULLTEXT search part of the query and omit the join with the Card* table)

Sapor