Order By

Loading "Sorting Users by Recent Entry with Raw SQL"

Sorting Users by Recent Entry with Raw SQL

👨‍💼 Ok ok, hear me out. It's awesome that we can search by our user's names and usernames. But what would be more awesome is if the users were sorted by how active they've been. We don't want to have a user who hasn't added or updated a note in months or years coming up ahead of a user who adds and updates notes on a daily basis.

We want our users to find the most active users. So let's sort things by how active they've been. We'll do this by identifying each user's most recently updated note. We'll use the updatedAt of the most recently updated note. And then sort the users we've found by that field for each of them.

🦉 Order By can be pretty straightforward:

SELECT id, title, content from Review ORDER BY updatedAt LIMIT 30;

But it can also be more complicated using a nested query, which is what we'll need to do:

SELECT
	id,
	name,
	content
from
	Ship as ship
ORDER BY
	(
		SELECT
			avgRating
		FROM
			(
				SELECT
					ship.id,
					AVG(shipReview.rating) AS avgRating
				FROM
					Ship ship
					INNER JOIN ShipReview shipReview ON shipReview.subjectId = ship.id
				GROUP BY
					ship.id
			) AS shipRatings
		WHERE
			shipRatings.id = ship.id
	) DESC
LIMIT
	50

👨‍💼 Luckily, our ORDER BY won't be quite as complicated. Nested queries like this are one example of the power of SQL!

It's easiest to think of the nested query on its own before combining it with the parent query. So consider the requirement:

Select updatedAt
From the Note model
Where the ownerId matches the user.id
Order by the updatedAt descending (DESC)
Get only the first one (LIMIT)

Once you have that, then you can wrap that in an ORDER BY and you're golden.

Remember, ChatGPT can help guide you on this one or if you really get stuck you can check the diff tab.

To test this one out, perform a search, then open the second user, and edit one of their notes. Go back and perform the same search and that user should show up on top.

08. SQL/04. Order By (💪 problem)

Order By