WordPress Posts, order by custom table results

I have a blog that I need to re-order based on the number of values in a completely custom table. The reason I am not using meta data is a bit complex, but this is just what I need to do.

I just need to count the number of rows in the table wp_upvotes which have a postID that matches the ID of the WordPress blog post, and order it by most "upvotes" to least. This result should include the WordPress post even if there are no values in the wp_upvotes table.

The query I am trying is this:

$post = $wpdb->get_results("
     SELECT wp_posts.*, COUNT(wp_upvotes.id) AS upvotes
     FROM wp_posts 
     LEFT JOIN wp_upvotes 
               ON wp_posts.ID = wp_upvotes.postID 
               WHERE wp_posts.post_status = 'publish'  
               AND wp_posts.post_type = 'post'
      ORDER BY upvotes DESC, wp_posts.date DESC 
      LIMIT $pageNum, $numToLoad
  ", ARRAY_A);

If you want to ensure that there is a match between each table, you are correct to use a LEFT JOIN - an easy way to think about it is that everything on the "left" (wp_posts) will be included, and things on the "right" (wp_upvotes) will be included if they have a match, otherwise null. Just using JOIN will ensure that rows from both tables will only be shown if there is a match.

My guess is that you need to include a GROUP BY p.ID to have each upvotes value specific to a particular post.

As a note, you also have an error using wp_posts.date instead of wp_posts.post_date;

It's also a good idea to use the $wpdb-posts and $wpdb-prefix properties in case you want to use this somewhere with a database prefix that is not wp_.

If you just want to see the results of the data in wp_posts you can just run a database query with an ORDER BY and return the columns, or if you want to use the WordPress filters (on things like the_title() and the_content() you can pass the post IDs into a WP_Query with the post_id__in and orderby=post__in arguments - you would need to reference back the $upvotes value by ID however.

global $wpdb;
$sql = <<<SQL;
    SELECT p.*, COUNT(v.id) as upvotes
    FROM {$wpdb->posts} p
    JOIN {$wpdb->prefix}upvotes v
      ON p.ID = v.postID
    WHERE 
      p.posts_status = 'publish'
      AND p.post_type = 'post'
    GROUP BY p.ID
    ORDER BY upvotes DESC, p.post_date DESC
    LIMIT $pageNum, $numToLoad
SQL;

// use ARRAY_A to access as $row['column'] instead of $row->column
$rows = $wpdb->get_results( $sql ); 
foreach ( $rows as $row ){
    $content = $row->post_content;
    $upvotes = $row->upvotes;
}