1

I have 2 tables : Company and category (category table has tags field, I wrote keywords for each category, I used comma(,) as delimiter)

When I search something, it will first search in the company table and then search the query in the tags field.

I wrote the query but I have a problem. When I search "emlak", everything is fine but if I search "emlakçı" it can't find anything. Do I have to enter "emlakçı" as a keyword in tags field or is it possible to solve this with an another way?

Here is my query :

SELECT
    SQL_CALC_FOUND_ROWS company.*,
    MATCH(company.c_title) AGAINST('SEARCH TEXT') as nameScore,
    MATCH(category.tags) AGAINST('SEARCH TEXT') as tagScore
FROM company LEFT JOIN category ON company.c_cat_id = category.c_id
WHERE MATCH(company.c_title) AGAINST('SEARCH TEXT')
OR MATCH(category.tags) AGAINST('SEARCH TEXT')
ORDER BY nameScore DESC
3
  • Welcome to the site, Murat. I retitled your question to put an emphasis on full-text search. You can rollback to your original title, but I felt this was clearer to your problem. Jul 27 '12 at 15:05
  • Why are you using a comma delimited field for tags instead of a tags table and a join table? Comma delimited lists in a RDBMS are possible the worst anti-pattern there is. Jul 30 '12 at 16:51
  • Could this be a character set issue? Do other words with accents such as ç have similar problems?
    – Rick James
    Aug 30 '18 at 23:23
1

Here is your original query

SELECT
    SQL_CALC_FOUND_ROWS company.*,
    MATCH(company.c_title) AGAINST('SEARCH TEXT') as nameScore,
    MATCH(category.tags) AGAINST('SEARCH TEXT') as tagScore
FROM company LEFT JOIN category ON company.c_cat_id = category.c_id
WHERE MATCH(company.c_title) AGAINST('SEARCH TEXT')
OR MATCH(category.tags) AGAINST('SEARCH TEXT')
ORDER BY nameScore DESC

You may want to try running the fulltext searching before doing the LEFT JOIN

SELECT
    SQL_CALC_FOUND_ROWS company.*,
    company.nameScore,category.tagScore
FROM
    (SELECT *,WHERE MATCH(c_title) AGAINST('SEARCH TEXT' IN BOOLEAN MODE) nameScore
    FROM company WHERE MATCH(c_title) AGAINST('SEARCH TEXT' IN BOOLEAN MODE)) company
    LEFT JOIN
    (SELECT *,MATCH(tags) AGAINST('SEARCH TEXT' IN BOOLEAN MODE) as tagScore
    FROM category WHERE MATCH(tags) AGAINST ('SEARCH TEXT' IN BOOLEAN MODE)) category
    ON company.c_cat_id = category.c_id
ORDER BY nameScore DESC;

As for the search string emlakçı, try using a wildcard character. In other words,

MATCH(tags) AGAINST ('emlak*' IN BOOLEAN MODE)

This may pick up more occurrences but should get emlak and similar strings

UPDATE 2012-07-30 18:01 EDT

I don't know your table structure. Assuming name is a field in category, try this:

SELECT
    company.name,category.name,COUNT(1) CategoryCount
FROM
    (SELECT *,WHERE MATCH(c_title) AGAINST('SEARCH TEXT' IN BOOLEAN MODE) nameScore
    FROM company WHERE MATCH(c_title) AGAINST('SEARCH TEXT' IN BOOLEAN MODE)) company
    LEFT JOIN
    (SELECT *,MATCH(tags) AGAINST('SEARCH TEXT' IN BOOLEAN MODE) as tagScore
    FROM category WHERE MATCH(tags) AGAINST ('SEARCH TEXT' IN BOOLEAN MODE)) category
    ON company.c_cat_id = category.c_id
GROUP BY company.name,category.name;
8
  • Thx for the answer but i get an error. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near "WHERE MATCH(c_title) AGAINST('SEARCH TEXT' IN BOOLEAN MORE) nameScore FROM company"
    – Murat
    Jul 27 '12 at 7:33
  • I corrected the syntax. I had BOOLEAN MORE which should have been BOOLEAN MODE Jul 27 '12 at 11:08
  • in addition to this, how can i count how many rows are found in each category
    – Murat
    Jul 30 '12 at 21:48
  • If i use group by , then it returns only one row
    – Murat
    Jul 30 '12 at 22:17
  • Then, the search falls under one category. Is that so ??? Jul 30 '12 at 22:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.