Recursive Relation 1

[Extension]

Hi,

I'm trying to produce a nested list based on a 3-level relation.

Table:

+-------+---------------+---------------+-------+
|Level1	|Level2		|Level3		|Name	|
+-------+---------------+---------------+-------+
|US	|Vermont	|Burlington 	|City	|
+-------+---------------+---------------+-------+
|US	|Vermont	|Winchester	|City	|
+-------+---------------+---------------+-------+
|US	|Vermont	|		|State	|
+-------+---------------+---------------+-------+
|US	|New York	|Brooklyn	|City	|
+-------+---------------+---------------+-------+
|US	|New York	|Manhattan	|City	|
+-------+---------------+---------------+-------+
|US	|New York	|		|State	|
+-------+---------------+---------------+-------+
|CA	|Alberta	|Lake Town	|City	|
+-------+---------------+---------------+-------+
|CA	|Alberta  	|		|State	|
+-------+---------------+---------------+-------+

Output: (Which I want to achieve)

- US
  - Vermont
    - Burlington
    - Winchester

  - New York
    - Brooklyn
    - Manhattan

- CA
  - Alberta
    - Lake Town

Query:

<cfquery name="Levels" datasource="#Source#">
	SELECT * 
	FROM RELATION
	WHERE Level3 = Level2
	ORDER BY Level1

</cfquery>

Code:

[/code]

<cfoutput query="Levels" group="Level1">

- #Level1#
-#Level2#
-#Level3#

</cfquery>

[/code]

Thank you in advance



PS. I hope you can see the table properly

 

[Extension]

boomboy

What about using the group function to group Level1 and a cfloop for Level2 and Level3 ??

Thank you

I'll owe you alot of stars for this one...

 

[TruthInSatire]

it would probably be easier to put all the query results into arrays and loop through them.

If you don't ask the right questions, you don't get the right answers. A question asked in the right way often points to its own answer. Asking questions is the ABC of diagnosis. Only the inquiring mind solves problems.

-Quote by Edward Hodnett

 

[Extension]

bombboy

Can you guide me with the array technique. Any help would be appreciated.

*

 

[TruthInSatire]

Before we go that rout lets try some other alternatives.

first try

<cfoutput query = "levels" group = "level1">
  -#level1#
  <cfoutput group = "level2">
    &nbsp;&nbsp;-#level2# (ID #ID[b][currentRow][/b]#)
    <cfoutput group = "level3">
      <cfif len(trim(level3))>
        &nbsp;&nbsp;&nbsp;-#level3# (ID #ID#)
      </cfif>
    </cfoutput>
  </cfoutput>
</cfoutput>

try [currentRow] to [currentRow-1] and [currentRow+1] and see if you get the answers you're looking for. If not, we'll do a "q of q" to get the id for the first level 2.

If you don't ask the right questions, you don't get the right answers. A question asked in the right way often points to its own answer. Asking questions is the ABC of diagnosis. Only the inquiring mind solves problems.

-Quote by Edward Hodnett

 

[Extension]

bombboy
Thank you for your help.

#ID[currentRow]#-> Same results
#ID[currentRow-1]#-> Getting an error
#ID[currentRow+1]#-> Getting an error

So the query-of-query would be another option...

Thanks

 

[TruthInSatire]

ok fine.

original query

<cfquery name="Levels" datasource="#Source#">
    SELECT *
    FROM RELATION
    WHERE 0=0
    ORDER BY Level1, Level2, Level3
</cfquery>

new code (untested you may have to play with it)

[b]
<cfset thisGroup = levels.group2>
<cfquery name = "levelID" dbtype = "query">
SELECT id
from   levels
where  level2 = '#thislevel#' and level 3 is null
</cfquery>[/b]
<cfoutput query = "levels" group = "level1">
  -#level1#
  [b]<cfoutput group = "level2">
    <cfif thisGroup neq level2>
      <cfset thisGroup = level2>
      <cfquery name = "levelID" dbtype = "query">
        SELECT id
        from   levels
        where  level2 = '#thislevel#' and level 3 is null
       </cfquery>
     </cfif>[/b]
    &nbsp;&nbsp;-#level2# (ID #[b]levelID.ID[/b]#)
    <cfoutput group = "level3">
     <cfif #level3# gt 0>
     &nbsp;&nbsp;&nbsp;-#level3# (ID #ID#)
     </cfif>
    </cfoutput>
  </cfoutput>
</cfoutput>

If you don't ask the right questions, you don't get the right answers. A question asked in the right way often points to its own answer. Asking questions is the ABC of diagnosis. Only the inquiring mind solves problems.

-Quote by Edward Hodnett

 

[Extension]

I'm using a "q of q" solution similar to yours. I'll try to work on a less "query-dependant" solution later you.
Thank you for your help. It's so appreciated. Your are helpful for alot of people here. Continue your great work.

Again, thank you

 

[TruthInSatire]

it might sound like a lot of work but you're base query is in memory so it doesn't have to make the trip to the DB, just memory where the query is stored. if you wrap it in the cfif like i had, it should only do the q of q once for each level 2 group. overall it isn't adding too much crunch time to your process.

If you don't ask the right questions, you don't get the right answers. A question asked in the right way often points to its own answer. Asking questions is the ABC of diagnosis. Only the inquiring mind solves problems.

-Quote by Edward Hodnett

 

[Extension]

One last question. How can I sort the list based on the ID Number (DESC). In order to make the grouping work, I have to ORDER BY LEVEL1, LEVEL2, LEVEL3. But what about if I want to sort the list by the ID field.

Thank you in advance.

 

[TruthInSatire]

your id fields would have to be in order by level. if not, that wouldn't work no matter how you do it, cfoutput or any kind of cfloops. sorry

If you don't ask the right questions, you don't get the right answers. A question asked in the right way often points to its own answer. Asking questions is the ABC of diagnosis. Only the inquiring mind solves problems.

-Quote by Edward Hodnett

 

[TruthInSatire]

when i mean "wouldn't work" i mean you wouldn't get errors but you'd get an output that looks like this (ignoring the real id this is just an example).

- US
  - Vermont (ID 12)
    - Burlington (ID 12)


  - New York (ID 22)
    - Brooklyn (ID 22)
    - Manhattan (ID 21)

-US
  -Vermont (ID 12)
    - Winchester (ID 11)

- CA
  - Alberta (ID 31)
    - Lake Town (ID 31)

if your id's are in the right order you wont have a problem.

all it does is groups the results that are the same right next to each other. if your ID jumps all over you'll get strange results.

If you don't ask the right questions, you don't get the right answers. A question asked in the right way often points to its own answer. Asking questions is the ABC of diagnosis. Only the inquiring mind solves problems.

-Quote by Edward Hodnett

 

[TruthInSatire]

oops forgot to change the ID of winchester

-US
  -Vermont (ID 12)
    - Winchester (ID 25)

If you don't ask the right questions, you don't get the right answers. A question asked in the right way often points to its own answer. Asking questions is the ABC of diagnosis. Only the inquiring mind solves problems.

-Quote by Edward Hodnett

 

[Extension]

Well since the ID are not stored in a specific order, I get strange results when I put

<cfquery name="Levels" datasource="#Source#">
    SELECT *
    FROM RELATION
    WHERE 0=0
    ORDER BY [b]ID DESC,[/b] Level1, Level2, Level3
</cfquery>

Any other way to solve this ?

 

[TruthInSatire]

you'll have to reasign your id's to be in the right order. (yeah right). otherwise, nope, not if you want to group it like that.

note: this isn't a cf problem you couldn't do that with any language, that's how it works logicaly.

If you don't ask the right questions, you don't get the right answers. A question asked in the right way often points to its own answer. Asking questions is the ABC of diagnosis. Only the inquiring mind solves problems.

-Quote by Edward Hodnett