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?
Copy link to clipboard
Copied
the syntax for grouping is:
<cfoutput query="myQuery" group="column1">
#column1#
<cfoutput group="column2">
#column2#
</cfoutput>
</cfoutput>
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.
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>
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.
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
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!
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>
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
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!
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
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.
Copy link to clipboard
Copied
I hear ya` on that. Guess you will need to clone yourself to get some assistance
-Leigh