Reputation: 608
i have a table of products. to perform a search am using 3 different queries to get most matching results. but am getting few bummers eg pagination thus the need to combine my 3 queries to one and still give same results.
here are my queries and an explanation of what each does
Query 1: this query gets exact matches to the search string
SELECT *,
MATCH (product_name) AGAINST ('k' IN BOOLEAN MODE) as distance,
MATCH (product_description) AGAINST ('k' IN BOOLEAN MODE) as distance2
FROM products
INNER JOIN shops ON shops.shop_id = products.shop_id
WHERE MATCH (product_name) AGAINST ('k')
OR MATCH (product_description) AGAINST ('k')
ORDER BY distance DESC, distance2 DESC
LIMIT 0, 9;
Query 2: this query gets all results where the search string is part of string minus the results of query one then adds results to array
SELECT *
FROM products
INNER JOIN shops ON shops.shop_id = products.shop_id
WHERE product_name LIKE '%k%'
ORDER BY INSTR(product_name,'k'), product_name
LIMIT 0, 9;
Query 3: this query gets closest match to the search string minus results of query 1 and query 2 adds results to array
SELECT *,
fuzzy_substring( 'k', product_name ) as distance,
fuzzy_substring( 'k', product_description ) as distance2
FROM products
INNER JOIN shops ON shops.shop_id = products.shop_id
WHERE (fuzzy_substring( 'k', product_name ) < 3
OR fuzzy_substring( 'k', product_description ) < 3)
AND product_id NOT IN ('pr933j4', 'Posr49il', 'p4021dd')
order by distance ASC, distance2 ASC
LIMIT 0, 9;
i cant figure out how to combine this three queries to one any suggestions to the right direction are highly appreciated. NOTE 'k' is the string being searched
Upvotes: 0
Views: 32
Reputation: 608
UNION worked for me
SELECT * FROM
( (SELECT j1.* FROM products AS j1 LEFT JOIN shops AS z1 ON z1.shop_id =
j1.shop_id WHERE MATCH (product_name) AGAINST ('k') OR MATCH
(product_description) AGAINST ('k') ORDER BY MATCH (product_name)
AGAINST ('k' IN BOOLEAN MODE) DESC, MATCH (product_description) AGAINST
('k' IN BOOLEAN MODE) DESC ) s1)
UNION
SELECT * FROM
( ( SELECT j2.* FROM products AS j2 LEFT JOIN shops AS z2 ON
z2.shop_id = j2.shop_id WHERE product_name LIKE '%k%' ORDER BY
INSTR(product_name,'k'), product_name ) s2)
UNION
SELECT * FROM
( ( SELECT j3.* FROM products as j3 LEFT JOIN shops as z3 ON z3.shop_id =
j3.shop_id WHERE (fuzzy_substring( 'k', product_name ) < 3 OR
fuzzy_substring( 'k', product_description ) < 3) AND product_id NOT IN
('pr933j4', 'Posr49il', 'p4021dd') order by fuzzy_substring( 'k',
product_name ) ASC, fuzzy_substring( 'k', product_description ) ASC )
s3)
Upvotes: 1