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

图片转代码服务由CSDN问答提供 功能建议

我有一个允许用户“喜欢”帖子的网站。 帖子的喜欢被存储为一个名为“likers”的postmeta字段中的用户ID数组。

我在按照最喜欢的顺序显示帖子时遇到问题。 理想情况下,我可以查询likers数组的大小并按该值排序,但我找不到使用WP_Query的方法。

目前我正在查询每一个 post的ID和likers字段,然后在foreach循环中我计算likers数组的大小,按该值排序并使用自定义循环显示每个帖子。 除了这个方法,我不能使用标准的WP分页功能。

有没有人有更好的解决方案?

这是当前代码:< / p>

  global $ wpdb; 
 $ posts = $ wpdb-&gt; get_results(“SELECT post_id,meta_value FROM $ wpdb-&gt; postmeta WHERE meta_key ='likers'”,'  ARRAY_A'); 
if(!$ posts){
返回“找不到帖子。”; 
}其他{
 
 //将它们添加到一个大小与'likers'字段相关的数组中 ($ posts as&amp; $ post){
 $ post ['likers'] = count(unserialize($ post ['meta_value'])); 
} 
 
 //按喜欢排序数组
 uasort  ($ posts,function($ i,$ j){
 $ a = $ i ['likers']; 
 $ b = $ j ['likers']; 
 if($ a == $ b) 返回0; 
 elseif($ a&lt; $ b)返回1; 
否则返回-1; 
}); 
 
 //现在显示帖子... 
 foreach($ posts as $ 邮寄){
   
 
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

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);       
    
    打赏 评论
  • dongzuan4491 2015-05-20 16:36

    I am making a liking plugin for wordpress and ran into this same issue. I ended up approaching it as you explained in a comment which was to just get the number after the first 'a:' in the serialized array. To do this, I used the mysql SUBSTR function.

    SELECT ID, post_title, SUBSTR(m.meta_value,3,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
    
    打赏 评论

相关推荐 更多相似问题