How to display mdl_course_sections.availability as plain text

How to display mdl_course_sections.availability as plain text

by Richard French -
Number of replies: 1

My group uses a lot of Access restrictions on course sections. It looks like the restrictions are kept in the mdl_course_sections table in the "availability" field in the following format:

{"op":"|","c":[{"type":"profile","cf":"Access","op":"contains","v":"this-group"},{"type":"profile","cf":"Access","op":"isequalto","v":"that-group"}],"show":false}

If I do the following:

  SELECT cs.availability

  FROM prefix_course AS c

  JOIN prefix_course_categories AS mcc ON mcc.id = c.category

  JOIN prefix_course_sections AS cs ON (cs.course = c.id)

 WHERE c.id != 1

cs.availability looks exactly like above. Any way to correctly format the field so that the access restrictions are in a more viewable format?


Thanks,

Richard


 

Average of ratings: -
In reply to Richard French

Re: How to display mdl_course_sections.availability as plain text

by Yaniv Cogan -
Using SQL String Functions (not all functions in link are necessarily supported in this plugin, but the most important ones, CONCAT, MID, REVERSE and LOCATE are), you can format text to pretty much everything you want, but that is a lot of work.


For example (of how much work goes into this kind of things, this example isn't relevant to your question otherwise) - here is what you have to do to format a string that contains a number into the number only:

SELECT
CAST(MID(results.survey_name, results.first, results.last - results.first) AS INT)  AS "Number"
FROM
(
SELECT
q.name AS "survey_name",
LEAST(
CASE WHEN LOCATE('0', q.name) = '0' THEN '99999' ELSE LOCATE('0', q.name) END,
CASE WHEN LOCATE('1', q.name) = '0' THEN '99999' ELSE LOCATE('1', q.name) END,
CASE WHEN LOCATE('2', q.name) = '0' THEN '99999' ELSE LOCATE('2', q.name) END,
CASE WHEN LOCATE('3', q.name) = '0' THEN '99999' ELSE LOCATE('3', q.name) END,
CASE WHEN LOCATE('4', q.name) = '0' THEN '99999' ELSE LOCATE('4', q.name) END,
CASE WHEN LOCATE('5', q.name) = '0' THEN '99999' ELSE LOCATE('5', q.name) END,
CASE WHEN LOCATE('6', q.name) = '0' THEN '99999' ELSE LOCATE('6', q.name) END,
CASE WHEN LOCATE('7', q.name) = '0' THEN '99999' ELSE LOCATE('7', q.name) END,
CASE WHEN LOCATE('8', q.name) = '0' THEN '99999' ELSE LOCATE('8', q.name) END,
CASE WHEN LOCATE('9', q.name) = '0' THEN '99999' ELSE LOCATE('9', q.name) END
)

AS "first",
2+CHAR_LENGTH(q.name)-LEAST(
CASE WHEN LOCATE('0', q.name) = '0' THEN '99999' ELSE LOCATE('0', REVERSE(q.name)) END,
CASE WHEN LOCATE('1', q.name) = '0' THEN '99999' ELSE LOCATE('1', REVERSE(q.name)) END,
CASE WHEN LOCATE('2', q.name) = '0' THEN '99999' ELSE LOCATE('2', REVERSE(q.name)) END,
CASE WHEN LOCATE('3', q.name) = '0' THEN '99999' ELSE LOCATE('3', REVERSE(q.name)) END,
CASE WHEN LOCATE('4', q.name) = '0' THEN '99999' ELSE LOCATE('4', REVERSE(q.name)) END,
CASE WHEN LOCATE('5', q.name) = '0' THEN '99999' ELSE LOCATE('5', REVERSE(q.name)) END,
CASE WHEN LOCATE('6', q.name) = '0' THEN '99999' ELSE LOCATE('6', REVERSE(q.name)) END,
CASE WHEN LOCATE('7', q.name) = '0' THEN '99999' ELSE LOCATE('7', REVERSE(q.name)) END,
CASE WHEN LOCATE('8', q.name) = '0' THEN '99999' ELSE LOCATE('8', REVERSE(q.name)) END,
CASE WHEN LOCATE('9', q.name) = '0' THEN '99999' ELSE LOCATE('9', REVERSE(q.name)) END
)
AS "last
FROM prefix_questionnaire AS q


If that isn't a problem, and you have a specific display format in mind for the availability conditions, please share it, for further advice.

Average of ratings: -