I am trying to: Perform a custom search and narrow search results.
Link to a page where the issue can be seen: At versteckter Link, if you press the search button (ΑΝΑΖΗΤΗΣΗ) you get 5 results. If now try to narrow down by setting "Οξύτητα" (second slider from top, and last column of results) to say 0:0.4 you expect to find 2 result, instead you find 4 results.
After installing "Query Monitor" plugin I found that the relevant query is:
SELECT wp_posts.*
FROM wp_posts
INNER JOIN wp_postmeta
ON ( wp_posts.ID = wp_postmeta.post_id )
INNER JOIN wp_postmeta AS mt1
ON ( wp_posts.ID = mt1.post_id )
INNER JOIN wp_postmeta AS mt2
ON ( wp_posts.ID = mt2.post_id )
INNER JOIN wp_postmeta AS mt3
ON ( wp_posts.ID = mt3.post_id )
INNER JOIN wp_postmeta AS mt4
ON ( wp_posts.ID = mt4.post_id )
INNER JOIN wp_postmeta AS mt5
ON ( wp_posts.ID = mt5.post_id )
WHERE 1=1
AND wp_posts.ID NOT IN (142)
AND ( ( wp_postmeta.meta_key = 'wpcf-blend'
AND CAST(wp_postmeta.meta_value AS SIGNED) = '0' )
AND ( mt1.meta_key = 'wpcf-quantity'
AND CAST(mt1.meta_value AS SIGNED) BETWEEN '0'
AND '50' )
AND ( mt2.meta_key = 'wpcf-oleicacid'
AND CAST(mt2.meta_value AS SIGNED) BETWEEN '0'
AND '0.4' )
AND ( mt3.meta_key = 'wpcf-fruity'
AND CAST(mt3.meta_value AS SIGNED) BETWEEN '0'
AND '10' )
AND ( mt4.meta_key = 'wpcf-bitter'
AND CAST(mt4.meta_value AS SIGNED) BETWEEN '0'
AND '10' )
AND ( mt5.meta_key = 'wpcf-pungent'
AND CAST(mt5.meta_value AS SIGNED) BETWEEN '0'
AND '10' ) )
AND wp_posts.post_type = 'oliveoil'
AND ((wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private'))
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
I think that the CAST function for example here:
...CAST(mt2.meta_value AS SIGNED)...
rounds-off the number to the nearest integer and that is the culprit.
Should it be something like (?):
...CAST(mt2.meta_value AS DECIMAL)...
Please check it out.
Thank you,
Kostas
