The training module of an application I am working required a section that displays all the file attachments from five different training types submitted by a user. A request was sent in requesting that files from all the tables be combined into one report.
The training records to be filed for each employee include the following:
Additionally, a user table (USER) is provided as well, containing the bio-data of employees.Each of these training tables have common fields:
USER which is the integer for the user’s IDCERTIFICATE_FILE which contains the BLOB or link to the scanned copy of the training completion certificate.The Challenge:
To search all 5 tables and collect the file attachments related a given user’s training and display them in the given format:
TIDTYPETITLEDATE_OF_COMPLETIONCERTIFICATE_FILEThe Solution (Sort of):
The approach here is to gather and build the sub-tables inside the MySQL query. Several sub-queries will be run to search the items inside each table and the final results will be combined in a master query.
Below is what I have:
SELECT
-- build the table: combine the fields into HTML
CONCAT(
"<table>"
, "<thead>"
, "<tr>"
, "<td colspan=\"5\">"
, "Employee Name: "
, `USER`.`NAME`
, "</td>"
, "</tr>"
, "</thead>"
, "<tbody>"
-- minimum requirement rows
, @file_mq
-- classroom training rows
, @file_class
-- on the job training rows
, @file_ojt
-- specialisation training rows
, @file_spl
-- miscellaneous training rows
, @file_misc
, "</tbody>"
, "<table>"
)
, @file_mq := @file_mq AS `@file_mq`
, @file_class := @file_class AS `@file_class`
, @file_ojt := @file_ojt AS `@file_ojt`
, @file_spl := @file_spl AS `@file_spl`
, @file_misc := @file_misc AS `@file_misc`
FROM `USERS`
WHERE 1
-- fetch the values from the minimun hire requirements
, (SELECT @file_mq := CONCAT(
, GROUP_CONCAT(
CONCAT(
"<tr>"
, "<td>"
, `MIN_Q`.`ID`
, "</td><td>"
, "Minimum Requirement"
, "</td><td>"
, `MIN_Q`.`TITLE`
, "</td><td>"
, `MIN_Q`.`DATE_OF_COMPLETION`
, "</td><td>"
, `MIN_Q`.`CERTIFICATE_FILE`
, "</td>"
, "</tr>"
)
SEPARATOR "")
, "")
FROM `MIN_Q`
WHERE 1 AND `MIN_Q`.`USER` = "3"
) AS `file_mq`
-- fetch the rows from the classroom work
, (SELECT @file_class := CONCAT(
, GROUP_CONCAT(
CONCAT(
"<tr>"
, "<td>"
, `CLASS_TRAIN`.`ID`
, "</td><td>"
, "Classroom Training"
, "</td><td>"
, `CLASS_TRAIN`.`TITLE`
, "</td><td>"
, `CLASS_TRAIN`.`DATE_OF_COMPLETION`
, "</td><td>"
, `CLASS_TRAIN`.`CERTIFICATE_FILE`
, "</td>"
, "</tr>"
)
SEPARATOR "")
, "")
FROM `CLASS_TRAIN`
WHERE 1 AND `CLASS_TRAIN`.`USER` = "3"
) AS `file_class`
-- fetch the rows from the on the job training
, (SELECT @file_ojt := CONCAT(
, GROUP_CONCAT(
CONCAT(
"<tr>"
, "<td>"
, `OJT_TRAIN`.`ID`
, "</td><td>"
, "On-the-Job Training"
, "</td><td>"
, `OJT_TRAIN`.`TITLE`
, "</td><td>"
, `OJT_TRAIN`.`DATE_OF_COMPLETION`
, "</td><td>"
, `OJT_TRAIN`.`CERTIFICATE_FILE`
, "</td>"
, "</tr>"
)
SEPARATOR "")
, "")
FROM `OJT_TRAIN`
WHERE 1 AND `OJT_TRAIN`.`USER` = "3"
) AS `file_ojt`
-- fetch the specialised training
, (SELECT @file_spl := CONCAT(
, GROUP_CONCAT(
CONCAT(
"<tr>"
, "<td>"
, `SPL_TRAIN`.`ID`
, "</td><td>"
, "Specialised Training"
, "</td><td>"
, `SPL_TRAIN`.`TITLE`
, "</td><td>"
, `SPL_TRAIN`.`DATE_OF_COMPLETION`
, "</td><td>"
, `SPL_TRAIN`.`CERTIFICATE_FILE`
, "</td>"
, "</tr>"
)
SEPARATOR "")
, "")
FROM `SPL_TRAIN`
WHERE 1 AND `SPL_TRAIN`.`USER` = "3"
) AS `file_spl`
-- fetch the miscellaneous
, (SELECT @file_misc := CONCAT(
, GROUP_CONCAT(
CONCAT(
"<tr>"
, "<td>"
, `MISC_TRAIN`.`ID`
, "</td><td>"
, "Miscellaneous Training"
, "</td><td>"
, `MISC_TRAIN`.`TITLE`
, "</td><td>"
, `MISC_TRAIN`.`DATE_OF_COMPLETION`
, "</td><td>"
, `MISC_TRAIN`.`CERTIFICATE_FILE`
, "</td>"
, "</tr>"
)
SEPARATOR "")
, "")
FROM `MISC_TRAIN`
WHERE 1 AND `MISC_TRAIN`.`USER` = "3"
) AS `file_misc`
AND `USERS`.`ID` = "3"
GROUP_CAT String Limits
Now this approach of building the html inside the MySQL takes a tool on the length limits. So we would have to run the following command before the above:
SET SESSION group_concat_max_len = 100000000This will increase the GROUP_CONCAT result limits to 100M characters.
Picking valid files only
Additionally we can add check before each file row is added to the sub-query, to determine if the file exists. this way we can ensure that the file that the user has submitted a valid certificate file. Doing that, the minimum table sub-query will look like this:
, (SELECT @file_mq := CONCAT(
, GROUP_CONCAT(
CONCAT(
"<tr>"
, "<td>"
, `MIN_Q`.`ID`
, "</td><td>"
, "Minimum Qualidication"
, "</td><td>"
, `MIN_Q`.`TITLE`
, "</td><td>"
, `MIN_Q`.`DATE_OF_COMPLETION`
, "</td><td>"
, `MIN_Q`.`CERTIFICATE_FILE`
, "</td>"
, "</tr>"
)
SEPARATOR "")
, "")
FROM
`MIN_Q`
WHERE 1
AND `MIN_Q`.`USER` = "3"
AND LOAD_FILE(`MIN_Q`.`CERTIFICATE_FILE`) IS NOT NULL
) AS `file_mq`