view the badges in a course (issued AND not issued) for all users

view the badges in a course (issued AND not issued) for all users

per Lawrence Symes -
Nombre de respostes: 9

Thanks Randy for all your work on the plugin, and for the effort in responding to all of us!

I have been struggling to customise the badges issued by user custom report for several weeks now, and am losing sleep 🤣😳💀

  1. I'd like to shows all the badges in a specific course, and show if they have been achieved, by group. 
  2. Course users are enrolled into separate exclusive groups (i.e they do not appear in more than one).

*(ideally, it would be great to have an expiry status where there is one, but i haven't tried that yet)

T

For giggles here is the code i have done so far but if there is an easier way i would love to know. So far i can only show badges issued, and i *think* that the code is working for what i want in the 

SELECT DISTINCT g.name AS "group name", u.username, b.name AS badgename,
CASE
WHEN b.courseid IS NOT NULL THEN
(SELECT c.shortname
    FROM prefix_course c
    WHERE c.id = b.courseid)
WHEN b.courseid IS NULL THEN '*'
END AS "course name",
CASE 
  WHEN t.criteriatype = 1 AND t.method = 1 THEN 'Activity Completion (All)'
  WHEN t.criteriatype = 1 AND t.method = 2 THEN 'Activity Completion (Any)'
  WHEN t.criteriatype = 2 AND t.method = 2 THEN 'Manual Award'
  WHEN t.criteriatype = 4 AND t.method = 1 THEN 'Course Completion (All)'
  WHEN t.criteriatype = 4 AND t.method = 2 THEN 'Course Completion (Any)'
  ELSE CONCAT ('Other: ', t.criteriatype)
END AS Criteriatype,
DATE_FORMAT( FROM_UNIXTIME( d.dateissued ) , '%Y-%m-%d' ) AS dateissued,
CONCAT ('<a target="_new" href="%%WWWROOT%%/badges/badge.php?hash=',d.uniquehash,'">Badge Details</a>') AS Details
FROM prefix_badge_issued AS d 
JOIN prefix_user AS u ON d.userid = u.id
JOIN prefix_badge AS b ON d.badgeid = b.id
JOIN prefix_badge_criteria AS t ON b.id = t.badgeid 
JOIN prefix_cohort_members AS cm ON u.id = cm.userid
JOIN prefix_cohort AS ch ON cm.cohortid = ch.id
JOIN prefix_context AS x ON ch.contextid = x.id
JOIN prefix_course AS c ON b.courseid = c.id
JOIN prefix_groups AS g on c.id = g.courseid
JOIN prefix_groups_members AS gm on u.id = gm.userid
WHERE t.criteriatype IN (1,2,3,4)
AND b.courseid = %%COURSEID%%
AND c.category = 157
AND g.name = 'Test Group'
##AND ch.name ='Test Cohort'

Any help would be wholeheartedly appreciated.

Mitjana de qualificacions: -
En resposta a Lawrence Symes

Re: view the badges in a course (issued AND not issued) for all users

per Lawrence Symes -
For some reason the layout i made of the table I need did not save. I’m aware that this order might be a little different than in the above code…

GROUP, COURSE, USERNAME, BADGENAME , BADGE STATUS(issued/not_issued/expired), BADGE DETAILS
Mitjana de qualificacions: -
En resposta a Lawrence Symes

Re: view the badges in a course (issued AND not issued) for all users

per Randy Thornton -
Without going into too much detail, usually the issue with using groups like this is the inner join: so when you do that, it limits all the badges shown in the results to only those with groups, that is in this case when awarded.

This is a case where a left join is probably a way to a solution.
Mitjana de qualificacions:Useful (1)
En resposta a Randy Thornton

Re: view the badges in a course (issued AND not issued) for all users

per Lawrence Symes -
Hi Randy,

I realise I didn't specify where I have been having trouble - I am new to SQL queries, so it's likely there were several issues in my example! 
have been grappling with left join in this example for the last few hours and simply cannot grasp it.  I don't know if it is my code or my logic (I've tried another from scratch attached- thinking maybe I can show the status by checking if the badge id does/not exist in the badge issue table). 

I'm not attached to the queries I've written, I just the simplest way to show every badge status in the for every user in the course, for teachers to identify/filter those users in their cohort/group, something like: 



How would you do it?



-------------
I am trying to track the level of understanding of users, using 3 badges
"CLevel1"  = easy activities are completed,
"CLevel2" = medium... (plus C1 achieved)
"CLevel3"=hard...(plus C1, C2)

1. need to see the status of all the badges for all users in the course.  
2. users are enrolled into cohorts with matching group (separate) to help manage the workload for teachers. COHORTA: GROUPA, COHORTB: GROUPB etc which is why there needs to be able to identify or filter the users in their cohort or group (same name). 









Mitjana de qualificacions: -
En resposta a Lawrence Symes

Re: view the badges in a course (issued AND not issued) for all users

per Randy Thornton -
Lawrence,

I would not begin with that query. If you need to see all the badges, issued or not, I would begin with a query that shows all badges, such as this one.

The query above starts with only awarded badges, as you can see in the FROM statement that it starts with the badge_issued table and then goes from there. So, it already excludes badges that don't yet have any users earning them.

So, I would instead start with a query showing me all the badges in a courses, then add in the details for those that have been awarded. All the important pieces that you need are in two those queries, I would just do them the other way around, so to speak.
Mitjana de qualificacions:Useful (1)
En resposta a Randy Thornton

Re: view the badges in a course (issued AND not issued) for all users

per Randy Thornton -
Off the top of my head, here is a short query that does that: it is only the basics with no error checking or anything but shows you the structure: start with all badges, then pull the issued ones. Type = 2 means only course badges are included.

SELECT
c.shortname "Course",
b.name AS "Badge",
u.username
FROM prefix_badge b
JOIN prefix_course c ON c.id = b.courseid
LEFT JOIN prefix_badge_issued bi ON b.id = bi.badgeid
LEFT JOIN prefix_user u ON bi.userid = u.id
WHERE b.type = 2
# AND c.id = some course id number(s) or use %%COURSEID%% if using Config Reports


Mitjana de qualificacions:Useful (1)
En resposta a Randy Thornton

Re: view the badges in a course (issued AND not issued) for all users

per Lawrence Symes -
In trying this I got all the badges in the course, and the username alongside if they have earned a badge, however, the username is NULL column if the badge is not earned. I've tried experimenting with conditional arguments to target these but I've only managed to separate badges issued from badges not issued into different columns. Am I going completely up the wrong tree?

SELECT u.username, b.name,
CASE
WHEN NOT EXISTS
(SELECT *
FROM prefix_badge_issued AS bi
WHERE bi.userid = u.id)
THEN u.username
Else '*'
END AS "users_without_badge"
FROM prefix_badge AS b
JOIN prefix_course AS c ON c.id = b.courseid
LEFT JOIN prefix_badge_issued AS bi ON b.id = bi.badgeid
LEFT JOIN prefix_user AS u ON bi.userid = u.id
WHERE c.id = 961

thanks
Mitjana de qualificacions: -
En resposta a Lawrence Symes

Re: view the badges in a course (issued AND not issued) for all users

per Randy Thornton -
Yes, it shows nulls since the data is null, but you can easily add something else with code like IF(u.username IS NULL,'-',u.username). In any case, the sorting is what matters.

If you only want to find the badges with no awards, then this will help you out:

SELECT
b.name
FROM prefix_badge b
LEFT JOIN prefix_badge_issued bi ON b.id = bi.badgeid
WHERE bi.badgeid IS NULL

Again, the outer join makes this possible.

However, if you are thinking of trying to get all this into two columns (awarded vs unawarded) that would be very difficult in SQL, easy in PHP. Personally, I would use the first report and just sort it properly or download it into a spreadsheet to do any further machinations with it.
Mitjana de qualificacions:Useful (1)