Getting all sub records in a one-to-many relationship

I am running a query for my application, that is really making me wish I used ORM. My table are structured as follows:

tabs

• id
• name
• sort

fields

• id
• label
• tabid

As you can assume there is a one-to-many relationship between fields and tabs. What I would like to do is, using pure SQL if possible, create a query that has the tabs and underneath each tab shows a subquery of all fields.

Currently I am just doing the following, but I was wondering if there is something better to do.

<cfquery name="local.tabQuery" attributeCollection="#Variables.dsn#">
     SELECT id,name FROM tabs ORDER BY sort
</cfquery>
<cfset local.tabs = [] />
<cfloop query="local.tabQuery">
     <cfquery name="local.fields" attributeCollection="#Variables.dsn3">
          SELECT * FROM fields WHERE tabid = <cfqueryparam va开发者_开发百科lue="#local.tabQuery.id#" cfsqltype="cf_sql_integer" />
     <cfquery>
     <cfset arrayAppend(local.tabs, local.fields) />
</cfloop>

Note: That is not my actual code, but that should, in theory, work just fine.

You want grouped output.

<cfquery name="local.tabQuery" attributeCollection="#Variables.dsn#">
    SELECT t.id, t.name, t.sort, f.id AS fieldID, f.label
    FROM tabs t  INNER JOIN fields f ON t.id = f.tabID
    ORDER BY t.sort
</cfquery>

<cfoutput query="local.tabQuery" group="sort">
    Tab: #local.tabQuery.name#<br>

    <cfoutput>
        Field: #local.tabQuery.label#<br>
    </cfoutput>
</cfoutput>

You definitely want the CFOUTPUT group, feature, but I think you might want a join in your query. Unless there's something I'm not seeing, running a secondary CFQUERY inside a looped CFOUTPUT or other loop sounds like a lot of work for the database server. What about

SELECT T.id, T.name, F.id, F.label
FROM tabs T JOIN fields F ON F.tabid = T.id
ORDER BY T.sort, F.label

Then use the group CFOUTPUT clause based on the tab name field

<cfoutput query="local.tabQuery" group="name">
    Tab: #local.tabQuery.name#<br>

    <cfoutput>
        Field: #local.tabQuery.label#<br>
    </cfoutput>
</cfoutput>

If you always want to show all tabs, even if you don't have a field in it, make the JOIN a LEFT OUTER JOIN. Here's an example using the cfartgallery database that should be built into your development server. It shows all the artists and any works they may have.

<cfquery name="qryArtistsWithWorks" datasource="cfartgallery">
    SELECT A1.artistID, A1.lastname || ', ' || A1.firstname as artistName,
                A2.artID, A2.artName, A2.description
    FROM artists A1 LEFT OUTER JOIN art A2 ON A2.artistID = A1.artistID
    ORDER BY artistName, A2.artName
</cfquery>


<cfoutput query="qryArtistsWithWorks" group="artistId">

    <dl>
        <dt>#qryArtistsWithWorks.artistName#</dt>

        <cfoutput>
        <dd>#qryArtistsWithWorks.artName#</dd>
        </cfoutput>

    </dl>
</cfoutput>

Make sure the field you group on uniquely identifies the groups you want to build (i.e. no two records have the same value for 'sort'). You should generally use something like a primary key or a label field as your grouping attribute. If you don't, CF may group things together into one group in ways that you are not expecting.

In the example above, two artists might have the same first and last name, but their art works would not be collapsed into a single list.