dannjoroge
dannjoroge

Reputation: 608

Combining Mysql sub queries

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

Answers (1)

dannjoroge
dannjoroge

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

Related Questions