Combining queries

Combining queries

by Ron Meske -
Number of replies: 4
Picture of Particularly helpful Moodlers

The results of a query I created from two different queries is not returning the same results.

Query A

SELECT 
 c.fullname Course
 u.firstname First,
 u.lastname Last,
 u.id ID,
 u.institution Company
 
 FROM (mdl_scorm_scoes_track AS st)
 JOIN mdl_user AS u ON st.userid=u.id
 JOIN mdl_scorm AS sc ON sc.id=st.scormid
 JOIN mdl_course AS c ON c.id=sc.course
 Join mdl_user_enrolments AS uenr ON uenr.userid=u.id
 Join mdl_enrol AS enr ON enr.id=uenr.enrolid 
 
 WHERE (
 (st.value='incomplete' OR st.value='not attempted') 
 AND DATEDIFF(NOW(), FROM_UNIXTIME(uenr.timecreated)>60)
 ORDER BY c.fullname, u.lastname,u.firstname, u.id

Query B

SELECT
 c.fullname AS Course,
 u.firstname AS Firstname,
 u.lastname AS Lastname,
 u.id AS ID,
 u.institution AS Company
 
 IF (u.lastaccess = 0,'never',
 DATE_FORMAT(FROM_UNIXTIME(u.lastaccess),'%Y-%m-%d')) AS dLastAccess
 
 ,(SELECT DATE_FORMAT(FROM_UNIXTIME(timeaccess),'%Y-%m-%d') FROM mdl_user_lastaccess WHERE userid=u.id AND courseid=c.id) AS CourseLastAccess 
 FROM mdl_user_enrolments AS ue
 JOIN mdl_enrol AS e ON e.id = ue.enrolid
 JOIN mdl_course AS c ON c.id = e.courseid
 JOIN mdl_user AS u ON u.id = ue.userid
 LEFT JOIN mdl_user_lastaccess AS ul ON ul.userid = u.id
 WHERE ul.timeaccess IS NULL AND (DATEDIFF(NOW(), FROM_UNIXTIME(ue.timecreated))>60)
 ORDER BY u.id, c.fullname

I have combined them into Query C

SELECT
 c.fullname AS Course,
 u.firstname AS Firstname,
 u.lastname AS Lastname,
 u.id AS IDNumber,
 u.institution AS Institution,
 
 IF (u.lastaccess = 0,'never',
 DATE_FORMAT(FROM_UNIXTIME(u.lastaccess),'%Y-%m-%d')) AS dLastAccess
 
 ,(SELECT DATE_FORMAT(FROM_UNIXTIME(timeaccess),'%Y-%m-%d') FROM mdl_user_lastaccess WHERE userid=u.id AND courseid=c.id) AS CourseLastAccess
 
 
 FROM mdl_user_enrolments AS ue
 JOIN mdl_enrol AS e ON e.id = ue.enrolid
 JOIN mdl_course AS c ON c.id = e.courseid
 JOIN mdl_user AS u ON u.id = ue.userid
 LEFT JOIN mdl_user_lastaccess AS ul ON ul.userid = u.id
 WHERE (ul.timeaccess IS NULL OR ue.userid IN
 (SELECT u.id 
 FROM (mdl_scorm_scoes_track AS st)
 JOIN mdl_scorm AS sc ON sc.id=st.scormid
 
 WHERE c.id=sc.course AND st.userid=u.id AND (st.value='incomplete' OR st.value='not attempted') 
 )
 )AND (DATEDIFF(NOW(), FROM_UNIXTIME(ue.timecreated))>60)
 ORDER BY c.fullname, u.lastname,u.firstname

I have not found where my logic is incorrect in Query C. Query C is adding an incorrect record not found by either A or B and duplicating entries in a couple of cases.

I would like some pointers on where my logic on combining the 2 went astray.

 

The purpose of the queries is to identify anyone that has been enrolled in a SCORM course and has not accessed or completed that course within 60 days.

Average of ratings: -
In reply to Ron Meske

Re: Combining queries

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Can't you just combine the two queries with a UNION?

Average of ratings: -
In reply to Ron Meske

Re: Combining queries

by Philip Templeton -

At first glance, it seems that the inner joins are building the desired result table but the Left join is stacking two separate tables into the result table that is not related to the previous joins.

For e.g. student john smith has a record in the enrolments table, match his record in the enrol table based on the enrolments table, then match his course he's taking in the enrolments table to the courses in the courses table,) this is where I believe the query breaks down -> then match anyone with a last access to  anyone that has a userid limiting the result set to the where clause. To prove the thought remove the where clause and you may have duplicate entries for everybody who has a userid and last access.

As Tim mentioned you may be able to union the two queries or what I have found success in is creating views. Create a view of all users enrolled in the SCORM courses then run a query against the view limiting to the date ranges you have specified.  There is also the approach of creating a temporary table to hold the result set from the inner joins and then running a second query all in the same script that filters the table by date.

Hope that helps.

Phil Templeton

Average of ratings: -
In reply to Philip Templeton

Re: Combining queries

by Ron Meske -
Picture of Particularly helpful Moodlers

I will have to look more at how UNIONs work to determine if it will provide what I am looking for.  I hadn't questioned the LEFT JOIN, as I took that query from user contributed queries and after a few fixes it seemed to work as I expected.  I will also have to look into that.

In case this helps, my intent is to list all users that have been enrolled into a course but within a given timeframe, have not logged into the system, accessed the course or, finally, have not completed the SCORM activity in the course.

So the logic I am looking for is:

  1. If the user has not logged in within 60 days - display name
  2. If logged in but has not accessed the course within 60 days - display name
  3. If logged in and has accessed the course but has not completed the course activity- display name

Query A does list all users that started the activity but have not completed within 60 days.

Query B does list all users that have not logged in or accessed the course within 60 days or at least it appears that way.  The problem with Query A was that it also lists users that started the SCORM activity but are no longer enrolled in the course.

Query C did correct the problem with Query A and did exclude those users that did not complete and were unenrolled. 

Average of ratings: -
In reply to Ron Meske

Re: Combining queries - resulting query

by Ron Meske -
Picture of Particularly helpful Moodlers

I ended up starting from scratch to create the query I was looking for.  I eliminated the duplicates by using DISTINCT.  Based on the existing data I had, this did return the correct number of items. 

This query will return a list of all users that:

1. Have been enrolled as a STUDENT in a course more than 30 days, AND

2a. Have never logged in or accessed the course, OR

2b. Have not completed the course and obtained a score.

I have ignored the use of the context table to ensure that I was only looking at a course as our setup is only using SCORM bsed courses, so not sure if this will work for others.

 

 

SELECT DISTINCT 
c.fullname Course, 
u.lastname 'last name', 
u.firstname 'first name', 
u.idnumber 'ID number',
(DATE_FORMAT(FROM_UNIXTIME(ue.timemodified),'%Y-%m-%d')) 'Enrolled on', 
IF (ul.timeaccess is NULL,'never',
DATE_FORMAT(FROM_UNIXTIME(ul.timeaccess),'%Y-%m-%d')) 'Last Accessed on', 
(DATEDIFF(NOW(), FROM_UNIXTIME(ue.timecreated))) 'Days Enrolled'

FROM prefix_user_enrolments ue
JOIN prefix_user u ON u.id=ue.userid
JOIN prefix_enrol en ON en.id=ue.enrolid
JOIN prefix_course c ON c.id=en.courseid
LEFT JOIN prefix_user_lastaccess ul ON (ul.courseid=c.id AND ul.userid=ue.userid)
JOIN prefix_role_assignments ra ON ra.userid=ue.userid
JOIN prefix_role as r ON r.id=ra.roleid
JOIN prefix_grade_items gi ON gi.courseid=c.id
LEFT JOIN prefix_grade_grades gg ON (gg.userid=ue.userid AND gg.itemid=gi.courseid)

WHERE ue.status=0 AND (DATEDIFF(NOW(), FROM_UNIXTIME(ue.timecreated))>30) AND r.name='student' AND (gg.finalgrade is NULL OR gg.finalgrade = 0) 

ORDER BY c.fullname, u.lastname, u.firstname

Currently this lists all courses.  if you use this in Configurable Reports and add a filter for courses add then add the following on the line before ORDER BY.

%%FILTER_COURSES:c.id%%
Average of ratings: -