WordPress帖子，按自定义表格结果排序 [英] 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.

我只需要计算表wp_upvotes中具有与WordPress博客文章的ID相匹配的postID的行数，并按大多数赞"进行排序.即使wp_upvotes表中没有值，此结果也应包括WordPress帖子.

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);  

推荐答案

如果要确保每个表之间都匹配，则使用LEFT JOIN是正确的-考虑它的一种简单方法是如果包含匹配项，则将包括左侧"(wp_posts)上的所有，如果包含匹配项，则将包含右侧"(wp_upvotes)上的所有内容，否则为null.仅使用JOIN可以确保只有匹配时才显示两个表中的行.

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.

我的猜测是，您需要包括一个GROUP BY p.ID，以使每个upvotes值都特定于特定帖子.

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

请注意，使用wp_posts.date而不是wp_posts.post_date也会出错；

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

如果您想在数据库前缀不是wp_的地方使用$wpdb-posts和$wpdb-prefix属性，这也是一个好主意.

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_.

如果您只想查看wp_posts中的数据结果，则可以使用ORDER BY运行数据库查询并返回列，或者如果您想使用WordPress过滤器(在和the_content()，您可以使用post_id__in和orderby=post__in参数将帖子ID传递到WP_Query中-但是，您需要按ID引用$upvotes值.

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;
}

这篇关于WordPress帖子，按自定义表格结果排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！