• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Problems with cfoutput and grouping

Contributor ,
May 03, 2012 May 03, 2012

Copy link to clipboard

Copied

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#&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#&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?

Views

2.6K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
May 03, 2012 May 03, 2012

Copy link to clipboard

Copied

the syntax for grouping is:

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

     #column1#

     <cfoutput group="column2">

          #column2#

     </cfoutput>

</cfoutput>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
May 03, 2012 May 03, 2012

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
May 03, 2012 May 03, 2012

Copy link to clipboard

Copied

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>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
May 03, 2012 May 03, 2012

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
May 03, 2012 May 03, 2012

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
May 03, 2012 May 03, 2012

Copy link to clipboard

Copied

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!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
May 03, 2012 May 03, 2012

Copy link to clipboard

Copied

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>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
May 04, 2012 May 04, 2012

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
May 04, 2012 May 04, 2012

Copy link to clipboard

Copied

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!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
May 04, 2012 May 04, 2012

Copy link to clipboard

Copied

    > 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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
May 04, 2012 May 04, 2012

Copy link to clipboard

Copied

-==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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
May 04, 2012 May 04, 2012

Copy link to clipboard

Copied

LATEST

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

 

-Leigh

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation