Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

Using Group by in Full text search

charanvcharanv EntrantCurrent User Role Beginner
Hi All
I am writing a query that would pull out results from a database using a fulltext search. The query goes like this

1.
"select * from tbl_products WHERE MATCH(productname, productkeywords, product_description, productcategory) AGAINST ('"Dragon shoes"')"

The query executes well and I get relevant results. The problem is there are some duplicate products in the table and I dont want to delete them because I make use of them for comparison purposes. However I do not want duplicate entities to show up when searching. Using the query mentioned above I get duplicate products too. In order to get rid of the duplicates I modified the search query as follows:

2.
"select * from tbl_products WHERE MATCH(productname, productkeywords, product_description, productcategory) AGAINST ('"Dragon shoes"') GROUP BY productname"

Here is the problem when I execute the 2nd query I get unique products but the search is not at all relevant. The least irrelevant result shows up first. But when I execute the 1st query the results are relevant but I see duplicate products.

IS THERE A WAY FOR ME TO USE GROUP BY OR SOME OTHER FUNCTION THAT WOULD PULL UP THE RESULTS WITH NO DUPLICATE ENTITIES AND NOT DESTROYING THE RELEVANCE OF SEARCH RESULTS.....

Comments

  • januzijanuzi Advisor Inactive User Role Beginner
    So, You don't use those products ? You could add new column: useable TINYINT.

    Then You could use query:
    SELECT
    *
    FROM
    tbl_products t
    LEFT JOIN
    tbl_products t2 ON t.id = t2.id
    WHERE
    MATCH (t2.productname, t2.productkeywords, t2.product_description, t2.productcategory) AGAINST ('words')
    AND
    t.useable = 1

    (id - primary key)

    That should do it.
  • charanvcharanv Entrant Current User Role Beginner
    Thank you.. This should work...
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.