Skip navigation
Currently Being Moderated

Problems with cfoutput and grouping

May 3, 2012 10:03 AM

I'm trying to build a back-end editing page for a web app that we have.  It's basically a search tool where you put in some criteria, and it gives you results in the form of resources with URLs.

 

The web app in question lives here: http://www.fhwa.dot.gov/real_estate/practitioners/rcb_navigator/

 

You start by choosing your role, then picking your resource type, then your competency area, then your level of understanding (noob, expert, or in-between).

 

Each resource has one type (first step), and a fixed level of understanding (last step), but it can have many roles or areas.

 

The back-end page I'm building is basically a form that gets filled out by a query, and that you will eventually be able to make changes to and submit.  On the page is a bulleted list of roles, and another one for areas, and that's what I'm having problems with.  The first bulleted list displays fine, but the second one repeats for the number of entries in the first.  So, for instance if there are 3 roles and 4 areas, each of the 4 areas repeat 3 times.

 

I have been fiddling with it all morning, and I'm sure it's something stupid.  Here's what I have:

 

Query:

 

<cfquery name="navedit" datasource="navigator">
SELECT       resources.resource_id
        , resources.resource_title
        , resources.resource_type
        , resources.resource_url
        , resources.resource_url_exit
        , resources.resource_desc
        , resources.resource_provider
        , resources.provider_url
        , resources.provider_url_exit
        , resources.resource_roles
        , resources.resource_level
        , types.type_id
        , types.type_name
        , roles.role_id
        , roles.role_name
        , areas.area_id
        , areas.area_name
FROM ( ( ( ( ( resources
INNER JOIN types
        ON resources.resource_type = types.type_id
        )
INNER JOIN resc_roles
        ON resc_roles.rr_resource_id = resources.resource_id
        )
LEFT JOIN roles
        ON roles.role_id = resc_roles.rr_role_id 
        )
INNER JOIN resc_areas
        ON resc_areas.ra_resc_id = resources.resource_id
        )
LEFT JOIN areas
        ON areas.area_id = resc_areas.ra_area_id
        )
WHERE resource_id = <cfqueryparam value="#URL.id#" cfsqltype="cf_sql_numeric">
</cfquery>

 

And here's the form:

<cfform name="edit_resc" method="post" action="navedit.cfm">
<table border="0" cellspacing="5">
<tr>
<td><label for="resourcetitle">Resource Title:</label></td>
<td><cfinput type="text" class="cf_textinput" name="resourcetitle" size="70" value="#navedit.resource_title#" /></td>
</tr>
<tr>
<td><label for="resourceurl">Resource URL:</label></td>
<td><cfinput type="text" class="cf_textinput" name="resourceurl" size="110" value="#navedit.resource_url#" /><input type="checkbox" class="cf_textinput" name="resourceurlexit" id="resourceurlexit"<cfif navedit.resource_url_exit eq 1> checked="checked"</cfif> /><label for="resourceurlexit">Exit Door</label></td>
</tr>
<tr>
<td><label for="resourceprovider">Resource Provider:</label></td>
<td><cfinput type="text" class="cf_textinput" name="resourceprovider" size="70" value="#navedit.resource_provider#" /></td>
</tr>
<tr>
<td><label for="provider_url">Resource Provider URL:</label></td>
<td><cfinput type="text" class="cf_textinput" name="provider_url" size="110" value="#navedit.provider_url#" /><input type="checkbox" class="cf_textinput" name="providerurlexit" id="providerurlexit"<cfif navedit.provider_url_exit eq 1> checked="checked"</cfif> /><label for="providerurlexit">Exit Door</label></td>
</tr>
<tr>
<td><label for="resourcetype">Resource Type:</label></td>
<td>
<select name="resourcetype" id="resourcetype">
<cfoutput query="list_types"><option value="#type_id#"<cfif type_id eq navedit.type_id> selected="selected"</cfif>>#type_name#</option>
</cfoutput>
</select></td>
</tr>
<tr>
<td valign="top"><label for="pickroles">Roles:</label></td>
<td valign="top">
<ul>
<cfoutput query="navedit" group="role_id"><li>#role_name# <a href="navedit.cfm?delrole=#role_id#&amp;resc=#resource_id#">X</a></li></cfoutput>
</ul>
<select name="pickroles" id="pickroles">
<cfoutput query="addroles"><option value="#role_id#">#role_name#</option>
</cfoutput>
</select>
<input type="submit" name="submitrole" value="add" /></td>
</tr>
<tr>
<td valign="top"><label for="pickareas">Areas:</td>
<td valign="top">
<ul>
<cfoutput query="navedit" group="area_id"><li>#area_name# <a href="navedit.cfm?delarea=#area_id#&amp;resc=#resource_id#">X</a></li></cfoutput>
</ul>
<select name="pickareas" id="pickareas">
<cfoutput query="addareas"><option value="#area_id#">#area_name#</option>
</cfoutput>
</select>
<input type="submit" name="submitarea" value="add" /></td>
</tr>
<tr>
<td colspan="2" valign="top"><hr /></td>
</tr>
<tr>
<td colspan="2" align="center" valign="top"><input type="submit" name="submitall" value="Done" /></td>
</tr>
</table>
</cfform>

 

I need to figure out how to stop the second bulleted list from repeating.  Any ideas?

 
Replies
  • Currently Being Moderated
    May 3, 2012 10:07 AM   in reply to BreakawayPaul

    the syntax for grouping is:

     

    <cfoutput query="myQuery" group="column1">

         #column1#

         <cfoutput group="column2">

              #column2#

         </cfoutput>

    </cfoutput>

     
    |
    Mark as:
  • Currently Being Moderated
    May 3, 2012 10:43 AM   in reply to BreakawayPaul

    if for each role you can have several areas, just nest that one too, like:

     

    <cfoutput query="myQuery" group="column1">

         #column1#

         <cfoutput group="column2">

              #column2#

              <cfoutput group="column3">

                   #column3#

              </cfoutput>

         </cfoutput>

    </cfoutput>

     
    |
    Mark as:
  • Currently Being Moderated
    May 3, 2012 11:18 AM   in reply to BreakawayPaul

    you can try adding an ORDER BY clause, listing the columns EXACTLY in the same order you want them displayed,  like:

     

    ORDER BY resource_id, role_id, area_id

     
    |
    Mark as:
  • Currently Being Moderated
    May 3, 2012 10:46 PM   in reply to BreakawayPaul

    role and areas are not related.

     

    That is why the "group" is not working the way you want. The "group" feature works very simply. It just outputs the value whenever the grouped column value changes.  For it to generate the desired results the data in both columns would have to be sorted simultaneously, which obviously is not possible in sql. You can only sort results by one column then further sort by another. So the values in one of your columns will always jump around, resulting in repeating values when you use "group".

     

    ie

                   Column 1 | Column 2

                   a             | x **                           <<  since the values in Column 2 change this would output "x" and "y" twice

                   a             | y **

                   b             | x **

                   b             | y **

                   c             | z

     

          ...  grouped results

     

               Column 1             Column 2 (repeats)

                - a                      - x  

                - b                      - y

                - c                      - x

                                          - y

                                          - z

     

    A work around is to use a structure when outputting the last column to supress the duplicates. Something along these lines

     

                     SELECT ....

                     ORDER BY columnA, columnB, columnC

     

              <cfoutput query="queryName" group="columnA">

                    #columnA#

     

                   <cfoutput>

                        <li>#columnB#</li>

                   </cfoutput>

     

                   <cfset seen = structNew()>

                   <cfoutput>

                        <cfif not structKeyExists(seen, columnC)>

                            <li>#columnC#</li>

                            <cfset seen[columnC] = true>

                        </cfif>

                   </cfoutput>

              </cfoutput>

     
    |
    Mark as:
  • Currently Being Moderated
    May 4, 2012 6:36 AM   in reply to -==cfSearching==-

    i think the only way to get duplicates in the second (or third) column will be to have duplicates in the first. check your query and make sure you don't

    i've built this simple table as an example:

     

    column1 column2 column3

    val1        val2-1    val3-1

    val1        val2-2    val3-2

    val2        val2-1    val3-2

    val2        val2-3    val3-3

     

    <cfquery name="test">

    select column1, column2, column3 from testGroup

    </cfquery>

    <cfoutput query="test" group="column1">
    #column1#
    <p>
    <cfoutput group="column2">
        #column2# <br>
    </cfoutput>
    <p>
    <cfoutput group="column3">
        #column3# <br>
    </cfoutput>
    <p>
    </cfoutput>

     

    running the code above returns:

     

    val1 

      val2-1
      val2-2
     

      val3-1
      val3-2
     

    val2 

      val2-1
      val2-3
     

      val3-2
      val3-3

     
    |
    Mark as:
  • Currently Being Moderated
    May 4, 2012 8:07 AM   in reply to BreakawayPaul

        > I manually searched for dupes in each table, but couldn't find any.

     

    Yeah, you wouldn't There are no duplicates records. The "group" just makes it "seem" that way because of how the query data is sorted.

     

     

        >  All the other variations I tried have me inconsistent results.

        > I ended up doing three separate queries in the end.

     

    I have never had any problems with the structure method. So I suspect the sorting of your query sorting was off. But glad you found something that worked.

     

     

    -Leigh

     
    |
    Mark as:
  • Currently Being Moderated
    May 4, 2012 1:55 PM   in reply to BreakawayPaul

    I hear ya` on that. Guess you will need to clone yourself to get some assistance

     

     

    -Leigh

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points