[mapserver-users] Templated for related data

[Johan Forsman]

All:

I have a case where I want to show data in a one-to-many relationship, but the related data are more than one table "away", or perhaps expressed as one-to-many-to-many.
For example, I have three tables: [sampling_point], [samples], [analytical_results].

[samples] is linked to [sampling_point] and
[analytical_results] is linked to [samples].
[samples] contains sample id, sampling date, etc., and
[analytical_results] contains the analytical names and results for each sample.

I want to map each sampling point once, then I want to display the related analytical data as a table so that the data are "grouped" first by sampling point, then by sample id + sampling date, approximately like so:

[sampling_point_id1]
    [samples_id1] + [samples_date1]
        [analyte_name1] + [analyte_result1]
        [analyte_name2] + [analyte_result2]
        [analyte_name3] + [analyte_result3]
    [samples_id2] + [samples_date2]
        [analyte_name1] + [analyte_result1]
        [analyte_name2] + [analyte_result2]
        [analyte_name3] + [analyte_result3]
[sampling_point_id2]
    [samples_id1] + [samples_date1]
        [analyte_name1] + [analyte_result1]

Etc.

I can successfully display the data at one level "shallower" by using the LAYER + JOIN structure and the associated templates, however I have only been able to accomplish this between one layer and one table, thus either losing the grouping of unique sample_id and associated sample_date, or including those fields with the analytical results and creating long displays of repeated id and date values for each analytical result like so:

[sampling_point_id1]
    [samples_id1] + [samples_date1] + [analyte_name1] + [analyte_result1]
    [samples_id1] + [samples_date1] + [analyte_name2] + [analyte_result2]
    [samples_id1] + [samples_date1] + [analyte_name3] + [analyte_result3]
    [samples_id2] + [samples_date2] + [analyte_name1] + [analyte_result1]
    [samples_id2] + [samples_date2] + [analyte_name2] + [analyte_result2]
    [samples_id2] + [samples_date2] + [analyte_name3] + [analyte_result3]
[sampling_point_id2]
Etc.

Please advise how I may accomplish this or redirect my thinking, whichever is necessary.

Thank you,
Johan

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20130610/77cc7561/attachment.htm>