DML function for list as field like GROUP_CONCAT, FOR XML PATH('') ?

DML function for list as field like GROUP_CONCAT, FOR XML PATH('') ?

by Ray Morris -
Number of replies: 2

 

Occasionally, there is need to concatenate values from multiple rows in a query or subquery, like this query which shows which groups each user is in:

SELECT firstname, GROUP_CONCAT(groupid) AS groups
    FROM mdl_user
    LEFT JOIN mdl_groups_members ON mdl_groups_members.userid=mdl_user.id
    GROUP BY userid;

 

firstname groups
Ray 1,2
Guest User NULL

 

Note the "groups" field is a concatenation of a column from multiple rows.

MySQL has the GROUP_CONCAT function for that.  MS-SQL doesn't have a function designed for that purpose, so the common idiom is to use FOR XML PATH('').  is there a Moodle DML function to do that in a database-neutral way?

I've found many posts in this forum where people have been using GROUP_CONCAT, making their code not portable to Microsoft SQL and possibly others.  If such a function doesn't exist in our library, should it be added, so people can use it rather than database specific code?

 

 

Average of ratings: -
In reply to Ray Morris

Re: DML function for list as field like GROUP_CONCAT, FOR XML PATH('') ?

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

If you think you need GROUP_CONCAT then it is probably a sign that you don't understand relational databases.

GROUP_CONCAT is occasionally a useful hack in ad-hoc reports, but it should rarely be used for real.

Average of ratings: -
In reply to Tim Hunt

Re: DML function for list as field like GROUP_CONCAT, FOR XML PATH('') ?

by Ray Morris -

> If you think you need GROUP_CONCAT then it is probably a sign that you don't understand relational databases.

I started to address that in my original post, and perhaps I should have.  Certainly that's true when you WANT a relational data structure, such as in the business logic, so maybe 80% of the time that it's used, it shouldn't be.  Then there's the other 20%, when it's not being used wrong.

The other 20% of the time, when it's actually proper / okay to use in my opinion, fall into two categories, one when it's "acceptable", and one when it truly is the best way available. Sometimes, it may be acceptable when both a) you're in the presentation layer and that's what you're going to end up with anyway, and also b) doing it in the query is ten times faster than calling a PHP function in a loop.  A example of that would be as above, a list of users, one column of which is the groups they are in.  If you're writing the UI page to show that (not a library for user info), I think that's acceptable.  In that case, you WANT something easily human readable, you don't WANT a proper data structure any longer.

Where it may be the best choice available would be if there were a class called user_list, which does not show the groups.  You want to add a plugin to show the groups. Thankfully, class user_list does offer a method user_list->add_column() to add a cell to each row in the table.  So easy enough to add the "user's groups" column, right?  Except add_column() gets you one cell, for one value.  The list of groups isn't a single value, so you can either a) have your plugin return the group list as one value or b) rewrite the exist user_list class to allow each cell to contain an array of values, which it will then concatenate in PHP, accomplishing precisely the same result with far more work.

 

A particular instance of the above type of example that you are familiar with is question_bank_column->get_required_fields() - it expects one field, so GROUP_CONCAT(tags) might be the best way to do it.  The other option would be to get Tim involved in significant changes to how question_bank works, just so you could have a column for "Used by these quizzes", or "has these tags".

Wow I was long-winded in this post, but I don't feel like editing it to be shorter.

 

Average of ratings: -