0down VoteFavorite使用此SQL查询按用户帖子收到的投票数（元键）对用户进行排序。
<?php
global $wpdb;
$results = $wpdb->get_results ( "
SELECT u.display_name as name,
( SELECT SUM(pm.meta_value)
FROM wp_posts p, wp_postmeta pm
WHERE p.post_author = u.ID
AND p.post_status LIKE \'publish\'
AND pm.meta_key = \'epicredvote\'
AND p.ID = pm.post_id ) as votes
FROM wp_users u
ORDER BY votes DESC LIMIT 20
" );
foreach ( $results as $result ) {
echo "{$result->name}: {$result->votes} votes<br>";
}
?>
以前从未像这样在wordpress中使用过直接SQL查询，它总是wp循环或其变体。
无论如何，这将显示用户名+他们的分数（总计）。
我想做的是把这限制在一个帖子的日期范围内，比如说一周。
我认为可以使用strotime来实现这一点，但我不确定如何实现。
$date1 = date(\'Y-m-d\', strtotime(\'+1 days\'));
$date2 = date(\'Y-m-d\', strtotime(\'-8 days\'));
那么要在过去7天内收到帖子？在谷歌上搜索了一下，但没找到多少。
非常感谢您的帮助。
编辑：-由于某种原因无法工作的完整代码。
<?php
global $wpdb;
$date2 = date(\'Y-m-d\', strtotime(\'-60 days\'));
$weekly = $wpdb->get_results ( "
SELECT u.display_name as name,
( SELECT
CONCAT_WS(\'/\',
SUM(CASE WHEN p.post_date < \'$date2\' THEN pm.meta_value ELSE 0 END),
SUM(pm.meta_value))
FROM wp_posts p, wp_postmeta pm
WHERE p.post_author = u.ID
AND p.post_status LIKE \'publish\'
AND pm.meta_key = \'epicredvote\'
AND p.ID = pm.post_id ) as votes
FROM wp_users u
ORDER BY votes DESC LIMIT 20
" );
foreach ($weekly as $result) {
$name = $result->name;
list( $weekly_vote, $total_vote ) = explode( \'/\', $result->votes ? $result->votes : \'0/0\' );
// do stuff with $name, $weekly_vote, $total_vote ...eg
echo $name, \'<br>\', \' Weekly Vote: \', $weekly_vote,\'<br>\', \' Total Vote: \', $total_vote, \'<br>\', \'<br>\';
}
?>
最合适的回答,由SO网友:bonger 整理而成
你基本上已经知道答案了，只需添加$date2 到内部sql where子句：
AND p.post_date > \'$date2\'
上周才拿到。要获得特定的一周，请使用
AND p.post_date < \'$date1\' AND p.post_date > \'$date2\'
其中eg
$date1 = date(\'Y-m-d\', strtotime(\'2014-07-04 +1 days\'));
$date2 = date(\'Y-m-d\', strtotime(\'2014-07-04 -8 days\'));
要返回每周/总投票数，可以将测试放入select子句中：
$results = $wpdb->get_results ( "
SELECT u.display_name as name,
( SELECT
CONCAT_WS(\'.\',
SUM(CASE WHEN p.post_date < \'$date1\' AND p.post_date > \'$date2\' THEN pm.meta_value ELSE 0 END),
SUM(pm.meta_value))
FROM wp_posts p, wp_postmeta pm
WHERE p.post_author = u.ID
AND p.post_status LIKE \'publish\'
AND pm.meta_key = \'epicredvote\'
AND p.ID = pm.post_id ) as votes
FROM wp_users u
ORDER BY votes+0 DESC LIMIT 20
" );
然后根据需要输出结果：
foreach ($results as $result) {
$name = $result->name;
list( $weekly_vote, $total_vote ) = explode( \'.\', $result->votes ? $result->votes : \'0.0\' );
// do stuff with $name, $weekly_vote, $total_vote ...eg
echo \'Name: \', $name, \' Weekly Vote: \', $weekly_vote, \' Total Vote: \', $total_vote;
}
