[Phx] Backend SQL

[TheJacob]

Just was writing up some queries for the backend panel of my ICECRON project and decided to release a few to the public to implement in their own CMS.

The following query gets all the furniture and the furniture count by username:

SELECT users.username AS username, f.public_name AS furniture, COUNT(*) AS total_count
FROM items items
INNER JOIN users users
    ON users.id = items.user_id
INNER JOIN furniture f
    ON f.id = items.base_item
WHERE users.username = '<username>' /* This is the username, i.e. TheJacob. */
GROUP BY f.public_name
HAVING COUNT(*) >= 1
ORDER BY COUNT(*) DESC;

Just thought it'd be handy to have, as I know I used to unfortunately suffer with some people cloning or giving furniture they shouldn't :/.

 

[RastaLulz]

Always fun to use queries like this, not only does it reduce the amount code/queries you have to write, but it also improves performance.

Cool release nonetheless.