Dong3000 Dong3000 - 5 months ago 13
SQL Question

mysql search title, description and multi rows tag (regarding conditions)

I want to realize a search similar to mysql search title, description and multi rows tag.

Here are my tables:

Books:

+----+-----------------------+-------------+
| id | name | description |
+----+-----------------------+-------------+
| 1 | Me Before You | [TEXT] |
| 2 | How To Win Friends... | [TEXT] |
| 3 | The Girl on the Train | [TEXT] |
| 4 | After You | [TEXT] |
| 5 | We Were Liars | [TEXT] |
+----+-----------------------+-------------+


Tags:

+----+-----------------------+
| id | tag |
+----+-----------------------+
| 1 | romance |
| 2 | thriller |
| 3 | fantasy |
| 4 | science fiction |
| 5 | drama |
| 6 | friends |
+----+-----------------------+


Books_tags:

+---------+--------+
| book_id | tag_id |
+---------+--------+
| 1 | 1 |
| 1 | 3 |
| 2 | 3 |
| 3 | 3 |
| 3 | 5 |
| 4 | 1 |
| 4 | 5 |
| 4 | 6 |
| 5 | 2 |
| 5 | 6 |
+---------+--------+


Here are some example searches and the desired results:

'romance' -> books 1, 4
'friends' -> books 2, 4, 5
'friends win' -> books 2
'fantasy' -> books 2, 3
'fantasy train' -> books 3


Before building the SQL query, a function checks every given keyword if it's even a tag at once. My problem is, for example, in this case:


  • Case: 3 /

  • Keywords: friends win /

  • Tags: friends



Query:

SELECT SQL_CALC_FOUND_ROWS
b.id, b.name,
MATCH(b.name) AGAINST('*friends* *win*' IN BOOLEAN MODE) as name_score,
MATCH(t.tag) AGAINST('friends' IN BOOLEAN MODE)as tag_score
FROM
books b
LEFT JOIN
books_tags bt ON bt.book_id = b.id
LEFT JOIN
tags t ON t.id = bt.tag_id
WHERE
MATCH(b.name) AGAINST('*friends win*' IN BOOLEAN MODE)
OR MATCH(t.tag) AGAINST('friends' IN BOOLEAN MODE)
GROUP BY
b.id
ORDER BY
name_score DESC, (tag_score + name_score) DESC


Results:

array (size=3)
0 => string '2' (length=1)
1 => string '4' (length=1)
2 => string '5' (length=1)


In this case, the keyword 'friends' already matches the title, so the condition must be reduced and should not search the tags anymore. How can I solve it?

Answer

Ok, finally I wrote a function, that generates a working query for me. The function is very complex and depends on several user inputs. The solution is to concatenate t.tag and b.name. This is, how my WHERE-condition looks like and it work's fine for me:

... WHERE ap.active='yes'
AND (LOWER(CONCAT_WS(' ', IF(LENGTH(t.tag), t.tag, NULL), IF(LENGTH(b.name), b.name, NULL) )) REGEXP 'friends'
AND LOWER(CONCAT_WS(' ', IF(LENGTH(t.tag), t.tag, NULL), IF(LENGTH(b.name), b.name, NULL) )) REGEXP 'win')
GROUP BY b.id