How to properly get popular Posts by multiple Values

Question

I am trying to display Popular Posts on my website, selected by a mixture of Views and Comment Count. Since views are always way more than Comments, I am multiplying the Comment Count by X, but it doesnt seem to work how I was hoping it would. The Query Result is just ordered by the Meta Value "Views". I assume this is because I would need to calculate the Sum of Views + Comment Count * X and then order by it... But I am not sure how to accomplish this with using only get_posts(). Can somebody point me in the correct direction? :)

This is my Code

$home_top_news = get_posts(
    array(
        'post_type' => array('post','reviews'),
        'post_status' => 'publish',
        'numberposts' => '15',
        'meta_key' => 'views',
        'orderby' => array(
            'meta_value_num' => 'DESC',
            '(comment_count * 10)' => 'DESC',
        ),
        'date_query' => array(
            array(
                'after' => '12 days ago'
            )
        )
    )
);

Answer 1

I believe you will not be able to do so just with the WP Query/get_posts interface. There is probably an way using a custom MySQL query, but there is another approach you can try. Since you have a custom meta views, you can try create a new custom meta popularity, which will be the sum of views and the comment count. You can hook this in the same views hook, and in the comment_post hook. After that, just query the popularity meta.

Answer 2

You'll need to use the $wpdb object for this.

The SQL would look something like this (completely untested and you need to add the time condition)

SELECT ID, post_date, post_title, post_content, (meta_value + (comment_count * 10)) AS rank
FROM $wpdb->posts
INNER JOIN $wpdb->postmeta pm
    ON (ID = pm.post_id AND pm.meta_key = 'views')
WHERE (post_type = 'post' OR post_type = 'reviews')
    AND post_status = 'publish'
ORDER BY rank DESC
LIMIT 0,15;