Learn from Saki

Knowledge is power

This content is for registered users only. Please login.
Hello! To access your account, please Log in. Not a member? Sign up
  • Blog
  • Examples
  • Services
  • Add-ons
  • About

Displaying 1:n Data in Grid

May 11, 2008 by saki 9 Comments

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:

1
2
3
4
5
6
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

1
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
/**
* 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 '

‘ + val + ‘

1
2
';
} // eo function renderLastName

and getPhones function:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
/**
* 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.

  • About
  • Latest Posts
Follow me:

saki

I'm a well seasoned developer, consultant and educator of web applications based mainly on Sencha libraries, PHP, MySQL and Node.js. Besides (Apple) computers, I love photography and mountain biking.
Follow me:

Latest posts by saki (see all)

  • The Site Resurgence - February 11, 2018
  • Configuring ViewModel Hierarchy - June 19, 2015
  • Localization of Ext Applications - April 14, 2015

Filed Under: Know-how Tagged With: example, extjs, javascript, mysql, php, sql, sqlite

Comments

  1. Lobos says

    May 14, 2008 at 9:02 pm

    Wow I had never heard of group_concat, so I have learned something new today! Thanks for the great article!

    Log in to Reply
  2. mjlecomte says

    May 16, 2008 at 8:37 pm

    Saki, if you don’t mind, a database question. Do you use your application or the database to enforce relationships between tables? Meaning if you have mysql tables that would have foreign keys in other tables, etc. do you let the database handle the on cascade stuff to enforce the table relations when records are added/deleted, or do you enforce this with your application/php logic? I’m not sure which way to go with this and wanted your wisdom on the matter.
    MJ.

    Log in to Reply
  3. Saki says

    May 16, 2008 at 11:42 pm

    Yes MJ,

    I use relationships of InnoDB Engine of mySQL. I always use ON UPDATE CASCADE and selectively, when appropriate, ON DELETE CASCADE.

    Of course, PHP and/or Ext have to take into account these relationships, e.g. Ext has to supply parent ID when updating child record but mySQL takes additional care about data integrity.

    Cheers,
    Saki

    Log in to Reply
  4. Jeff says

    May 17, 2008 at 8:38 am

    Hi Saki,

    Nice work. I was wondering if you have ever used Perl with EXTJS? The work that I do does not allow me to use PHP. Although I do use it for other projects. I am interested in seeing some examples of how Perl could be used with EXTJS in conjunction with JSON.

    Any thoughts?

    Log in to Reply
  5. Saki says

    May 17, 2008 at 10:42 am

    Hi Jeff,

    no I don’t use Perl but I think that it wouldn’t be too different. I believe that there has to be some good JSON encode/decode in Perl so it boils down to find one.

    I would first search rpms of your distribution (assuming you are using Linux on your server) and second, I would google for it.

    Cheers,
    Saki

    Log in to Reply
  6. James says

    September 3, 2008 at 4:42 am

    Hi, Saki,

    Cool, but i found a question. It will display “[object Object]” when sort columns. Is it a bug?
    What do you think?

    Log in to Reply
  7. Saki says

    September 15, 2008 at 9:06 am

    @James, it seem to be, however, it doesn’t invalidate the idea. Just some more logic is needed.

    Log in to Reply
  8. Vikas says

    November 4, 2011 at 9:21 am

    Hi Saki,

    I have to implement this with extjs 4.0.2 but couldnt found the code working with it.

    Can you please suggest if any more changes need to be done.

    Thanks,
    Vikas Kapoor

    Log in to Reply
  9. Chandrakanth Bairy says

    August 29, 2012 at 3:03 pm

    Hi Saki,
    I am new to ExtJs. I am trying to build a treepanel where my store uses a json file for data.
    Json file goes like this,
    {
    “data”: [
    {
    “id”: “1”,
    “title”: “All”,
    },
    {
    “id”: “2”,
    “title”: “Actors/Actresses”,
    },
    {
    “id”: “3”,
    “parentId”: “2”,
    “leaf”: true,
    “title”: “Actors”
    },
    {
    “id”: “4”,
    “parentId”: “2”,
    “leaf”: true,
    “title”: “Actresses”
    },
    {
    “id”: “9”,
    “title”: “Favorites”
    },
    {
    “id”: “10”,
    “parentId”: “9”,
    “leaf”: true,
    “title”: “Favorite Actors”
    },
    {
    “id”: “12”,
    “parentId”: “9”,
    “leaf”: true,
    “title”: “Favorite Actresses”
    },
    {
    “id”: “21”,
    “title”: “Cast By”
    },
    {
    “id”: “22.94”,
    “parentId”: “21”,
    “title”: “Administrators”
    },
    {
    “id”: “23.94.4”,
    “parentId”: “22.94”,
    “userTitle”: {
    “id”: 4,
    “firstName”: “Chan”,
    “lastName”: “Bairy”,
    “email”: “chandrakanth.bairy@gmail.com”,
    “jobTitle”: “Dev”
    }
    }
    ]
    }

    Since, this json file is flat, I am not able to get the children in the tree.
    Any suggestion please?

    Log in to Reply

We will be happy to hear back from you Cancel reply

You must be logged in to post a comment.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Categories

  • Addons (2)
  • Architecture (14)
  • Examples (2)
  • ExtJS (26)
  • Howtos (15)
  • Javascript (1)
  • Know-how (32)
  • Linux (1)
  • Mac OS X (2)
  • SASS/CSS (2)
  • Snippets (9)
  • Theory (14)
  • Touch (6)
  • Tutorials (5)
  • What is a … (9)

Tag cloud

abstract class accordion application button class cluster column component config css definition deprecated design education event example extension extjs factory function form grid html initComponent items javascript Know-how knowledge layout Linux listener mysql old panel pattern php plugin render snippet sql sqlite state table touch tree viewpoint

Membership

Become a Member
Affiliate Program

Support

FAQ
Contact

Legal

Terms and Conditions
Licensing
Privacy Policy

Copyright © 2019 · Dynamik Website Builder on Genesis Framework · WordPress · Log in