1

We are trying to get filtered custom field data out of the Moodle database via SQL query. Unfortunately it's necessary to join multiple tables to get the data we need, because Moodle keeps its data this way. Also we can't manipulate the data structure. The database looks something like this (simplified):

// table mdl_customfield_field
ID  shortname
================
8   language
11  institutions

// table mdl_customfield_data
ID  fieldid instanceid  value
=============================
1   8       1           2
2   8       2           1
3   11      1           1,2
4   11      2           2,3
5   11      3           1

We tried to get every record that has language 1 AND institution 2 with the following code.

SELECT * FROM mdl_course c
JOIN mdl_customfield_data d ON c.id = d.instanceid
JOIN mdl_customfield_field f ON f.id = d.fieldid
WHERE (f.shortname = 'institutions' AND (d.value LIKE '%,2,%' OR d.value LIKE '2,%' 
OR d.value LIKE '%,2' OR d.value = '2'))
AND (f.shortname = 'language' AND d.value = '1')

Using an AND in the last line of the query, the result set does not contain the language data and an OR is showing too many results. How can we just get the course that satisfies both conditions in the result?

J. Unkrass
  • 730
  • 5
  • 16
  • Please ask 1 specific researched non-duplicate question. Please either ask re 1 bad query/function with obligatory [mre], including why you think it should return something else or are unsure at the 1st subexpression that it doesn't give what you expect or are stuck, justified by reference to authoritative documentation, or ask about your overall goal giving working parts you can do with justification & a [mre]. Then misunderstood code doesn't belong. But please ask about unexpected behaviour 1st because misconceptions get in the way of your goal. [ask] [Help] Basic questions are faqs. – philipxy May 26 '23 at 09:27
  • 1
    A [mre] includes: cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) – philipxy May 26 '23 at 10:34
  • Please refer to the official documentation : https://moodledev.io/docs/apis/core/dml As you'll can see you don't necessarily need to make a verbose SQL query as there is a lot of built in functions within the $DB global. For a more complex query, (like one with joins), there is also a proper way to write them. – Veterini Jun 07 '23 at 08:27

2 Answers2

1

How would we get every record that has language 1 AND institution 2?

This reads like a relational division problem. One option uses a correlated subquery that filters courses that have both fields available and set to the expected value:

select c.*
from mdl_course c
where (
    select count(*)
    from mdl_customfield_data d
    inner join mdl_customfield_field f on f.id = d.fieldid
    where d.instance_id = c.id
      and (
           ( f.shortname = 'institutions' and '2' = any string_to_array(d.value, ',') )
        or ( f.shortname = 'language'     and '1' = any string_to_array(d.value, ',') )
) = 2

You could easily expand the query to accommodate for more criteria, by adding more or predicates in the subquery and incrementing the expected count of matches accordingly (currently 2).

Note that storing a list of values in CSV format in a database column (as here in column value ot table mdl_customfield_field) is not good practice; there should be a separate table to store this 1-N relationship.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • 1
    I had to make a few small syntax adjustments, but this is exactly what we needed. Thank you very much. `select c.* from mdl_course c where ( select count(*) from mdl_customfield_data d inner join mdl_customfield_field f on f.id = d.fieldid where d.instanceid = c.id and ( ( f.shortname = 'institutions' and 2::varchar = any(string_to_array(d.value, ',') )) or ( f.shortname = 'language' and 1::varchar = any(string_to_array(d.value, ',') )) ) ) = 2` – J. Unkrass May 26 '23 at 09:52
  • I wouldn't have used the CSV value either, because it's not normalized. But unfortunately we have no control, because it comes from a third party Moodle plugin :/ – J. Unkrass May 26 '23 at 09:54
1

Maybe treat the institute and language as 2 tables

SELECT c.*
FROM mdl_course c
JOIN (
    SELECT d.instanceid AS courseid
    FROM mdl_customfield_data d
    JOIN mdl_customfield_field f ON f.id = d.fieldid AND f.shortname = 'language'
    WHERE d.value = '1'
) l ON l.courseid = c.id
JOIN (
    SELECT d.instanceid AS courseid
    FROM mdl_customfield_data d
    JOIN mdl_customfield_field f ON f.id = d.fieldid AND f.shortname = 'institutions'
    WHERE d.value ~ '\y2\y'
) l ON i.courseid = c.id

d.value ~ '\y2\y' the tilde is for matching regular expressions in SQL

The \y is used for word boundaries in postgresql, other databases use \b as the word boundary. This will search for a whole word or number, so 2 will match 2 but not 28 or 82. Useful for searching comma separated values in string fields.

You could also use d.value ~ '[[:<:]]2[[:>:]]'

If you are developing this in Moodle, then you could use Moodle's sql compatibility functions to make the SQL code generic

$regexp = $DB->sql_regex(true);
$regexwordbegin = $DB->sql_regex_get_word_beginning_boundary_marker();
$regexwordend = $DB->sql_regex_get_word_end_boundary_marker();

$where = "d.value {$regexp} '{$regexwordbegin}:institute{$regexwordend}'`

:institute pass as a parameter in Moodle

Russell England
  • 9,436
  • 1
  • 27
  • 41