MySQL ORDER BY来自另一个表的“count（*）和2个其他参数”

Question

I have been struggling with this problem for about month.. Have been searching and reading many posts, but still can't figure out, how to make this work..

Basically: I got 2 database tables fun_posts and fun_post_upvotes And I want to

SELECT * 
FROM fun_posts 
ORDER BY (HOTTEST POSTS(MOST UPVOTED THIS WEEK))

This is my latest code, that won't work

SELECT * 
FROM fun_posts 
ORDER BY (SELECT count(*), image_id, date 
          FROM fun_post_upvotes 
          GROUP BY image_id 
          ORDER BY DATE(date) > (NOW() - INTERVAL 7 DAY) DESC, 
                   count(*) DESC, 
                   date DESC)

If I divide this line into 2 different SELECT functions, they work. I can select simple posts and I can select upvotes count ordered like I want.

But If I make them into one line like that, I get following error:

#1241 - Operand should contain 1 column(s)

EDIT NR 1:

fun_posts table fun_post_upvotes table

Problem with Answer that I checked:

Here, look how posts are ordered in my select function. (It selects like I want) 10->134->132->2->13

And here with given code (It selects image, but not in that order) 10->122->39->8->110

Answer 1

You can use a join to do this

SELECT fp.*, fpu.`cnt`
FROM fun_posts fp
LEFT JOIN ( SELECT image_id, COUNT(*) AS cnt
       FROM fun_post_upvotes
       WHERE `date` > (NOW() - INTERVAL 7 day)
       GROUP BY image_id
     ) fpu ON ( fpu.image_id = fp.id )
ORDER BY fpu.cnt DESC, fp.`date` DESC, fp.`id` DESC;

It selects a list from fun_post_upvotes grouped by image_id and counts the amount of rows. That list is returned to the main query and matches (LEFT JOIN) on fp.id. The query will first show the item with the most upvotes in the past 7 days, than the least. If no upvotes are found, the result will still return them, but at the bottom in no specific order.

You can edit the order by, to obtain the items in the order you like.

Here a sqlfiddle.com