Sunday, March 5, 2006, 8:31:49 AM, one spoke:
PB> I don't suppose you can think of any solution to the following?
PB> Say you have an InsuredPerson with ..n dependents and ..n
PB> addresses (no association between the dependents and the
PB> addresses) so I'd like to display:
Is it really ..n addresses? Your previous email indicated "shipping"
and "billing", which is slightly different; you presumably only want
one of each, and to know which is which.
If that's the case, you can flatten the "addresses" portion by joining
twice to the addresses table and use a single level of grouping in the
<cfoutput>:
SELECT InsuredPerson.FirstName,
Dependent.FirstName AS DependentFirstName, [etc.]
ShipAddress.Address1 AS ShipAddress1, [etc.]
BillAddress.Address1 AS BillAddress1, [etc.]
FROM
InsuredPerson
INNER JOIN Dependent
ON InsuredPerson.ID = Dependent.InsuredPersonID
INNER JOIN Address ShipAddress
ON (InsuredPerson.ID = ShipAddress.InsuredPersonID
AND ShipAddress.AddressType = 'S' )
INNER JOIN Address BillAddress
ON (InsuredPerson.ID = BillAddress.InsuredPersonID
AND BillAddress.AddressType = 'B' )
WHERE [etc.]
ORDER BY InsuredPerson.LastName,InsuredPerson.FirstName,
InsuredPerson.ID,
Dependent.[etc.]
This wouldn't be necessary, of course, if the relationship was
Dependent:Address (although that would still require a second join for
InsuredPerson:Address).
This obviously wouldn't work if you just had a slew of untyped
addresses for InsuredPerson (or, for that matter, Dependent). In that
case, you'd need to add some <cfif> logic to the <cfoutput> to only do
the inmost <cfoutput> once, but that'd vary depending on e.g. whether
you wanted the inner loop on the first iteration of the middle loop or
the last one, whether your outmost entity was a single record (is this
a loop through many InsuredPersons or just a single one), etc. One
example (displaying the addresses on the first loop thru dependents):
<cfoutput group="ID">
<cfset Insured_rownum = currentrow>
<cfoutput group="DependentID">
<cfif Insured_rownum EQ currentrow>
<cfoutput>
</cfoutput>
</cfif>
</cfoutput>
</cfoutput>
(Note on the "multiple InsuredPersons" scenario -- if there's a chance
that there could be three "Bill Smith"s in the query, it can a good
idea to include a SortKey at each level in your SELECT and ORDER, e.g.
InsuredPerson.LastName+InsuredPerson.FirstName+InsuredPerson.ID
AS InsuredPersonSortKey
[...]
ORDER BY InsuredPersonSortKey,DependentSortKey
and use *those* fields in your <cfoutput>
<cfoutput group="InsuredPersonSortKey">
<cfoutput group="DependentSortKey">
<cfoutput>
OR, (in this example) you can just use the ID
ORDER BY
InsuredPerson.LastName,InsuredPerson.FirstName,InsuredPerson.ID
outmost tag: <cfoutput group="ID">
... otherwise you could wind up with the child records randomly
distributed amongst the parent.)
I feel like I should point out, here, that <cfoutput> is not just
for display templates; you could well wind up doing the same sort of
grouped cfoutput whilst creating your object (some shorthand here ;-)):
SELECT InsuredPerson.*, DependentID
<cfoutput query="getInsuredPerson" group="ID">
<cfif IsNumeric(DependentID)>
<cfset this.Dependents = ArrayNew(1)>
<cfoutput group="DependentID">
<cfset ArrayAppend(this.Dependents,
CreateObject("dependent").init().load(DependentID))>
</cfoutput>
</cfif>
</cfoutput>
--
Jim Flannery
[EMAIL PROTECTED]
----------------------------------------------------------
You are subscribed to cfcdev. To unsubscribe, send an email to
[email protected] with the words 'unsubscribe cfcdev' as the subject of the
email.
CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting
(www.cfxhosting.com).
An archive of the CFCDev list is available at
www.mail-archive.com/[email protected]
