MySQL Forums
Forum List  »  Full-Text Search

Re: MariaDB and MATCH AGAINST Query
Posted by: SGK Stmk
Date: August 11, 2016 06:34AM

Just in the case anyone needs it sometime, this is my solution for now. Had to change things mentioned in my last posting and to work with subqueries. As a bonus I have added a bit of ranking...

SELECT DISTINCT id, title, shtext, lotext,
((case when title like '%[searchterm]%' then 1 else 0 end) +
(case when shtext like '%[searchterm]%' then 1 else 0 end) +
(case when lotext like '%[searchterm]%' then 1 else 0 end)) as priority
FROM b_entry
WHERE (title LIKE '%[searchterm]%' OR shtext LIKE '%[searchterm]%' OR lotext LIKE '%[searchterm]%')
AND archive = 0
AND (
id IN (SELECT b.id FROM b_entry b, b_category c, b_entry2user eu WHERE b.categoryId = 4 AND b.id = eu.entryId AND eu.userId = [id])
OR id IN (SELECT b.id FROM b_entry b, b_category c, b_entry2user eu WHERE b.categoryId = c.id AND c.parentId = 3 AND b.id = eu.entryId AND eu.userId = [id])
OR id IN (SELECT b.id FROM b_entry b, b_category c WHERE b.categoryId = c.id AND c.parentId = 2)
OR id IN (SELECT b.id FROM b_entry b, b_category c WHERE b.categoryId = c.id AND c.parentId = 1)
) ORDER BY priority DESC, edited DESC, stored DESC;

Options: ReplyQuote


Subject
Views
Written By
Posted
2789
August 01, 2016 08:30AM
1015
August 02, 2016 06:16PM
893
August 11, 2016 12:45AM
Re: MariaDB and MATCH AGAINST Query
926
August 11, 2016 06:34AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.