Combining multiple fields

It is common to want to combine the data from several columns together when displaying a report.

Example

For example, let's say we wanted to make our course name column contain the name of the course, but linked to the course page. Then we need two database fields (the fullname column, but also the course id to let us build the link).

The best way to achieve this is to use another optional setting in the column option definition:

$this->columnoptions[] = new rb_column_option(
    'course',
    'fullnamelink',
    get_string('coursename'),
    'base.fullname',
    array(
        'displayfunc' => 'link_course_name',
        'extrafields' => array('courseid' =>'base.id')
    )
);

The new optional setting 'extrafields' stores an associative array which can contain any number of key-value pairs.

The key is an arbitrary name, used to reference each extra field. It can be anything you like, although you should avoid duplications within a source and names that match the format '[columntype_columnvalue]' to avoid conflicts with existing columns. The name you choose will be used in the display function to access the extra field.

The value in the associative array is an SQL snippet similar to the 4th argument to column options. Typically this will be of the form [tablename.fieldname] although more complex SQL syntax is permitted.

Although this lets you access multiple database fields, the primary field is still the one provided in the 4th argument to the column option. The primary column is the only one that will be used for filtering and sorting.

Here's what the display function to display linked course names might look like:

function rb_display_link_course_name($coursename, $row) {
    if($coursename === null) {
        // if no coursename found, return an empty string
        return '';
    }
    // access the extrafield 'courseid'.
    $courseid = $row->courseid;
    if($courseid) {
        // return the linked course name
        return '<a href=' . $CFG->wwwroot .
            '/course/view.php?id=' . $courseid . '">' . $coursename . '</a>';
    } else {
        // no course ID set, so return an unlinked course name
        return $coursename;
    }
}

The $row object will contain a property that matches the 'key' used in the column option definition (in this case 'courseid').

Note that if any additional table joins are required to access the extrafields, they should be included in the joins for the column option. Otherwise you cannot be sure that all the tables you need will be included in the query.

You might be tempted to create a report that defines two separate column options - course fullname and course id. If both columns were in a report, it would be possible to reference the course id column option from within the display function (through the $row object). But remember though that an administrator has full control over the report - if they decided to delete the ID column, the link would be broken. That's why it's best to use extrafields to ensure that the fields referenced by the display function are always available.