Kevin J Kevin J - 2 months ago 5
MySQL Question

Count the number of times an index exists using a query containing multiple EXIST() statements

My query gets the results of these products based on if they exist in a separate table index. I am trying to get a count of all the instances where they exist so I can ORDER the results by relevance. Everything I try seems to return the variable @priority as 0. Any ideas?

Maybe it is better to use join statements?

Thank you for your help. Here is my MySQL query:

SELECT `products` . * , @priority
FROM `products`
LEFT JOIN productstypes_index ON productstypes_index.product_id = products.id
WHERE (

EXISTS (

SELECT *
FROM `productstypes_index`
WHERE `productstypes_index`.`product_id` = `products`.`id`
AND `productstypes_index`.`_type_id` = '1'
)
AND (
(
(

EXISTS (

SELECT @priority := COUNT( * )
FROM `producthashtags_index`
WHERE `producthashtags_index`.`product_id` = `products`.`id`
AND `producthashtags_index`.`producthashtag_id` = '43'
)
)
AND (

EXISTS (

SELECT @priority := COUNT( * )
FROM `producthashtags_index`
WHERE `producthashtags_index`.`product_id` = `products`.`id`
AND `producthashtags_index`.`producthashtag_id` = '11'
)
)
)
)
)
ORDER BY `updated_at` DESC;

Answer

You could do without those exists, and without variables. Also, a left join has no sense if you have an exists condition on the joined table. Then you might as well do the more efficient inner join and put the extra type condition in the join condition.

The priority can be calculated by a count over the hash tags, but only those with id in ('43', '11').

SELECT     products.* 
           count(distinct producthashtags_index.producthashtag_id) priority
FROM       products
INNER JOIN productstypes_index 
        ON productstypes_index.product_id = products.id
       AND productstypes_index._type_id =  '1'
INNER JOIN producthashtags_index 
        ON producthashtags_index.product_id = products.id
       AND producthashtags_index.producthashtag_id in ('43', '11')
GROUP BY   products.id  
ORDER BY   updated_at DESC;