0

I'm trying to figure out how to use group and max() on joins correctly, I'm doing some parsing of a moodle(Open source school software) mysql database. Students are allowed to retake the quizes indefinatly for this particular program, but I need to be able to update the course completion date to reflect the last time they took the test because a lot of other things depend on the completion fields. The mdl_quiz_attempts table stores all attempts for all quizes, the userid will have many of the same entries, but the attempt number is not unique to the table, but instead unique to both the student AND the key for the row. Meaning students have multiple entries. On the mdl_course_modules table, The instance field is the key for the mdl_quiz table, and the mdl_course_modules_completion coursemoduleid field is the key for mdl_course_modules.

So what I want to do is this: given a student id UPDATE mdl_course_completion.timemodified to mdl_quize_attempts.timemodified WHERE the row on mdl_quiz_attempts is the max attempt by userid for each quiz.(the quiz field on the quiz_attempts has to be looked up through in course module instance table to get the, instance id for course completion module id)

Here are example partial tables.

mdl_quiz_attempts
id  quiz    userid  attempt timemodified    
2   1   3   6   1365408901  
6   1   4   1   1369873688  
7   2   4   1   1369877532  
8   7   4   1   1369881431  
9   7   4   2   1369882897  
12  5   4   1   1505165504  
13  6   4   1   1369887643  
17  8   4   1   1369958105  
18  1   4   2   1374557701  
22  7   4   3   1374639901  
23  6   4   7   1374640202  
24  5   4   2   1374639901  
25  8   4   2   1374639901  
26  2   4   2   1374639301  
27  2   6   1   1376620469  
29  2   12  1   1389915486  
30  1   23  1   1390978667  
31  1   23  2   1391030924  
32  2   23  1   1392113103  
33  2   23  2   1392696602  
34  2   23  3   1392767435  
35  7   12  1   1398914256  
36  8   43  1   1405281193  
37  1   50  1   1405522411  
38  5   43  1   1505165504  

mdl_course_modules
id  course  module  instance    section 
3   2   9   2   3   
5   2   17  2   4   
7   2   17  3   5   
8   2   17  4   6   
9   2   17  5   7   
10  2   17  6   8   
11  2   17  7   9   
12  2   17  8   10  
13  2   17  9   11  
14  2   17  10  12  
15  2   17  11  13  
25  2   16  1   14  
26  2   23  1   4   
28  2   7   1   14  
30  4   9   4   26  
42  4   23  3   33  
45  4   23  6   38  
46  4   23  7   37  
47  4   23  8   36  
48  4   23  9   35  
49  4   23  10  32  
50  4   23  11  34  
51  5   9   5   27  
53  5   23  12  43  
55  5   23  13  44  

mdl_quiz
id  name    
10  Unit 10 Quiz    
11  Unit 2 Quiz 
12  Unit 3 Quiz 
13  Unit 5 Quiz 
14  Unit 1 Quiz 
15  Unit 8 Quiz 
16  Unit 9 Quiz 
17  Unit 7 Quiz 
18  Unit 4 Quiz 


mdl_course_modules_completion
id  coursemoduleid  userid  completionstate viewed  timemodified    
14  25  2   0   1   0   
15  25  6   0   1   0   
67  25  4   1   1   1369873688  
68  28  4   1   0   1369874483  
69  192 4   1   0   1369875233  
70  184 4   1   1   1369877532
UserZer0
  • 1,441
  • 2
  • 14
  • 27

1 Answers1

1

Something like this ?

update mdl_course_modules_completion c
join mdl_quiz_attempts a on a.userid = c.userid
join (select max(attempt) max_attempts from mdl_quiz_attempts group by userid) max on max.max_attempts = a.attempt
set c.timemodified = a.timemodified
where c.userid = :<USER_ID>
antoine.lange
  • 731
  • 6
  • 24
  • Not only did that not select a different value for each quiz, it updated the course completions dates to the same value which was the earliest attempt at any quiz. – UserZer0 Apr 21 '19 at 16:57
  • Thanks I was able to work with what you had, and tweaked it to accomplish my goal. Not sure about efficiency but this seems to work: UPDATE mdl_course_modules_completion c JOIN mdl_quiz_attempts a ON a.userid = c.userid JOIN( SELECT MAX(attempt) max_attempts FROM mdl_quiz_attempts GROUP BY userid ) MAX ON MAX.max_attempts = a.attempt SET c.timemodified = a.timemodified WHERE c.userid = 308 and a.quiz = (SELECT instance FROM `mdl_course_modules` where id = c.coursemoduleid) – UserZer0 Apr 22 '19 at 01:39