doupi7619 2015-03-18 11:12
浏览 65
已采纳

按数组大小查询WordPress

I have a website which allows users to 'like' posts. A post's likes are stored as an array of user IDs in a postmeta field called "likers".

I'm having trouble displaying posts in order of most liked. Ideally I could query the size of the likers array and order by that value, but I can't find a way to do it using WP_Query.

At the moment I'm querying for every post's ID and likers field, then in a foreach loop I'm counting the size of the likers array, sorting by that value and using a custom loop to display each post. Except with this method I can't use the standard WP pagination function.

Has anyone got a better solution?

Here's the current code:

global $wpdb;
$posts = $wpdb->get_results("SELECT post_id, meta_value FROM $wpdb->postmeta WHERE meta_key = 'likers'", 'ARRAY_A');
if (!$posts) {
    return "No posts found.";
} else {

    // add them to an array with the size of their 'likers' field
    foreach($posts as &$post) {
        $post['likers'] = count(unserialize($post['meta_value']));
    }

    // sort array by likes
    uasort($posts, function ($i, $j) {
        $a = $i['likers'];
        $b = $j['likers'];
        if ($a == $b) return 0;
        elseif ($a < $b) return 1;
        else return -1;
    });

    // now display the posts...
    foreach($posts as $post) {
  • 写回答

2条回答 默认 最新

  • duan1930 2015-03-18 16:03
    关注

    I'm not really sure how you store the number of likes in your database. If you store the data as an comma separated string <userid>,<userid>,<userid> you could write a query like this to order your posts by the number of likes:

    global $wpdb;
    $posts = $wpdb->get_results("SELECT ID, 
                                 post_title, 
                                 LENGTH(m.meta_value) -  
                                 LENGTH(REPLACE(m.meta_value, ',', '')) + 1 likes 
                                 FROM {$wpdb->posts} p
                                 LEFT JOIN {$wpdb->postmeta} m ON m.post_ID = p.ID &&
                                                             m.meta_key = 'likes' 
                                 ORDER BY likes DESC", OBJECT);
    

    The above calculates the number of likes by subtracting the length of the meta_value with the length of the meta_value without commas and then adding 1.

    You should be able to use the same technique in cases where your meta value contains a serialized array. Notice that each value need to be stored as a string for this to work:

    // serialized array of user ids
    a:4:{i:0;s:4:"1714";i:1;s:4:"1333";i:2;s:4:"1332";i:3;s:2:"38";}
    
    $posts = $wpdb->get_results("SELECT ID, 
                                 post_title, 
                                 (LENGTH(m.meta_value) -  
                                 LENGTH(REPLACE(m.meta_value, '\"', '')) - 2) * 0.5 likes 
                                 FROM {$wpdb->posts} p
                                 LEFT JOIN {$wpdb->postmeta} m ON m.post_ID = p.ID &&
                                                             m.meta_key = 'likes' 
                                 ORDER BY likes DESC", OBJECT);       
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 winform的chart曲线生成时有凸起
  • ¥15 msix packaging tool打包问题
  • ¥15 finalshell节点的搭建代码和那个端口代码教程
  • ¥15 用hfss做微带贴片阵列天线的时候分析设置有问题
  • ¥15 Centos / PETSc / PETGEM
  • ¥15 centos7.9 IPv6端口telnet和端口监控问题
  • ¥20 完全没有学习过GAN,看了CSDN的一篇文章,里面有代码但是完全不知道如何操作
  • ¥15 使用ue5插件narrative时如何切换关卡也保存叙事任务记录
  • ¥20 海浪数据 南海地区海况数据,波浪数据
  • ¥20 软件测试决策法疑问求解答