[MSSQL][ASP.NET] Recursive ParentID *

[StapelPanda]

ik heb de volgende tabel:

code:

ID  Name  PID
1    cat1    0
2    cat2    0
3    sub1   1
4    ssub1 3
5    sub2   2

daaruit krijg ik al het volgende menu

code:

cat1
-sub1
--ssub1
cat2
-sub2

normaal gesproken krijg je alleen cat1 en cat2 te zien, maar als je de pagina van ssub1 bekijkt moeten zowel cat1 als sub1 uitgeklapt zijn.
Nu kan dit wel met wel met 3 query's
"SELECT pid FROM cat WHERE id = 4" => 3
"SELECT pid FROM cat WHERE id = 3" => 1
"SELECT pid FROM cat WHERE id = 1" => 0
maar nu wil ik dit eigenlijk met een while loop in de SQL doen ipv in ASP.NET
nu ben ik tot de volgende query gekomen:

code:

DECLARE @ids VARCHAR(100)
DECLARE @pid INT
SELECT @pid = parentid FROM Categorie WHERE categorieid = 5
WHILE @pid <> 0
 BEGIN
  SELECT @ids = (@ids + ',' + @pid), @pid= parentid FROM Categorie WHERE categorieid = @pid
 END

als ik de query uitvoer krijg ik geen errors , maar ook geen resultaat.
de vraag luid: hoe krijg ik @ids uit de query in mijn asp script?

[TeeDee]

Waarom wil je dit in SQL doen? Lijkt mij dat dit gewoon pure presentatie is.
Maar goed, je wil nu dus de return value van een Sproc in je asp.net gebruiken?
Pseudo

C#:

/*
*stored procedure aanroep etc. etc.
* bijvoorbeeld
* SqlCommand comm = new SqlCommand(...);
*/
string ids = comm.ExecuteScalar(); //ids bevat nu het e.e.a. 

Voor de rest zal je toch echt meer moeten laten zien.

[RobIII]

Dit is toch al meer dan eens voorbijgekomen...
Kijk anders hier eens. Dan heb je heel de recursie niet eens meer nodig.

[ Voor 27% gewijzigd door RobIII op 07-05-2008 16:42 ]

[d00d]

Er staat niet welke versie van MSSQL je gebruikt maar vanaf 2005 kun je het volgende doen:
(ik ben er vanuit gegaan dat de naam van je tabel dbo.Menu is.

SQL:

declare @id int
set @id = 4

;with cteMenu as (
    select ID, Name, 0 as lvl, ROW_NUMBER() over (order by ID) as nr
    from dbo.Menu
    where PID = 0
    
    union all
    
    select M2.ID, M2.Name, M.lvl + 1, M.nr 
    from dbo.Menu as M2
        join cteMenu as M
            on M2.PID = M.ID
),
Number as (
    select nr, lvl
    from cteMenu
    where ID = @id
)
select REPLICATE('-', cteMenu.lvl) + Name 
from cteMenu
    join Number on (cteMenu.nr = Number.nr
        and cteMenu.lvl <= Number.lvl )
    or cteMenu.lvl = 0
order by cteMenu.nr, cteMenu.lvl, id;

Dit levert exact jouw resultaat:

code:

cat1
-sub1
--ssub1
cat2

Het resultaat van deze query is dus dat hij de menustructuur toont tot en met het door jouw opgegeven id (4 in dit geval) en altijd alle hoofdmenu's.

[ Voor 24% gewijzigd door d00d op 07-05-2008 18:21 ]

[StapelPanda]

Woow.....
bedankt voor je stuk code, maar dat is neit wat ik bedoelde
het was alleen om te laten zien hoe je t eruit kreeg, dus subniveaus illustreren

ExecuteScalar is het denk ik idd, hele sql code:

SQL:

DECLARE @ids VARCHAR(50)
DECLARE @id INT
DECLARE @pid INT
SELECT @pid = 3
SELECT @ids = ''
  WHILE @pid <> 0
   BEGIN
   SELECT @ids = CONVERT(VARCHAR(6),categorieid) + ',' + @ids, @pid = parentid FROM Categorie WHERE categorieid = @pid
   END
SELECT @ids

Het blijkt dat je ook in SQL je Variabelen moet Initialiseren dus SELECT @ids = ''

[ Voor 72% gewijzigd door StapelPanda op 07-05-2008 19:20 ]

[TeeDee]

Als inderdaad "ExecuteScalar" de oplossing voor jouw probleem is, dan zou ik als ik jou was maar eens wat tutorials gaan volgen / boeken gaan doorspitten.

[ Voor 12% gewijzigd door TeeDee op 07-05-2008 19:34 ]

[wboevink]

maar nu wil ik dit eigenlijk met een while loop in de SQL doen ipv in ASP.NET

Waarom?

Het blijkt dat je ook in SQL je Variabelen moet Initialiseren dus SELECT @ids = ''

Flauwekul, je begrijpt niet wat er gebeurt. @ids is null daar tel je een int bij op (een geluk dat je een int bij een char mag optellen in sql) null + 2 = null.
Je moet @ids inderdaad 'initialiseren', maar dat geldt in dit geval en zeker niet voor alle variabelen.

Maar toch, de oplossing van 'd00d' is inderdaad de beste manier, en anders een recursieve proc schrijven. Als je het perse in SQL wil oplossen, maar daar hoort het eigenlijk niet thuis.

[ Voor 23% gewijzigd door wboevink op 07-05-2008 19:48 ]

[StapelPanda]

Hij werkt al hoor
d00d heeft een query geschreven om deze output te genereren:
cat1
-sub1
--ssub1
cat2
-sub2
Dit was alleen ter illustratie, en ik wou een query die als je op de pagina van ssub1 bent de ID's van sub1 en cat1 terug gaf.

Ik wil het graag in SQL omdat dat sneller is dan een whileloop + 3 querys. en ik van een uitdaging hou.

[ Voor 24% gewijzigd door StapelPanda op 07-05-2008 19:59 ]

[gorgi_19]

Afhankelijk van de frequentie van gebruik kan je ook EfBe in "\[VB.NET] Treeview + Subnodes (van Subnodes)" gebruiken; parentId's kan je op een vergelijkbare methodiek opslaan / cachen en hoef je ook niet steeds terug te gaan naar je database.

[d00d]

Gieltjev schreef op woensdag 07 mei 2008 @ 19:53:
Hij werkt al hoor
d00d heeft een query geschreven om deze output te genereren:
cat1
-sub1
--ssub1
cat2
-sub2
Dit was alleen ter illustratie, en ik wou een query die als je op de pagina van ssub1 bent de ID's van sub1 en cat1 terug gaf.

Ik wil het graag in SQL omdat dat sneller is dan een whileloop + 3 querys. en ik van een uitdaging hou.

Je heb gelijk, mijn eerste query was niet wat je vroeg, maar je vraag was ook een beetje onduidelijk. Jouw oplossing is eigenlijk prima, maar ik blijf erbij dat de mooiste oplossing is om een recursieve query te schrijven, dus hierbij poging nummer 2.

SQL:

declare @id int;
set @id = 4
;with cteCategory as
(
    select categorieid, parentid, 0 as [rank]
    from Categorie
    where categorieid = @id
    
    union all
    
    select c.categorieid, c.parentid, cte.[rank] + 1 
    from Categorie c
        join cteCategory cte
            on c.categorieid = cte.parentid
)
select stuff(
    (select ',' + cast(categorieid as varchar(5)) as [text()]
from cteCategory
order by rank desc
for xml path('')), 1, 1, '');

Resultaat:

code:

1,3,4

[curry684]

Gieltjev schreef op woensdag 07 mei 2008 @ 16:14:
ik heb de volgende tabel:

code:

ID  Name  PID
1    cat1    0
2    cat2    0
3    sub1   1
4    ssub1 3
5    sub2   2

Waarom geef je een item zonder parent een ongeldige PID in plaats van zoals het zou moeten een afwezige?

Daarnaast is dit zoals gezegd een vraagstuk voor je presentation layer of in uitzonderlijke gevallen je business logic. Recursie in databases is A Bad Idea^tm en niet waar ze voor bedoeld zijn.

[_Thanatos_]

Recursie in databases is A Bad Idea^tm en niet waar ze voor bedoeld zijn.

Uhhuh, typische een uitspraak van iemand die te "enterprisey" denkt. Recursie in een database niet een een goed of slecht idee, het is gewoon een requirement van de software en daardoor een requirement van de klant. Je zult toch érgens moeten opslaan onder welke hoofdcategorieën je subcategorieën vallen, en dat hoort bij de gegevens thuis, in de database, en daarmee onherroepelijk recursie. Het preorder algoritme is natuurlijk een oplossing, maar als ik daar met een enterprise-oog naar kijk, denk ik ook "hmm, dat is presentatie in de database", want sortering is presentatie.

Dus hoe vies je het ook vindt, soms moet je gewoon gaan voor de oplossing die werkt, en niet voor de oplossing die er het mooiste uitziet voor die ene persoon die ernaar kijkt (jij dus). Om de vergelijking met auto's maar es te trekken: de meeste auto's hebben de motor voorin, terwijl dat helemaal geen handige plek is; de motor hoort in het midden. Toch doen ze dat omdat het handig is voor de voorwielaandrijving en om extra ruimte voor passagiers te scheppen. Dat is een requirement van de klant, en de techniek pas je daarop aan.

Zo hier ook.

[curry684]

Ik denk niet te enterprisey, jij begrijpt me gewoon verkeerd

Dat je Id's en een ParentId's in een RDBMS opslaat is niets mis mee, dat is standaard foreign key relatiewerk tussen 2 entiteiten of soms binnen een enkele entiteit zoals hier. Dat die relaties er echter liggen betekent echter niet dat je ze er ook uit moet halen. Er komen hier blokken van 26 regels SQL langs die stomweg overbodig zijn als je een database gewoon laat doen waar ie goed in is (data opslaan, organiseren, bewaken en opzoeken) en een applicatie gewoon laat doen waar ie goed in is (logica toekennen aan die data). Je kunt wel stellen dat de oplossing goed is omdat ie werkt, maar dat is een onzinnige stelling gezien het feit dat de SQL te moeilijk voor 'm is om zelf te schrijven, zie het bestaan van dit topic, en het in een taal die er wel geschikt voor is met minder dan de helft van de regels veel performanter en simpeler kan. Keep It Simple Stupid. SQL is goed in een hele hoop dingen, met name rondom het manipuleren van set-based data. Set-based data is niet recursief, en daarom heeft SQL zo weinig mogelijkheden tot recursie Het verhaal wordt anders als je met een OODBMS gaat werken, maar het gaat hier expliciet om MSSQL.

Sortering is overigens juist wel weer een inherente eigenschap van set-based data, en een waar de database subliem geschikt voor is door de aanwezigheid van indexes. Dus juist iets wat je bij voorkeur in je datalayer houdt waar mogelijk.

[d00d]

Tot voor SQL 2005 ben ik het wel met je eens, maar nu hebben we zoiets als Common Table Expressions (dat is een view die on-the-fly gemaakt wordt, ongeveer) en daarin kun je wel degelijk recursie gebruiken.

Dat in combinatie met de nieuwe ranking en windowing functies maakt T-SQL zeer geschikt om problemen die te maken hebben met Hierarchies, Bill of Materials etc. in de database zelf op te lossen.

Het enige wat ik zou aanpassing zijn de 0 waardes in PID, dit moeten eigenlijk NULL's zijn.
Vervolgens kun je met een Foreign Key de integriteit afdwingen.

[Niek.NET]

d00d schreef op donderdag 08 mei 2008 @ 10:57:
Tot voor SQL 2005 ben ik het wel met je eens, maar nu hebben we zoiets als Common Table Expressions (dat is een view die on-the-fly gemaakt wordt, ongeveer) en daarin kun je wel degelijk recursie gebruiken.

Dat in combinatie met de nieuwe ranking en windowing functies maakt T-SQL zeer geschikt om problemen die te maken hebben met Hierarchies, Bill of Materials etc. in de database zelf op te lossen.

Het enige wat ik zou aanpassing zijn de 0 waardes in PID, dit moeten eigenlijk NULL's zijn.
Vervolgens kun je met een Foreign Key de integriteit afdwingen.

Dat het mogelijk is wil niet zeggen dat de 'beste' oplossing is. Wat heeft het voor voordelen om dit op DB niveau te doen. Misschien denk ik te moeilijk maar ik neem aan dat TS gebruik maakt van ADO.NET om de resultset te processen in zijn code behind.

Dan kom je ook sowieso uit op een recursief iets om de boom op te bouwen in memory of bestaat er een DataTable class waar ik niet van weet?


Overigens is de query die jij gebruikt natuurlijk wel handig voor bijvoorbeeld reporting en andere omgevingen waar je geen uitgebreide prog taal tot je beschikking hebt.

[d00d]

Niek.NET schreef op donderdag 08 mei 2008 @ 11:08:
[...]


Dat het mogelijk is wil niet zeggen dat de 'beste' oplossing is. Wat heeft het voor voordelen om dit op DB niveau te doen. Misschien denk ik te moeilijk maar ik neem aan dat TS gebruik maakt van ADO.NET om de resultset te processen in zijn code behind.

Ik ben er een voorstander van om zoveel mogelijk door de database op te laten lossen en het resultaat vervolgens met een simpele call naar een view of sproc aan te bieden aan de ontwikkelaars. Dat komt waarschijnlijk omdat ik als DBA vaak ontwikkelaars zie die suboptimale code naar mijn database sturen, en ik bedoel suboptimaal in de zin van dramatisch slecht...

Je kunt natuurlijk ook gewoon een select * from Phone_Number doen en in de client applicatie filteren op ugly en horny, maar een database is hier nu eenmaal beter in

[Niek.NET]

d00d schreef op donderdag 08 mei 2008 @ 11:29:
[...]


Ik ben er een voorstander van om zoveel mogelijk door de database op te laten lossen en het resultaat vervolgens met een simpele call naar een view of sproc aan te bieden aan de ontwikkelaars. Dat komt waarschijnlijk omdat ik als DBA vaak ontwikkelaars zie die suboptimale code naar mijn database sturen, en ik bedoel suboptimaal in de zin van dramatisch slecht...

Je kunt natuurlijk ook gewoon een select * from Phone_Number doen en in de client applicatie filteren op ugly en horny, maar een database is hier nu eenmaal beter in

Helemaal mee eens, het filteren/sorteren(schandalig versimpelt natuurlijk) is een DBMS 1000x beter in. Boom structuren vindt MS-SQL een stuk minder leuk.

Eigenlijk heb je bewezen dat je met T-SQL wel degelijk boom structuren kunt processen(waarvoor kudos), zou je echter deze als stored_procedure aan mij aanbieden zou ik diep ongelukkig worden . Dit zou namelijk inhouden dat ik in mijn code die string moet splitten op een ',' en vervolgens voor elke id die ik heb alsnog alle info uit de DB moet halen om de naam weer te kunnen geven(waar jij dan weer niet blij van wordt ).

[Janoz]

d00d schreef op donderdag 08 mei 2008 @ 11:29:
[...]


Ik ben er een voorstander van om zoveel mogelijk door de database op te laten lossen en het resultaat vervolgens met een simpele call naar een view of sproc aan te bieden aan de ontwikkelaars. Dat komt waarschijnlijk omdat ik als DBA vaak ontwikkelaars zie die suboptimale code naar mijn database sturen, en ik bedoel suboptimaal in de zin van dramatisch slecht...

Je kunt natuurlijk ook gewoon een select * from Phone_Number doen en in de client applicatie filteren op ugly en horny, maar een database is hier nu eenmaal beter in

Grappig, ik kom het vaak juist andersom tegen. Enorm ingewikkelde, suboptimale en lastig te onderhouden en testen SP's van DBA-ers die in de businness logic veel netter en beter opgelost zouden kunnen worden.

Het opbouwen van een boom wil bijvoorbeeld veel makkelijker door een wat creative combinatie van order by en vervolgens handig doorlopen van de resultset in je code.

[d00d]

Niek.NET schreef op donderdag 08 mei 2008 @ 11:51:
[...]

Helemaal mee eens, het filteren/sorteren(schandalig versimpelt natuurlijk) is een DBMS 1000x beter in. Boom structuren vindt MS-SQL een stuk minder leuk.

Ik ben blij dat je het met me eens bent en ik ben het ook met jou eens dat de oplossing met comma separated string slecht is. Daarom gaf ik in eerste instantie een betere oplossing...

Stel de inhoud van de tabel is zoals TS aangaf:

code:

ID  Name    PID
 1  cat1    0
 2  cat2    0
 3  sub1    1
 4  ssub1   3
 5  sub2    2

dan krijg je van mij met de volgende code: (die verwerkt wordt in een view of sproc)

SQL:

;with cteMenu as ( 
    select ID, Name, 0 as lvl, ROW_NUMBER() over (order by ID) as nr 
    from dbo.Menu 
    where PID = 0 
     
    union all 
     
    select M2.ID, M2.Name, M.lvl + 1, M.nr  
    from dbo.Menu as M2 
        join cteMenu as M 
            on M2.PID = M.ID 
)
select * from cteMenu
order by nr, lvl;

... deze resultset terug...

code:

ID          Name       lvl         nr
1           cat1       0           1
3           sub1       1           1
4           ssub1      2           1
2           cat2       0           2
5           sub2       1           2

Vervolgens kun je met 1 simpele iteratie het menu aanbieden...