Using Joins To Return Row With Most Recent Date

[Arithan]

I'm working on creating a badge/achievement system for a website but am having problems trying to only show the highest badge for a specific type earned. Currently, I can use the following query to have it show all badges earned for a user:

 

SELECT b.badge_name, b.badge_description FROM badges AS b INNER JOIN badges_user AS bu USING (badge_id) WHERE bu.user_id=$id

 

$id is the id of the user.

 

There are 3 different type of badges total and 10 badges for each type. I want it only show the highest badge earned for each type. Badges are earned after a certain number of uploads, ratings, and downloads.

 

Here are the 3 MySQL tables involved:

 

badges

badge_id

badge_type_id

badge_name

badge_description

trigger

 

badges_user

badge_user_id

badge_id

user_id

date

 

badges_progress

badge_progress_id

user_id

rank

uploads

ratings

downloads

 

The badges table has all 30 badges that can be earned as well as the number threshold (trigger) needed to earn the badge. The badges_user table has all the users that have earned badges. The badges_progress table keeps track of the progress of users.

 

So what I'm trying to do is to create a SQL query that will only show the highest badge earned for each of the 3 types (uploads, ratings, downloads) for a specific user.

 

I tried using:

 

SELECT badges.badge_name, badges.badge_description FROM badges, badges_user WHERE badges.badge_id=badges_user.badge_id AND badges_user.user_id=$id GROUP BY badges.badge_type_id

 

but it only shows the first one of each type instead of the lastest one. Putting a ORBER BY badges_user.date DESC didn't do anything.

 

After doing some research online and in the book about joins, I found this from http://stackoverflow...d-in-each-group:

 

SELECT m1.* FROM messages m1 LEFT JOIN messages m2 ON (m1.name = m2.name AND m1.id < m2.id) WHERE m2.id IS NULL

 

but it couldn't figure out how to modify it to work with all 3 of my tables. I managed to get it to show the highest possible badge for each type only if the user has earned it with the following query, but for those who haven't earned the highest, it shows nothing:

 

SELECT b1.badge_name, b1.badge_description FROM badges AS b1 INNER JOIN badges_user AS bu ON (bu.badge_id=b1.badge_id) LEFT JOIN badges AS b2 ON (b1.badge_type_id = b2.badge_type_id AND b1.badge_id < b2.badge_id) WHERE b2.badge_id IS NULL AND bu.user_id=$id

 

I just want it to show the highest badge for each type that a specific user has earned. Sorry if something is unclear, it was hard trying to explain everything.

 

Thanks in advance for the help.

 

Apache: 2.2.22

PHP: 5.2.17

MySQL: 5.1.61-community-log

[Antonio Conte]

How do you do the internal ranking of badges? It looks like it's missing.

 

I would've added a column like badge_rating inside the badge table. With a badge rating, you could do a

 

SELECT bla FROM bla WHERE user_id = $id ORDER BY badges_user.date, badges.rating

 

With a couple of joins, you have it.

 

Edit: Ok, you only wanted ONE badge. Hmm. Look up nested SELECT queries. I'm pretty sure you need one of those to be able to find the highest rated badge in each category.

[Arithan]

The trigger column in the badges table could be used to rank the badges, or even the badge id itself since they are in ascending order. However, my original idea was to use the badge_user table with a join to the badges table to only show the most recent earned badge in each of the 3 categories for a specific user on their profile page. I tried grouping the badge_type_id column and sorting by the date (in the badges_user table) in descending order but it would only return the first of each type instead of the last.

 

Here are some examples of what I have in the badges and badges_user tables:

 

badges

badge_id   badge_type_id    badge_name      badge_description          trigger
1              1             Newbie          Uploaded 1 file              1
2              1             Contributor     Uploaded 10 files            10
3              2             Likeable        10 ratings received          10
4              2             Popular         50 ratings received          50
5              3             Hot             10 downloads received        10
6              3             Burning         100 downloads received       100


badges_user

badge_user_id   badge_id     user_id	date
1		1		1	dates stored using datetime
2		2		1
3		1		2
4		5		3
5		3		4
6		4		4

 

So what I'm trying to do is to use the badge_type_id column in the badges table to determine the badge type, and the date column in the badges_user table to determine the lastest badge earned for each type for a specific user.

[Antonio Conte]

Ok. Then you have ranking. Very good.

 

Try using a function like MAX(badge_id) in the SELECT part of your query. This solved a very similar problem I had some time ago.

[Arithan]

Using MAX(badge_id) works for the badge_id, but what I want to show is the badge_name and badge_description. I could use another query to select all the rows with those badge id's after selecting them, but it doesn't seem like an efficient way to do it since it would have to loop through and select each row one by one that match the badge id's. For scaleability, it would take the page a long time to load if there are a lot of rows.

[Antonio Conte]

I will look more into this tomorrow, but have you thought about using some JSON? Get the badges into an array and match the array key (badge id) to it with your query. You could then build this array again if you make updates to the badge table. As for scalability, this would be pretty efficient as you won't need time for disk access (SQL disk lookups) and have a constant time operation when matching with the array. (Just make sure it's an associative one)

 

Need more help, scream out. Too tired to really look into this now. Out.