Using Group by in Full text search

Polyglot Projects Sphinx & Full-Text Search
1

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

“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:

“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…

2

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.

3

Thank you… This should work…