12 Replies Latest reply: May 4, 2012 1:55 PM by -==cfSearching==- RSS

    Problems with cfoutput and grouping

    BreakawayPaul Community Member

      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?

        • 1. Re: Problems with cfoutput and grouping
          ion

          the syntax for grouping is:

           

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

               #column1#

               <cfoutput group="column2">

                    #column2#

               </cfoutput>

          </cfoutput>

          • 2. Re: Problems with cfoutput and grouping
            BreakawayPaul Community Member

            Yes, that's the easy part.  But when I have a third group, that's when it gets tricky.

             

            Using your example, I created this:

            <cfoutput query="navedit" group="resource_id">

            <p>#resource_title#</p>

            <p>Roles:</p>

            <ul>

            <cfoutput group="role_id">

            <li>#role_name#</li>

            </cfoutput>

            </ul>

            <p>Areas</p>

            <ul>

            <cfoutput group="area_id">

            <li>#area_name#</li>

            </cfoutput>

            </ul>

            </cfoutput>

             

            Which gave me this:

            International Right of Way Association (IRWA)

            Roles:

            • Acquisition Agent
            • Appraiser
            • Relocation Agent

            Areas

            • Access Control
            • Negotiation Skills
            • Right-of-Way Engineering
            • Access Control
            • Negotiation Skills
            • Right-of-Way Engineering
            • Access Control
            • Negotiation Skills
            • Right-of-Way Engineering

             

            Each of the Areas in the second group are repeated x times, where x is the number of items in the first group.

             

            I also tried specifying role_id and area_id in the ORDER BY clause with no improvement.

            • 3. Re: Problems with cfoutput and grouping
              ion Community Member

              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>

              • 4. Re: Problems with cfoutput and grouping
                BreakawayPaul Community Member

                Roles and areas are not related.  Each resource can have multiple roles, and multiple areas, but the roles and areas have nothing to do with each other.

                 

                I'm starting to think maybe it would be easier to do this with three seperate queries.  The page works fine if I only have one subgroup, but it blows up when I add the second, even if I switch the order of the subgroups.

                • 5. Re: Problems with cfoutput and grouping
                  ion Community Member

                  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

                  • 6. Re: Problems with cfoutput and grouping
                    BreakawayPaul Community Member

                    Yep, I had tried that, and when I do that, the column that I list last is the one to display the multiples.

                     

                    There's either something fundemental that I'm missing, or you can't do more than one subgroup with a one-to-many relationship.

                     

                    Thanks for the second eye ion!

                    • 7. Re: Problems with cfoutput and grouping
                      -==cfSearching==- Community Member

                      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>

                      • 8. Re: Problems with cfoutput and grouping
                        ion Community Member

                        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

                        • 9. Re: Problems with cfoutput and grouping
                          BreakawayPaul Community Member

                          Thanks for the help everyone.  I manually searched for dupes in each table, but couldn't find any.

                           

                          I ended up doing three separate queries in the end.  All the other variations I tried have me inconsistent results.

                           

                          The app works perfectly now, and since it's internal, I'm not much worried about the performance hit of the 3 queries since only one person will be using the thing at any given time.

                           

                          Cheers!

                          • 10. Re: Problems with cfoutput and grouping
                            -==cfSearching==- Community Member

                                > 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

                            • 11. Re: Problems with cfoutput and grouping
                              BreakawayPaul Community Member

                              -==cfSearching==- wrote:

                               

                              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.

                              Well it may very well have worked, but by the time you posted it I had already tried the 3-query method, and I was too burnt out to change it.

                               

                              We've been one person short since mid-Feb, so I'm up to the ears in work.  But I've noted what you suggested on my "cheat sheet" in case I run into this again.

                              • 12. Re: Problems with cfoutput and grouping
                                -==cfSearching==- Community Member

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

                                 

                                 

                                -Leigh