Displaying 1:n Data in Grid

The Problem

Imagine that you have a one-to-many relationship in your database, for example, you have table person in which you keep personal data (first, middle, last names, etc.) and you have table phone where you keep phone numbers (phone type, phone number).

It is quite common to have person:phones, company:phones, order:items, invoice:items, etc relationships, isn’t it?

Now, it is quite easy to create a grid that displays list of persons but what about their phones? They are in the different table. Yes, we could create two stores: one for persons grid and another, hidden, for phones, load them from server and somehow filter phones depending on persons.

Nevertheless, I was looking for a simpler solution as I wanted one client server round trip and I wanted to display “many” data in QuickTip. And I found one…

Solution – Server Side

MySql, that I use as my main database backend, has function group_concat since version 4.1 and SQLite has it since version 3.5.

The idea is to join tables person and phone server side and return “many” data in one extra field as string separated by arbitrary separators. The SQL statement would be as follows:

SELECT 
    persFirstName, persMidName, persLastName,
    group_concat(concat_ws('~', phoneType, phoneNumber), '|') AS phones
FROM person
    LEFT JOIN phone ON person.persID=phone.persIDs
GROUP BY person.persID

phones part of the output of the above sql would look like

Home~123456|Work~87654|Mobile~654321

Solution – Client Side

We cannot display received phones directly in person grid (well we could but users would hate us) but we need some processing. I decided to display phones in QuickTips so I needed custom renderer for persLastName:

/**
 * Last Name rederer including tooltip with phones
 * @param {Mixed} val Value to render
 * @param {Object} cell
 * @param {Ext.data.Record} record
 */
,renderLastName:function(val, cell, record) {
 
    // get data
    var data = record.data;
 
    // convert phones to array (only once)
    data.phones = 
        Ext.isArray(data.phones) ? data.phones : this.getPhones(data.phones);
 
    // create tooltip
    var qtip = this.qtipTpl.apply(data.phones);
 
    // return markup
    return '<div qtip="' + qtip +'">' + val + '</div>';
} // eo function renderLastName

and getPhones function:

/**
 * Converts string phones to array of objects
 * @param {String} phones
 * @return {Array} Array of phone objects
 */
,getPhones:function(phones) {
 
    // empty array if nothing to do
    if(!phones) {
        return [];
    }
 
    // init return value
    var retval = [];
 
    // split string to phones
    var aps = phones.split('|');
 
    // iterate through phones to extract phoneType and phoneNumber
    Ext.each(aps, function(phone) {
        var a = phone.split('~');
        retval.push({phoneType:a[0], phoneNumber:a[1]});
    });
 
    return retval;
} // eo function getPhones

A bit of XTemplate work for QuickTips and we’re done.

Conclusion

This is not full fledged one-to-many data handling with editing, adding and deleting items at “many” side, it is just simple display of data from “many” table, anyway, it can come handy sometimes.

You can see the working example here: http://examples.extjs.eu

The “many” display target does not need to be QuickTip, it can be row expander as well.