Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Recursive Relation 1

Status
Not open for further replies.
Extension

Extension

Programmer
Nov 3, 2004
311
CA

Hi,

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

Table:

Code: 
+-------+---------------+---------------+-------+
|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)

Code: 
- US
  - Vermont
    - Burlington
    - Winchester

  - New York
    - Brooklyn
    - Manhattan

- CA
  - Alberta
    - Lake Town


Query:

Code: 
<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


 
Sort by date Sort by votes
see what this gets ya
Code: 
<cfoutput query = "levels" group = "level1">
  -#level1#
  <cfoutput group = "level2">
    &nbsp;&nbsp;-#level2#
    <cfoutput group = "level3">
      &nbsp;&nbsp;&nbsp;&nbsp;-#level3#
    </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
 
Upvote 0 Downvote
bombboy
Thanks for the help.
It's almost working. Only childs and sub-childs under US and not CA are being displayed.

Output

Code: 
- US
  - Vermont
    - Burlington
    - Winchester

  - New York
    - Brooklyn
    - Manhattan

BTW, ignore the query above (first post). This is the one I'm using

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

</cfquery>



 
Upvote 0 Downvote
if you only get one group are you sure you're getting it in the query? what happens if you do a cfdump of it?

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
 
Upvote 0 Downvote
bombboy

I was wrong in my last statement. It is not working. The childs are getting displayed two times. (see below)

Here's the outcome:
Code: 
- US
  - Vermont
  - Vermont
    - Burlington 
    - Winchester 

  - New York
  - New York
    - Brooklyn
    - Manhattan

So, it is displaying the child two times, meaning that the relation is wrong. Should I use a cfloop, but then I won't be able to use the group function of cfquery ?

Here's the table properly structured. (Easier to read)

Code: 
+-------+---------------+---------------+------+
|Level1 |Level2         |Level3         |ID    |
+-------+---------------+---------------+------+
|US     |Vermont        |               |10    |
+-------+---------------+---------------+------+
|US     |Vermont        |Burlington     |12    |
+-------+---------------+---------------+------+
|US     |Vermont        |Winchester     |11    |
+-------+---------------+---------------+------+
|US     |New York       |               |20    |
+-------+---------------+---------------+------+
|US     |New York       |Manhattan      |21    |
+-------+---------------+---------------+------+
|US     |New York       |Brooklyn       |22    |
+-------+---------------+---------------+------+
|CA     |Alberta        |               |30    |
+-------+---------------+---------------+------+
|CA     |Alberta        |Lake Town      |31    |
+-------+---------------+---------------+------+
 
Upvote 0 Downvote
Another approach would be to run the query as follows:

select level1, level2, level3
from table
group by level1, level2, level3

while displaying, only print levels if they change. Can also use sort if want to display levels alphabetically.
 
Upvote 0 Downvote
what about

where level3 is not NULL

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
 
Upvote 0 Downvote
bombboy

If you look at the table, you'll see that the real issue is in the way of determining which one of them are the childs (Level2). I don't think cfgroup can handle that. Maybe an extensive cfloop ?


Expected output based on the last table I've posted (With ID's added)
Code: 
- US
  - Vermont (ID 10)
    - Burlington (ID 12)
    - Winchester (ID 11)

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

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



 
Upvote 0 Downvote
well you get two new yorks and two vermonts because of the NULL value in level 3 so that would solve that part.

you should also ORDER BY all the fields you group by.

ORDER BY level1 ASC, level2 ASC, level3 ASC

that will keep them grouped together.

there is no reason it shouldn't work based on the table you provided.

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
 
Upvote 0 Downvote
With the following query:
Code: 
<cfquery name="Levels" datasource="#Source#">
    SELECT * 
    FROM RELATION
    WHERE 0=0
    ORDER BY Level1, Level2, Level3

</cfquery>

and following code

Code: 
<cfoutput query = "levels" group = "level1">
  -#level1#
  <cfoutput group = "level2">
    &nbsp;&nbsp;-#level2# [b](ID #ID#)[/b]
    <cfoutput group = "level3">
      &nbsp;&nbsp;&nbsp;-#level3# [b](ID #ID#)[/b]
    </cfoutput>
  </cfoutput>
</cfoutput>

I'm getting (Look at the IDs of Level 2 - They are the same as the first of Level 3)

Code: 
- US
  - Vermont (ID 12)
    - Burlington (ID 12)
    - Winchester (ID 11)

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

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

Still having an issue when determining the Level2


 
Upvote 0 Downvote
just curious, what do you get if you remove group = "level3" and just use 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
 
Upvote 0 Downvote
bombboy
Exactly the same, because there is no level under level 3. Basically useless.

 
Upvote 0 Downvote
the problem is, an id isn't really assigned to the second level. it's assigned to the third level that has a NULL value. what you might try is include the null values then use an if not to show it. that will put the ID you want on the second level. (it takes the first third level id, null would come first.)

Code: 
<cfoutput query = "levels" group = "level1">
  -#level1#
  <cfoutput group = "level2">
    &nbsp;&nbsp;-#level2# (ID #ID#)
    <cfoutput group = "level3">
      [b]<cfif len(trim(level3))>[/b]
        &nbsp;&nbsp;&nbsp;-#level3# (ID #ID#)
      [b]</cfif>[/b]
    </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
 
Upvote 0 Downvote
No it's not working...

Since we are using the group function, it is simply grouping the data by common fields. So for the Level2 ID, it is simply displaying the ID of the first Level3 record.

With this data

Code: 
+-------+---------------+---------------+------+
|Level1 |Level2         |Level3         |ID    |
+-------+---------------+---------------+------+
|US     |Vermont        |               |10    |
+-------+---------------+---------------+------+
|US     |Vermont        |Burlington     |12    |
+-------+---------------+---------------+------+
|US     |Vermont        |Winchester     |11    |
+-------+---------------+---------------+------+

It should output the following:
Code: 
- US
  - Vermont [b](ID 10)[/b]
    - Burlington (ID 12)
    - Winchester (ID 11)

instead of:

Code: 
- US
  - Vermont [b](ID 12)[/b]
    - Burlington (ID 12)
    - Winchester (ID 11)
 
Upvote 0 Downvote
before you were getting this:
- US
- Vermont
- Vermont
- Burlington
- Winchester

- New York
- New York
- Brooklyn
- Manhattan

what did you change to remove the second one?

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
 
Upvote 0 Downvote
Ignore that. I was testing something else.

Did you understand my last post ? Hope it was clear enough.

By the way, thank you very much for your time. You are really helpful.


 
Upvote 0 Downvote
i tested something similar with different fields. but here is what happened.

-department
-position
-
-name1
-name2

the blank row is one like your instance

US - vermont - null

so you should be getting an empty record somewhere.

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
 
Upvote 0 Downvote
bombboy
You're exactly on the same level as me. (This is great an a way...) To resolved the blank line I simply added the following. (in bold)

Code: 
<cfoutput query = "levels" group = "level1">
  -#level1#
  <cfoutput group = "level2">
    &nbsp;&nbsp;-#level2# (ID #ID#)
    <cfoutput group = "level3">
     [b]<cfif #level3# gt 0>[/b]
     &nbsp;&nbsp;&nbsp;-#level3# (ID #ID#)
     [b]</cfif>[/b]
    </cfoutput>
  </cfoutput>
</cfoutput>
 
Upvote 0 Downvote
:|

my theory doesn't work then. it should have taken the id from the blank record to put next to level2.

hiding anymore code?

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
 
Upvote 0 Downvote
bombboy

Nope !

This is exactly my current query & code

Query:

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

</cfquery>

Code:

Code: 
<cfoutput query = "levels" group = "level1">
  -#level1#
  <cfoutput group = "level2">
    &nbsp;&nbsp;-#level2# (ID #ID#)
    <cfoutput group = "level3">
     <cfif #level3# gt 0>
     &nbsp;&nbsp;&nbsp;-#level3# (ID #ID#)
     </cfif>
    </cfoutput>
  </cfoutput>
</cfoutput>

Using cfloop would be more complicated ?
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

TamedTech
  • Locked
  • Question
Calendar / Recursive Events
Replies
4
Views
51
TamedTech
TamedTech
billandmiss
  • Locked
  • Question
Recursive directory list
Replies
1
Views
41
imstillatwork
imstillatwork
tyrant1969
  • Locked
  • Question
Recursive Includes and Variables
Replies
5
Views
23
r937
r937
shop73
  • Locked
  • Question
Parent - Child relationship
Replies
2
Views
25
shop73
shop73
imstillatwork
  • Locked
  • Question
Recursive category collapse / expand
Replies
4
Views
23
imstillatwork
imstillatwork

Part and Inventory Search

Sponsor

Back
Top