vendredi 30 octobre 2015

Trying to use UNION on two random SELECT provides same result

I am trying to get 5 random listings related to a category in a directory website in Wordpress.

However the sticky ones have to be shown first always. I have this query that if I remove the UNION and make the queries separately works giving me the randomized sticky listings and then the other query the randomized non sticky listings.

But when I join them the same order is applied always, so they are not randomized anymore.

(SELECT p.ID,p.post_title, pm.meta_value as level 
FROM wp_posts p 
LEFT JOIN wp_term_relationships tr ON p.ID=tr.object_id 
LEFT JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id=tt.term_taxonomy_id 
LEFT JOIN wp_terms t ON t.term_id=tt.term_taxonomy_id 
LEFT JOIN wp_postmeta pm ON p.ID=pm.post_id 
WHERE tt.taxonomy LIKE ('wpbdp_category') AND t.name LIKE('Acupuncture') AND pm.meta_key LIKE ('_wpbdp[sticky]') AND pm.meta_value LIKE ('sticky') 
ORDER BY  RAND() ) 
UNION
(
SELECT pp.ID,pp.post_title, '' as level 
FROM wp_posts pp 
LEFT JOIN wp_term_relationships ptr ON pp.ID=ptr.object_id 
LEFT JOIN wp_term_taxonomy ptt ON ptr.term_taxonomy_id=ptt.term_taxonomy_id 
LEFT JOIN wp_terms pt ON pt.term_id=ptt.term_taxonomy_id 
WHERE ptt.taxonomy LIKE ('wpbdp_category') AND pt.name LIKE('Acupuncture')  
ORDER BY  RAND()
)
LIMIT 5

Any ideas on what I might be doing wrong?




Aucun commentaire:

Enregistrer un commentaire