Many-to-many related record values in list view

[ADobkin]

What is the best way to display data from a many-to-many relationship in the list view? For example, in a table of contacts, displaying multiple phone numbers or e-mail addresses per contact in a single cell. In this scenario, there would be a small number of values, most likely in the range of 0 - 5.

Here are some options I've considered:

• __sql__ directive in fields.ini with carefully crafted joins to be sure the same number of records is returned as the default query
This seems like it would be the best option for performance, but I'm not sure how to write the query to capture multiple values in a single cell.


• fieldname__rendercell() in table delegate class with a mysql_query or getRelatedRecords() function
This should give more flexibility with the display/layout, but it seems like it would be a significant performance hit to run a separate query for each row in the list view.


• custom action with RecordList/RecordGrid

Any pointers or other options would be appreciated!

Alan

[shannah]

__sql__ directive in fields.ini with carefully crafted joins to be sure the same number of records is returned as the default query

This would be quite difficult to do.

fieldname__rendercell() in table delegate class with a mysql_query or getRelatedRecords() function

This would be problematic for performance because you would need to run a query for each row that is displayed (unless you're very clever).

custom action with RecordList/RecordGrid

This is a possibility. Or you could just override the result_list slot (to replace the list) - or you could specify a different template for the list action with the "template" directive of the actions.ini file (check out the existing list action definition to see what I mean).


The approach that I generally use in cases like this is to keep a cached version in a column of its own. Keep it in sync either with the afterSave() trigger or with a periodic action that updates it for every record. Sometimes I create a separate table for these types of values so that they aren't confused with the normal schema. Then I just do a left join with this table in the __sql__ directive.

-Steve

[ADobkin]

Thanks for the speedy response!

__sql__ directive in fields.ini with carefully crafted joins to be sure the same number of records is returned as the default query

This would be quite difficult to do.

I thought so too, but I think I found one that might work nicely, using the GROUP_CONCAT function. Do you see any problems with this?

Code: Select all

__sql__ = "SELECT c.contactid, GROUP_CONCAT(p.phoneNo SEPARATOR '<BR>') AS phoneNo
             FROM contacts c
             LEFT JOIN contacts_phones cp ON c.contactid = cp.contactid
             LEFT JOIN phones p ON cp.phoneid = p.phoneid
             GROUP BY c.contactid;"

It appears to output the same number of rows in the test cases I tried.

This is a possibility. Or you could just override the result_list slot (to replace the list) - or you could specify a different template for the list action with the "template" directive of the actions.ini file (check out the existing list action definition to see what I mean).

The approach that I generally use in cases like this is to keep a cached version in a column of its own. Keep it in sync either with the afterSave() trigger or with a periodic action that updates it for every record. Sometimes I create a separate table for these types of values so that they aren't confused with the normal schema. Then I just do a left join with this table in the __sql__ directive.

These all sound like great suggestions, and I will definitely try them if the __sql__ directive is problematic. But if you think it is okay, I would prefer to keep it simple.

Thanks,
Alan

[ADobkin]

I didn't go quite far enough with my testing before posting. Sadly, it looks like the GROUP_CONCAT function is not supported by the SQL parser. Would this be difficult to add, assuming this is a good method to tackle the issue?

Also, a couple of minor self-corrections. The beginning of the __sql__ directive should be SELECT c.* instead of c.contactid to include all fields, and there should not be a semi-colon at the end.

[ADobkin]

I noticed there is an entry in the bug tracker about this already, which was resolved in the trunk:

0000698: group_concat function is not recognized
http://bugs.weblite.ca/view.php?id=698

I added the group_concat function to the Dialect_MySQL file in my local copy of Xataface, and it is now working!

Also, it seems that using the <BR> tag as a separator in the SQL directive is filtered out. So, I changed it to use a comma instead, and then I added the following in the table delegate class:

Code: Select all

    function phoneNos__rendercell(&$record) {
        $phoneNos = $record->strval('phoneNos');
        $phoneNos = preg_split("/[\s,]+/", $phoneNos);
        $newCell = '';
        foreach ($phoneNos as $phoneNo) {
            if ($newCell) $newCell .= '<BR>&nbsp;';
            if (length($newCell) > 40) return $newCell . '(more)';
            $newCell .= $phoneNo;
        }
        return $newCell;
    }

[shannah]

Clever use of GROUP_CONCAT. This looks like a very elegant solution.

[ADobkin]

Great, thanks! And thanks for your other suggestions as well.