Custom Query sorting by type?

  • Hi, I’m trying to write a custom query to list posts within the current category and have them sorted by type so that it outputs as such.

    <ul>
<li><h2>Documents</h2><li>
<li>post name</li>
…
<li><h2>Galleries</h2></li>
<li>post name</li>
…
<li><h2>AudioVideo</h2></li>
<li>post name</li>
…
</ul>

    Rather than querying the database for the contents of the post and determining their type there I’ve added a custom key to each post of Document, Gallery, Video and Audio. Video and Audio are combined in the same group on output.

    While I can just about build a simple custom query I can’t get the output to be ordered exactly as I’d want. Could anyone help with this?

    Thanks.

Viewing 4 replies - 1 through 4 (of 4 total)
  • Thread Starter minkowski

    (@minkowski)

    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.

    Thread Starter minkowski

    (@minkowski)

    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.

    Thread Starter minkowski

    (@minkowski)

    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?

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Custom Query sorting by type?’ is closed to new replies.