enter image description here

So i’m trying to get all posts from the database and order them but i’m stuck.
I want to order the posts by most liked -> not liked -> most unliked.

here is my query:

 $querystr = "SELECT DISTINCT {$wpdb->prefix}posts.ID, {$wpdb->prefix}posts.post_title, {$wpdb->prefix}posts.post_excerpt, {$wpdb->prefix}posts.guid, {$wpdb->prefix}posts.post_author
            FROM {$wpdb->prefix}posts
            LEFT JOIN {$wpdb->prefix}term_relationships rel ON rel.object_id = {$wpdb->prefix}posts.ID
            LEFT JOIN {$wpdb->prefix}term_taxonomy tax ON tax.term_taxonomy_id = rel.term_taxonomy_id
            LEFT JOIN {$wpdb->prefix}terms t ON t.term_id = tax.term_id
            LEFT JOIN {$wpdb->prefix}wti_like_post lp ON lp.post_id = {$wpdb->prefix}posts.ID
            WHERE t.term_id = " . $_SESSION['pageCategory'] . " 
            AND {$wpdb->prefix}posts.post_type = 'post' 
            AND post_status = 'publish' 
            ORDER BY (lp.value <= 0) ASC, ({$wpdb->prefix}posts.ID != lp.post_id) ASC, (lp.value > 0) ASC
            LIMIT $limit OFFSET $offset
            ";

So far I have tried solutions I found on S.O but nothing seems to work.

I will also mention that alot of the posts I would like to get are not linked to the wti_like_post table.

Here is a screenshot of the wti_like_post table, which links liked/disliked posts to the wordpress posts table in the database.

Any ideas on how I might approach this problem.

Cheers

Read more here: Order by positive/negative values and any other post not found in a table


Solution:

If you know the solution of this issue, please leave us a reply in Comment section, to update the question.


Wordpress related questions and answers: