Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I'm a bit new to php and am having some trouble combining the functionality of two custom queries in Wordpress in order to (1) get pagination to work properly, and (2) sort my posts according to a voting plugin.

(1) The following is a custom query, which is working with pagination, but does not sort my posts according to votes (set in a voting plugin).

$total = "
SELECT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
WHERE wposts.ID         =   wpostmeta.post_id
AND wposts.post_status  =   'publish'
AND wposts.post_type IN     ('youtube', 'vimeo')
GROUP BY wposts.ID
";

$totalposts = $wpdb->get_results($total, OBJECT);
$ppp = intval(get_query_var('posts_per_page'));
$wp_query->found_posts = count($totalposts);
$wp_query->max_num_pages = ceil($wp_query->found_posts / $ppp); 
$on_page = intval(get_query_var('paged'));  

if($on_page == 0){ $on_page = 1; }  
$offset = ($on_page-1) * $ppp;

$wp_query->request = "
SELECT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
WHERE wposts.ID         =   wpostmeta.post_id
AND wposts.post_status  =   'publish'
AND wposts.post_type IN     ('youtube', 'vimeo')
GROUP BY wposts.ID
LIMIT $ppp OFFSET $offset
";

$pageposts = $wpdb->get_results($wp_query->request, OBJECT);

(2) The following is another custom query. When this query is combined with the function in #3, it does sort my posts according to votes. However it does not work with pagination.

$enddate = date('Y-m-d');
$increment_date = strtotime("+1 day", strtotime( $enddate ));
$enddate = date("Y-m-d", $increment_date);

$increment_date = strtotime("-1 month", strtotime( $enddate ));
$startdate = date("Y-m-d", $increment_date);

$query = "
SELECT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
WHERE wposts.ID         = wpostmeta.post_id
AND wposts.post_status  = 'publish'
AND wposts.post_type IN ('youtube', 'vimeo')
AND post_date           >= '$startdate'
AND post_date           <= '$enddate'
GROUP BY wposts.ID
";

(3) And the function for sort according to votes is...

function ShowPostByVotes($query) {

global $wpdb, $voteiu_databasetable;
$upperlimit = get_option('voteiu_limit');
if ($upperlimit == '') { $upperlimit = 100; } $lowerlimit = 0;

$votesarray = array();

    $pageposts = $wpdb->get_results($query, OBJECT);

$query_limited = $query." LIMIT ".$lowerlimit.", ".$upperlimit;

$posttablecontents = mysql_query( $query_limited );

while ($row = mysql_fetch_array($posttablecontents)) {
    $post_id = $row['ID'];
    $vote_array = GetVotes($post_id, "array");
    array_push($votesarray, array(GetVotes($post_id)));
}
array_multisort($votesarray, SORT_DESC, $pageposts);
$output = $pageposts;
return $output;
}

My difficulty is that I can't seem to create a query/function that both sorts my posts by votes, and also works with pagination...I apologize I know it's a bit of a big question, but I've been struggling with this for a few days with no luck and can't seem to get anywhere.

share|improve this question

migrated from stackoverflow.com May 11 '11 at 0:56

This question came from our site for professional and enthusiast programmers.

    
Thanks Mario, I'll move it over tomorrow if no answer. –  Nick Budden May 9 '11 at 20:56

2 Answers 2

You should probably be using the WP_Query object and have it order your posts by a meta field:

http://codex.wordpress.org/Function_Reference/WP_Query#Parameters

Doing so will handle sorting and pagination out of the box.

share|improve this answer
    
Thanks Denis. This was the initial plan, but where we are querying the posts so much it really quickens things up to have the votes stored in their own table, and join that to our posts. It's a bit slow to query by the custom meta. But thanks again. –  Nick Budden May 9 '11 at 20:56
    
There are join filters that you can use for this. See the source code of the WP_Query object. –  Denis de Bernardy May 10 '11 at 0:42
    
Thanks Denis I'll have a look and see if I can figure it out...it would be great to be able to stick with WP_Query. –  Nick Budden May 10 '11 at 1:19

Why not just orderby your ShowPostByVotes query in the (1) custom query? Are they accessible to each other?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.