What I have so far, in standard SQL is:
SELECT *
FROM wp_posts INNER JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
INNER JOIN wp_term_taxonomy ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
INNER JOIN wp_terms ON wp_term_taxonomy.term_id = wp_terms.term_id
WHERE wp_terms.slug = 'my-category-name' AND wp_posts.post_status = 'publish' AND (wp_postmeta.meta_key = 'document' OR wp_postmeta.meta_key = 'gallery' OR wp_postmeta.meta_key = 'video' OR wp_postmeta.meta_key = 'audio')
ORDER BY wp_postmeta.meta_key ASC
From this I get results returned in the meta key order Audio, Document, Gallery and Video.
I can insert the <h2></h2> headings with php when the meta key changes, but I still need the order to have the Audio tag last of all.
I suppose, then, that unless someone has a brilliant solution that is built into WordPress queries that this is a SQL question.
And I seem to have the solution.
The complete WP-ised query is as follows.
$querystr = "
SELECT posts.*
FROM $wpdb->posts posts INNER JOIN $wpdb->term_relationships termrel ON posts.id = termrel.object_id
INNER JOIN $wpdb->postmeta postmeta ON posts.id = postmeta.post_id
INNER JOIN $wpdb->term_taxonomy termtax ON termrel.term_taxonomy_id = termtax.term_taxonomy_id
INNER JOIN $wpdb->terms terms ON termtax.term_id = terms.term_id
WHERE terms.term_id = $cat_ID AND posts.post_status = 'publish' AND (postmeta.meta_key = 'document' OR postmeta.meta_key = 'gallery' OR postmeta.meta_key = 'video' OR postmeta.meta_key = 'audio')
ORDER BY (CASE postmeta.meta_key
WHEN 'Document' THEN 1
WHEN 'Gallery' THEN 2
WHEN 'Video' THEN 3
WHEN 'Audio' THEN 4
ELSE 100 END) ASC
";
The important bit is clearly the ORDER BY (CASE… statement. It is quite self explanatory I think, the WHEN clause places the meta key in the order defined after the THEN and the ELSE 100 END simply places any other returned keys that are not defined at position 100 in the list, ie. last.
The last thing I need to do is grab the meta key in php and print it when it changes from one to another, except in the case of audio. Which is simple enough really.
Hope this helps someone in the future.
A further note, the ELSE 100 is superfluous if I limit my results to
AND (postmeta.meta_key = 'document' OR postmeta.meta_key = 'gallery' OR postmeta.meta_key = 'video' OR postmeta.meta_key = 'audio')
but you get the idea.
Also, I should probably make the key value combination at key = type & value = document, etc.
I can’t wait until different media types are sorted, will save me these headaches.
Interesting. Can you help me with dropdowns that sort posts based on meta key values?
