A basic join

Our hello world source now makes it possible to display any database fields from the course table, but currently there is no way to get data from any other table in the database.

Adding a basic join

For instance, what if we wanted to add a column that displayed the course category along side the course name? The name of the course category is stored in a different table:

mdl_course:
id   |    fullname    |   category
1    |   My Course    |   1

mdl_course_categories:
id   |  name
1    | Miscellaneous

We need to add a join to the joinlist, so report builder knows how to correctly connect the base table to the 2nd table. We do this by adding a join object to the joinlist array:

$joinlist = array(
    new rb_join(
        'course_category',
        'LEFT',
        '{course_categories}',
        'course_category.id = base.category',
    )
);

The rb_join method takes four required arguments:

• Join name: A unique string, used to identify this join
• Join type: Describes the type of join (e.g. LEFT OUTER, RIGHT OUTER, INNER) - this effects how the records are combined (see http://en.wikipedia.org/wiki/Join_(SQL)
• Table name: The full name of the table to join to
• Join condition: This describes which fields the tables should be joined on. Use the Join name to describe other tables, or 'base' for the base table. In this case we are joining the 'category' field of the base (course) table, to the 'id' field of the course_categories table.

Once the join has been defined, column options can make use of fields from the joined tables, by specifying which joined tables they will use. For example, to include a column option which displays the course category:

new rb_column_option(
    'course',
    'category',
    get_string('category'),
    'course_category.name',
    array(
        'joins' => 'course_category'
    )
);

Here the field name uses the syntax '[join name.field name]' to define the desired field. Any additional optional parameter 'joins' is added which lists which joins must be made to get access to this field. The 'joins' parameter can take a join name or an array of join names if you need to join multiple tables.

If you add the join and column option code to the Hello World source file and save it, you should see a new column option appear when you edit the report.