
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