I need to sort by the number of likes of the question on the main page. I am using php and mysql. I need to Sql query.
Explanation => status = 1 means -> liked status = 2 means -> unliked
First table :
Questions
| id | title | question | created_user_id | created_date | category_id |
|---|---|---|---|---|---|
| 1 | title-1 | question 1 | 1 | 2021/11/18 | 1 |
| 2 | title-2 | question 2 | 1 | 2021/11/18 | 4 |
| 3 | title-3 | question 3 | 5 | 2021/11/18 | 4 |
Second table :
Likes
| id | liked_user_id | liked_question_id | status |
|---|---|---|---|
| 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 1 |
| 3 | 3 | 1 | 1 |
I want it to be sorted by the number of likes on the homepage. If the question is not liked, it should be at the bottom after the ranking.
Example :
<Card> First -> should be the first question. because more likes </Card>
<Card> Second-> should be the second question. because the number of likes is 1 </Card>
<Card> Third-> should be the third question. because there are no likes </Card>
CodePudding user response：
I hope I understood your question correctly. You want to sort the table of questions according to the number of likes. You have to get the number of likes of each question and joined it with the table of questions.
SELECT q.*,l.num
FROM Questions q
LEFT JOIN
(SELECT COUNT(status) AS num,liked_question_id
FROM Likes
GROUP BY liked_question_id) l ON q.id = l.liked_question_id
ORDER bY num DESC
demo in db<>fiddle