In the meantime I found a great article after 2 hours of googling
Thanks, 3stripe. This article proved exactly what I needed. I wanted to be able to sort my posts, not categories. Eventually I came up with the idea to add a custom field named ‘volgnummer’ and sort the posts by the value of that field.
My query now looks like this:
$pageposts = $wpdb->get_results("
SELECT
wposts.*
FROM
$wpdb->posts wposts,
$wpdb->postmeta wpostmeta
WHERE
wposts.ID = wpostmeta.post_id
AND
wpostmeta.meta_key = 'volgnummer'
ORDER BY
wpostmeta.meta_value ASC", OBJECT);
if ($pageposts) : foreach ($pageposts as $post): setup_postdata($post);
// Yada yada yada
endforeach;
endif
Hopefully this is of any use.
Thread Starter
Xander
(@xander)
Thanks for posting up that link! I found a way to get this working quite well.
I adapted this solution to do something else entirely: select posts from a given category based on two seperate meta field values. I have a list of reviews on my site that I wanted to display by “Year” AND “Rating” but the nature of the postmeta table made this seem impossible… eventually, I put this query together:
SELECT wposts.* FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta, $wpdb->post2cat wpost2cat WHERE wposts.ID = wpostmeta.post_id AND wposts.ID = wpost2cat.post_id AND wpost2cat.category_id = '6' AND wposts.ID IN (SELECT wposts.ID FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta WHERE wposts.ID = wpostmeta.post_id AND wpostmeta.meta_key = 'Rating' AND wpostmeta.meta_value > '7') AND wpostmeta.meta_key = 'Year' AND wpostmeta.meta_value >= '2002' AND wposts.post_status = 'publish' AND wposts.post_date < NOW() ORDER BY wposts.post_title ASC
The subquery allowed me to select posts that were for releases that came out after the year 2002 with a rating higher than 7, for example. Maybe this technique would be of use to someone else out there. Anyway, thanks for the tip on this one!
