Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I have wp e-commerce plugin installed and custom building custom global search

My query looks like this

SELECT * FROM aka_v_posts 
LEFT JOIN aka_v_postmeta 
ON aka_v_posts.ID=aka_v_postmeta.post_id 
AND ((aka_v_postmeta.meta_key='_wpsc_special_price' 
      AND aka_v_postmeta.meta_value > '0') 
    OR aka_v_postmeta.meta_key='_wpsc_price')
WHERE ( post_title LIKE '%32%' OR post_content LIKE '%32%' )
AND ( post_status = 'publish' OR post_status = 'private' ) 
AND post_type = 'wpsc-product' 
AND aka_v_postmeta.meta_value BETWEEN 0 AND 999999 
ORDER BY aka_v_posts.post_date DESC, aka_v_postmeta.meta_value + 0 ASC

The thing I'm doing here, joining prices where if product is on sale, get post with sale meta_value, else get with meta_key "_wpsc_price"

Of course this part is not working

AND ((aka_v_postmeta.meta_key='_wpsc_special_price' 
      AND aka_v_postmeta.meta_value > '0') 
    OR aka_v_postmeta.meta_key='_wpsc_price')

It queries products right when product is not on sale, but when it is, it displays it twice, one with "_wpsc_price" meta_key and other with "_wpsc_special_price" Because 1 OR 1 is 1. So this logic is totally messy.

I did a little research with my little of knolidge of mysql and found CASE WHEN condition but never found a way to apply it for current issue.

Any suggestions? Of course doing it without CASE WHEN would be better

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

Thank's everyone for your attention.

If anybody will need to query database with two postmeta, here is the code

SELECT p.* 
, CASE WHEN m1.meta_value=0 OR m1.meta_value IS NULL 
THEN m2.meta_value 
ELSE m1.meta_value 
END meta_value 
FROM aka_v_posts p 
LEFT JOIN aka_v_postmeta m1 ON p.id=m1.post_id AND m1.meta_key='_wpsc_special_price' 
LEFT JOIN aka_v_postmeta m2 ON p.id=m2.post_id AND m2.meta_key='_wpsc_price' 
WHERE post_title LIKE '%32%'
AND post_type = 'wpsc-product' 
ORDER BY aka_v_posts.post_date DESC

The thing to keep in mind (at least I was doing it wrong) is that you need to check for meta_values before the FROM keyword, to select those meta_values.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.