I have some regular WordPress posts that I need to get from the database by parent category, and order by the results of a custom table in the database that I have made.
The parent category that I want to retrieve posts for (including children) is called explorer with the id of 29.
The custom table is called wp_upvotes. in this table there are a few columns, but the only columns that we care about would probably be id and postID. I want to order the wp_posts by the number of rows that has the postID equal to the wp_post.ID, and if there are no rows in that table for the other posts, then they should be ordered by date at the end. I want the most number of upvotes to the least number of upvotes by date.
The query I have tried is this (it returns only the first post, not all of them):
$catIDs = array(29,30,31,32);
SELECT wp_posts.*, COUNT(wp_upvotes.id) AS upvotes FROM wp_posts
LEFT JOIN wp_upvotes ON (wp_posts.ID = wp_upvotes.postID)
INNER JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_term_taxonomy
ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
AND wp_term_taxonomy.taxonomy = 'category'
AND wp_term_taxonomy.term_id IN (" . implode(',', $catIDs) . "))
AND wp_posts.post_status = 'publish'
ORDER BY upvotes DESC, wp_posts.post_date DESC
When I remove the LEFT JOIN for the wp_upvotes table it returns all of the correct posts.. Why is it only returning one row when I am using a LEFT JOIN?
SELECT wp_posts.* FROM wp_posts
INNER JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_term_taxonomy
ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
AND wp_term_taxonomy.taxonomy = 'category'
AND wp_term_taxonomy.term_id IN (" . implode(',', $catIDs) . "))
AND wp_posts.post_status = 'publish'
ORDER BY wp_posts.post_date DESC
Looks like the main factor was to use
ORDER BY wp_posts.IDfor whatever reason to make it show all of the rows instead of just one.. Odd but here’s my final code to do what I wanted to do above: