Displaying 1:n Data in Grid

Code in this post can be obsolete, however, principles and theory may still apply.

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:

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

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:

' + val + '

and getPhones function:

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. The working example that accompanied this post is no longer online. The "many" display target does not need to be QuickTip, it can be row expander as well.