0

Context

I just added the FULLTEXT index onto a few columns between two separate tables. The actual fulltext implementation works fine; however, when I use the MATCH() AGAINST() in the WHERE clause, my other specification in the WHERE clause fails to work. My query goes like so:

SELECT 
articles.article_id, 
articles.title, 
articles.article, 
articles.idUsers,
articles.published,
users.uidUsers, 
MATCH(articles.title, articles.article) AGAINST (? IN BOOLEAN MODE) AS content, 
MATCH(users.uidUsers) AGAINST (? IN BOOLEAN MODE) AS user, 
articlecoverimages.image, 
profileimages.image AS profileImage 
FROM 
articles 
JOIN users 
JOIN articlecoverimages 
JOIN profileimages 
ON users.idUsers = articlecoverimages.idUsers 
AND 
articles.article_id = articlecoverimages.article_id 
AND 
profileimages.idUsers = users.idUsers 
WHERE //start of the code in question
MATCH(articles.title, articles.article) AGAINST (? IN BOOLEAN MODE) 
OR 
MATCH(users.uidUsers) AGAINST (? IN BOOLEAN MODE)
AND
articles.published = ? //end of the code in question
ORDER BY
(content + user) DESC 
LIMIT 10

This query works fine except for one thing: despite that fact that I specified that the variable used with

...
AND
articles.published = ? 
...

is ? = "yes", the query still returns rows where articles.published is equal to "no". Everything else about the query is fine and works well; the articles are ordered by relevance of text and username.

Question

Is there a rule where you can't specify anything in the WHERE clause when using FULLTEXT or something of that sort? If not, is there anything visibly wrong with my code?

What I've Tried

I have already tried changing the order of the articles.published = ? to before and after the MATCH specifications in the WHERE clause to no avail. Let me know if there's something else I could put on here to make this easier to understand or if you have any questions. Thanks.

Carson D
  • 81
  • 13
  • You need `()` around `MATCH(articles.title, articles.article) AGAINST (? IN BOOLEAN MODE) OR MATCH(users.uidUsers) AGAINST (? IN BOOLEAN MODE)` to make the logic in your `WHERE` clause correct. – Nick Jun 27 '20 at 07:16
  • @Nick that makes sense. It worked perfectly. Thanks. – Carson D Jun 27 '20 at 20:35

0 Answers0