CODEWITHSUNDEEP
mysql
Damien Bourdonneau
Damien Bourdonneau

Reputation: 91

badge system / MySQL GROUP BY and ORDER BY

I'm creating a badge system for my website: the screenshot below shows you how it looks now. You receive badges with multiple levels (bronze, silver...) after earning 10,100 etc points for specific actions (such as answering a question, correcting a wiki). Note that the looking glass with the dollar sign is there two times, one in silver and one in bronze:

Badges

The underlying are two tables, one with the badge informations (x_badges: stores what action and how many points in it gives you what badge level), one with which user has earned which badge (x_badges_earned: badgeID and uID).

Explanation of FKs:

This was my original query, which gives me only one badge of each category (but not the highest, ie if I have earned silver and bronze in one category it'll return only bronze):

SELECT * 
FROM x_badges_earned, x_badges 
WHERE xbe_bID = xb_ID 
  AND xbe_uID = ? 
GROUP BY xb_requirement  
ORDER BY xb_requirement ASC, xb_requirement_value ASC

After research on SO, I created this query which generates the above screenshot. If I change the last GROUP BY to xb_requirement, I'm back with the results from query 1:

SELECT b.* 
FROM x_badges b 
LEFT JOIN x_badges b2 ON b.xb_ID = b2.xb_ID 
                     AND b.xb_requirement_value < b2.xb_requirement_value 
LEFT JOIN x_badges_earned be ON be.xbe_bID = b.xb_ID 
WHERE b2.xb_requirement_value IS NULL 
  AND be.xbe_uID = ? 
GROUP BY b.xb_ID

I feel like I'm pretty much going in circles at this moment. I have come up with this which probably would work (at least in my head) but I can't solve it:

SELECT *
FROM x_badges
WHERE xb_ID IN (
    SELECT xb_ID, MAX(xb_requirement_value)
    FROM x_badges_earned, x_badges
    WHERE xbe_bID = xb_ID AND xbe_uID = ?
    GROUP BY xb_requirement
)

Any help is greatly appreciated!

I have attached the MySQL code to create the two tables and populate them to play around. Furthermore, you can use 1 as parameter value since it's my userID. My current output looks like this, the desired output would be the same without the first row (xb_ID 11) since it's a bronze badge for which I have earned silver.


CREATE TABLE `x_badges` (
  `xb_ID` int(11) NOT NULL,
  `xb_requirement` varchar(40) NOT NULL,
  `xb_requirement_value` int(11) NOT NULL,
  `xb_text` text NOT NULL,
  `xb_text_details` text NOT NULL,
  `xb_icon` varchar(20) NOT NULL,
  `xb_color` varchar(6) NOT NULL,
  `xb_color_text` varchar(6) NOT NULL,
  `xb_level_name` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



INSERT INTO `x_badges` (`xb_ID`, `xb_requirement`, `xb_requirement_value`, `xb_text`, `xb_text_details`, `xb_icon`, `xb_color`, `xb_color_text`, `xb_level_name`) VALUES
(11, 'added_interaction', 10, 'Added interactions', 'Received more than %d points adding interactions with target companies!', 'search-dollar', 'cc8e34', '', 'Bronze'),
(15, 'added_target', 10, 'Added targets', 'Received more than %s points adding target companies!', 'dollar-sign', 'cc8e34', '', 'Bronze'),
(16, 'asked_question', 10, 'Asked questions', 'Received more than %s points asking questions!', 'question', 'cc8e34', '', 'Bronze'),
(17, 'notified_colleagues_interaction', 10, 'Interactions shared with colleagues', 'Received more than %s points tagging colleagues in interactions added!', 'bullhorn', 'cc8e34', '', 'Bronze'),
(18, 'reply_accepted', 10, 'Helpful replies', 'Received more than %s points thanks to replies marked as helpful!', 'check-double', 'cc8e34', '', 'Bronze'),
(19, 'reply_question', 10, 'Replies to questions', 'Received more than %s points replying to questions!', 'comments', 'cc8e34', '', 'Bronze'),
(20, 'updated_info', 10, 'Updated target profiles', 'Received more than %s points updating profiles of targets!', 'funnel-dollar', 'cc8e34', '', 'Bronze'),
(21, 'updated_wiki', 10, 'Updated wiki', 'Received more than %s points updating profiles of Group companies!', 'pen-nib', 'cc8e34', '', 'Bronze'),
(22, 'upvote_question', 10, 'Helpful questions', 'Received more than %s points for having own questions upvoted!', 'grin-alt', 'cc8e34', '', 'Bronze'),
(23, 'added_interaction', 100, 'Added interactions', 'Received more than %s points adding interactions with target companies!', 'search-dollar', 'aaa9ad', '', 'Silver'),
(24, 'added_interaction', 500, 'Added interactions', 'Received more than %s points adding interactions with target companies!', 'search-dollar', 'ffd700', '495057', 'Gold'),
(25, 'updated_wiki', 1000, 'Updated Bertelsmann wiki', 'Received more than %s points asking questions!', 'pen-nib', 'b9f2ff', '495057', 'Diamond'),
(26, 'added_interaction', 1000, 'Added interactions', 'Received more than %s points adding interactions with target companies!', 'search-dollar', 'b9f2ff', '495057', 'Diamond'),
(27, 'added_interaction', 5000, 'Added interactions', 'Received more than %s points adding interactions with target companies!', 'search-dollar', 'e0115f', '', 'Ruby');



CREATE TABLE `x_badges_earned` (
  `xbe_ID` int(11) NOT NULL,
  `xbe_uID` int(11) NOT NULL,
  `xbe_bID` int(11) NOT NULL,
  `xbe_timestamp` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


INSERT INTO `x_badges_earned` (`xbe_ID`, `xbe_uID`, `xbe_bID`, `xbe_timestamp`) VALUES
(19, 1, 11, '2020-03-23 15:24:54'),
(20, 1, 15, '2020-03-23 15:24:54'),
(21, 1, 16, '2020-03-23 15:24:54'),
(22, 1, 17, '2020-03-23 15:24:54'),
(23, 1, 18, '2020-03-23 15:24:54'),
(24, 1, 19, '2020-03-23 15:24:54'),
(25, 1, 23, '2020-03-23 16:00:51'),
(26, 1, 20, '2020-03-23 15:32:31'),
(27, 1, 21, '2020-03-23 15:32:31'),
(28, 1, 22, '2020-03-23 15:32:31'),
(29, 10, 25, '2020-03-23 15:37:32');

db<>fiddle here

Upvotes: 0

Views: 67

Answers (1)

Akina
Akina

Reputation: 42622

I have not found identifying expression for any definite badge except (xb_icon, xb_requirement_value) values pair (color is secondary, timestamp not guaranteed the ordering).

So the solution can be:

SELECT *
FROM x_badges xb
JOIN x_badges_earned xbe ON xb.xb_ID = xbe.xbe_bID
JOIN ( SELECT xb.xb_icon, MAX(xb.xb_requirement_value) xb_requirement_value
       FROM x_badges xb
       JOIN x_badges_earned xbe ON xb.xb_ID = xbe.xbe_bID
       WHERE xbe.xbe_uID = @user
       GROUP BY xb_icon ) max_earned ON (xb.xb_icon, xb.xb_requirement_value) 
                                      = (max_earned.xb_icon, max_earned.xb_requirement_value)
WHERE xbe.xbe_uID = @user;

fiddle

As a recommendation - normalize x_badges table, divide it to 2 separate tables - one stored a badge type data, and another stored level-related data with the reference to first table.

Upvotes: 1

Related Questions