Group attribute in Coldfusion not working right

Question

I'm trying to display options for homes and the categories in which they're in. Formatted in this way:

Category 1 Category 2
---------- ----------
option 1   Option 2

I have gotten very close. The options are showing up as expected, but the categories are repeating themselves. I've included an image.

I've tried all combinations of group in the CFOUTPUT and in the SQL with no success. I would appreciate some ideas. This is my current code:

<cfquery datasource="applewood" name="categorize">
    SELECT idOptions
           , options.option
           , options.idOpCategories
           , op_categories.categoryName
    FROM  options, op_categories
    GROUP BY idOptions
</cfquery>

 ...
<cfoutput query="categorize" group="idOpCategories">
    <h3>#UCASE(categoryName)#</h3>
    <ul>
        <cfoutput>
            <li>#option#</li>
        </cfoutput>
    </ul>
</cfoutput>

Update:

I ordered and grouped by the same column and getting this result:

Ordered and groupd by same column

You need to use ORDER BY in the SQL and GROUP in the cfoutput — Scott Stroz
@ScottStroz, your comment is the correct answer and should be posted as such. — Dan Bracuk
Not just any ORDER BY. You must sort the results by the same columns - in the same order - as your cfoutput group. Repeating generally means your query is not sorted properly. Can you please post your query? Also, what is "idOpCategories" and how does it relate to "CategoryName"? — Leigh
I am thinking of making a list of Top 10 most misunderstood CF features. cfoutput group will definitely make the cut ;-) — Leigh

Leigh Leigh · Accepted Answer · 2014-09-12T17:59:18

You are missing a JOIN clause between the "options" and "op_categories" tables, so the result is an implicit CROSS JOIN or cartesian product:

... it will produce rows which combine each row from the first table with each row from the second table

Hence the duplicates. You need to join the two tables on the related column ie the category id.

Also, as Scott mentioned in the comments, cfoutput group only works with sorted query results. However, the results must be sorted by the same columns - in the same order - as your grouping. Otherwise, it can create the appearance of duplicated categories. So if you are grouping by "CategoryName" first, you should order by "CategoryName" first.

   SELECT cat.categoryName, o.option, ...
   FROM   options o
             INNER JOIN op_categories cat
                ON cat.idOpCategories = o.idOpCategories
   ORDER BY cat.categoryName, o.option

   <cfoutput query="..." group="categoryName">
       ... more code ...
   </cfoutput>

(In this specific case, you could technically use the category id as well)

Group attribute in Coldfusion not working right

1 Answers