SELECT CASE WHEN p.post_title LIKE '%pirate%' THEN 0 ELSE 900
END AS priority,
p.ID as mID,
p.post_title as mpost_title,
inumber.meta_value AS mitem_number,
p.*
FROM wp_posts p
LEFT JOIN wp_postmeta inumber ON inumber.post_id = p.id AND inumber.meta_key = 'item_number'
WHERE p.post_type = 'product'
AND p.post_status = 'publish'
AND (
(
p.post_title LIKE '%pirate%' OR lower(inumber.meta_value) LIKE '%pirate%' OR p.id in (SELECT wp_posts.ID FROM wp_posts
LEFT JOIN wp_term_relationships
ON wp_posts.ID = wp_term_relationships.object_ID
LEFT JOIN wp_terms
ON wp_terms.term_id = wp_term_relationships.term_taxonomy_id
WHERE wp_terms.name = 'pirate') )
)
ORDER BY priority ASC, p.ID desc
