Arithan Posted March 20, 2012 Share Posted March 20, 2012 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 Link to comment Share on other sites More sharing options...
Antonio Conte Posted March 20, 2012 Share Posted March 20, 2012 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. 1 Link to comment Share on other sites More sharing options...
Arithan Posted March 20, 2012 Author Share Posted March 20, 2012 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. Link to comment Share on other sites More sharing options...
Antonio Conte Posted March 20, 2012 Share Posted March 20, 2012 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. 1 Link to comment Share on other sites More sharing options...
Arithan Posted March 20, 2012 Author Share Posted March 20, 2012 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. Link to comment Share on other sites More sharing options...
Antonio Conte Posted March 21, 2012 Share Posted March 21, 2012 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. Link to comment Share on other sites More sharing options...
Recommended Posts