php - Is it possible for MySQL to order by a certain column but if a value of another column is X put it at the end?
Get the solution ↓↓↓I have 3 tables,users
,warnings
,warningnames
.
The content of the tables is as follows:
Users(simplified):
u_id, username, password, rank
Warnings:
w_id, wn_id, u_reporter_id, u_reported_id
Warningnames:
wn_id, warningInfo, warningPoints
I have a loop in PHP which gets me all users with a simple query:
SELECT *
FROM users
WHERE u_id < :startcount
ORDER BY u_id DESC
LIMIT :perpage
How do I make a query which gets all users but puts the users with the most warnings at the top and the users with rank 0 at the bottom?
The query I have so far is:
SELECT users.*, COUNT(warnings.w_id) as warningCount
FROM users
LEFT JOIN warnings
ON users.u_id = u_reported_id
WHERE u_id < :startcount
ORDER BY warningCount DESC
LIMIT :perpage
But that doesn't return what I want, it just returns thewarningCount
of all users in a single query that fit theWHERE
statement.
Example fiddle:
http://sqlfiddle.com/#!9/ec6c2d/6
Sample PHP code:
<?php
$query = $this->handler->prepare('SELECT * FROM users WHERE u_id < :startcount ORDER BY u_id DESC LIMIT :perpage');
try{
$query->execute([
':startcount' => 25,
':perpage' => 25
]);
}
catch(PDOException $e){
echo $e->getMessage();
}
while($fetch = $query->fetch(PDO::FETCH_ASSOC)){
echo $fetch['username'] . '<br />';
}
?>
How can I correct this and order by awarningCount
column but put all users withrank
0 at the end?
Answer
Solution:
If I understand it, you want to order by warning count, but ignore users with rank = 0 on that query ?? if that's the case, what you need is group by, and then order by using case when, here's what I came up :
SELECT users.*, COUNT(warnings.w_id) as warningCount
FROM users
LEFT JOIN warnings
ON users.u_id = u_reported_id
WHERE u_id < 10
group by users.u_id
ORDER BY
case when rank = 0
then 0
else COUNT(warnings.w_id) + 1
end
DESC
LIMIT 25
I add +1 on order by to make sure, users with rank 0 is always at the bottom.
Edit :
- I Add the where startCount and the limit to the query
Answer
Solution:
The SQL Fiddle was helpful in explaining you situation, and I think you're looking for GROUP BY. Also note that you can order by multiple fields.
I've edited your fiddle slightly: https://www.db-fiddle.com/f/av9d6ordS9XVpavwzZUs8W/0
SELECT u.*, COUNT(w.wn_id) warnings
FROM users u
INNER JOIN warnings w ON u.u_id = w.u_reported_id
GROUP BY u.u_id
ORDER BY warnings DESC, u.rank DESC
# LIMIT 10
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: property [id] does not exist on this collection instance.
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.