[xsl] Grouping problem

["Joel Dubien" <joel@xxxxxxxxxxxxxxx>]

Hello,

I'm having difficulty figuring out how to group an XML tree correctly using
XSLT 1.0 (using libxsl and php). Any help would be greatly appreciated.

Keep in mind that an affiliate doesn't NEED to have queries for each period.
There may be periods that are missing, but I need to output a 0 for those
periods. Also, a master_affiliate may contain a different set of affiliates
based on which affiliates actually have queries for that period. Affiliates
cannot be under more than one distinct master_affiliate however.

I do have a list of the periods in a variable:
$period_list:
<period_list>
    <period>2010-04-05 00</period>
    <period>2010-04-05 01</period>
    <period>2010-04-05 02</period>
    <period>2010-04-05 03</period>
</period_list>

The XML data will be in the desired order already, sorted by master_affiliate,
or by number of queries. I would prefer to not use XSLT to sort the data. The
only requirement is that the queries are ordered by period in the output.

XML:
<periods>
    <period index="2010-04-05 01">
        <master_affiliates>
            <master_affiliate index="68800">
                <master_affiliate_id>68800</master_affiliate_id>
                <affiliates>
                    <affiliate index="68801">
                        <affiliate_id>68801</affiliate_id>
                        <master_affiliate_id>68800</master_affiliate_id>
                        <queries>120</queries>
                        <period>2010-04-05 01</period>
                    </affiliate>
                </affiliates>
                <summary>
                    <queries>120</queries>
                    <period>2010-04-05 01</period>
                </summary>
            </master_affiliate>
            <master_affiliate index="69767">
                <master_affiliate_id>69767</master_affiliate_id>
                <affiliates>
                    <affiliate index="69775">
                        <affiliate_id>69775</affiliate_id>
                        <master_affiliate_id>69767</master_affiliate_id>
                        <queries>120</queries>
                        <period>2010-04-05 01</period>
                    </affiliate>
                </affiliates>
                <summary>
                    <period>2010-04-05 01</period>
                    <queries>120</queries>
                </summary>
            </master_affiliate>
        </master_affiliates>
        <summary>
            <period>2010-04-05 01</period>
            <queries>240</queries>
        </summary>
    </period>
    <period index="2010-04-05 00">
        <master_affiliates>
            <master_affiliate index="68800">
                <master_affiliate_id>68800</master_affiliate_id>
                <affiliates>
                    <affiliate index="68801">
                        <affiliate_id>68801</affiliate_id>
                        <master_affiliate_id>68800</master_affiliate_id>
                        <queries>75</queries>
                        <period>2010-04-05 00</period>
                    </affiliate>
                </affiliates>
                <summary>
                    <period>2010-04-05 00</period>
                    <queries>75</queries>
                </summary>
            </master_affiliate>
            <master_affiliate index="69767">
                <master_affiliate_id>69767</master_affiliate_id>
                <affiliates>
                    <affiliate index="69775">
                        <affiliate_id>69775</affiliate_id>
                        <master_affiliate_id>69767</master_affiliate_id>
                        <queries>75</queries>
                        <period>2010-04-05 00</period>
                    </affiliate>
                </affiliates>
                <summary>
                    <period>2010-04-05 00</period>
                    <queries>75</queries>
                </summary>
            </master_affiliate>
        </master_affiliates>
        <summary>
            <period>2010-04-05 00</period>
            <queries>150</queries>
        </summary>
    </period>
</periods>


Desired Output:
<item>
    <master_affiliate_id>68800</master_affiliate_id>
    <queries_by_period>75,120,0,0</queries_by_period>
    <subitems>
        <item>
            <affiliate_id>68801</affiliate_id>
            <queries_by_period>75,120,0,0</queries_by_period>
        </item>
    </subitems>
</item>
<item>
    <master_affiliate_id>69767</master_affiliate_id>
    <queries_by_period>75,120,0,0</queries_by_period>
    <subitems>
        <item>
            <affiliate_id>69775</affiliate_id>
            <queries_by_period>75,120,0,0</queries_by_period>
        </item>
    </subitems>
</item>

My first instinct was group by master_affiliate_id then by affiliate_id, but
that is beyond me.

XSLT:
    <xsl:key name="master_affiliates" match="master_affiliate"
use="master_affiliate_id"/>
    <xsl:key name="affiliates" match="affiliate" use="affiliate_id"/>
    <xsl:variable name="period_list" select="document('period_list.xml')"/>

    <xsl:template match="/">
        <xsl:for-each
select="/periods/period/master_affiliates/master_affiliate[
            count(. | key('master_affiliates', master_affiliate_id)[1]) = 1
            ]">
            <item>
                <master_affiliate_id><xsl:value-of
select="master_affiliate_id"/></master_affiliate_id>
                <queries_by_period>
                    <xsl:variable name="master_affiliate" select="."/>
                    <xsl:for-each select="key('master_affiliates',
master_affiliate_id)">
                        <xsl:for-each select="$period_list/*">
                            <!-- how do I select each period's queries for
this master_affiliate? -->
                        </xsl:for-each>
                    </xsl:for-each>
                </queries_by_period>
                <subitems>
                    <xsl:for-each select="affiliates/affiliate[count(. |
key('affiliates', affiliate_id)[1]) = 1]">
                        <item>
                            <affiliate_id>
                                <xsl:value-of select="affiliate_id"/>
                            </affiliate_id>
                            <queries_by_period>
                                <xsl:for-each select="key('affiliates',
affiliate_id)">
                                    <xsl:for-each select="$period_list/*">
                                        <!-- how do I select each period's
queries for this affiliate? -->
                                    </xsl:for-each>
                                </xsl:for-each>
                            </queries_by_period>
                        </item>
                    </xsl:for-each>
                </subitems>
            </item>
        </xsl:for-each>
    </xsl:template>


Thank you for your time,
Joel