Hulp bij MySQL Subquery

[Jan Smeelen]

Ik heb een MySQL table(tbl_pagina) met structuur :

- id
- title
- subpagina_van

Opbouw v.d. pagina's als volgt :

id(=1) - title
- id(=3) - titel // records met id=3 heeft een waarde bij in het veld
subpagina _van = 1
- id(=4) - titel // records met id=4 heeft een waarde bij in het veld
subpagina _van = 1
id (=2) -titel

Nu wil ik in één query weten of een id wél of geen subpagina's heeft.
Ik probeer het als volgt maar dat lukt niet 123...

Ik krijg niet de juiste waardes terug voor Aantal...

SELECT * , (
SELECT COUNT( * )
FROM tbl_pagina AS t1
WHERE t1.subpagina_van = tbl_pagina.id
) AS Aantal FROM tbl_pagina
WHERE 1

Ben al wat suf van teveel scripten de laatste dagen, mogelijk iemand hier
met een heldere blik
die ziet wat ik fout doe ? :-)

De reden dat ik het aantal subpagina wil weten is ivm een delete knop :
Er mogen geen pagina's verwijderd worden waar nog subpagina's onder zitten,
ik vind het dan logischer om geen "delete knop" te tonen.

Bedankt !
Jan.

[Rik Wasmus]

On Fri, 07 Dec 2007 17:56:13 +0100, Jan Smeelen <ivalid@invalid.invalid>
wrote:

> Ik heb een MySQL table(tbl_pagina) met structuur :
>
> - id
> - title
> - subpagina_van
>
> Opbouw v.d. pagina's als volgt :
>
> id(=1) - title
> - id(=3) - titel // records met id=3 heeft een waarde bij in het
> veld
> subpagina _van = 1
> - id(=4) - titel // records met id=4 heeft een waarde bij in het
> veld
> subpagina _van = 1
> id (=2) -titel
>
> Nu wil ik in één query weten of een id wél of geen subpagina's heeft.
> Ik probeer het als volgt maar dat lukt niet 123...
>
> Ik krijg niet de juiste waardes terug voor Aantal...
>
> SELECT * , (
> SELECT COUNT( * )
> FROM tbl_pagina AS t1
> WHERE t1.subpagina_van = tbl_pagina.id
> ) AS Aantal FROM tbl_pagina
> WHERE 1
>
> Ben al wat suf van teveel scripten de laatste dagen, mogelijk iemand hier
> met een heldere blik
> die ziet wat ik fout doe ? :-)
>
> De reden dat ik het aantal subpagina wil weten is ivm een delete knop :
> Er mogen geen pagina's verwijderd worden waar nog subpagina's onder
> zitten,
> ik vind het dan logischer om geen "delete knop" te tonen.

Joins zijn vaak een stuk sneller:

SELECT p.id, count(sp.id)
FROM tbl_pagina p
LEFT JOIN tbl_pagina sp
ON sp.subpagina_van = p.id
GROUP BY p.id





CREATE TABLE `tbl_pagina` (
`id` int(4) NOT NULL,
`parent` int(4) default NULL,
PRIMARY KEY (`id`),
KEY `parent` (`parent`)
) ENGINE=MyISAM DEFAULT;

mysql> EXPLAIN SELECT p.id, count(sp.id)
-> FROM tbl_pagina p
-> LEFT JOIN tbl_pagina sp
-> ON sp.parent = p.id
-> GROUP BY p.id;
+----+-------------+-------+-------+---------------+---------+---------+----------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+----------+------+-------------+
| 1 | SIMPLE | p | index | NULL | PRIMARY | 4 |
NULL | 4 | Using index |
| 1 | SIMPLE | sp | ref | parent | parent | 5 |
foo.p.id | 2 | |
+----+-------------+-------+-------+---------------+---------+---------+----------+------+-------------+

mysql> EXPLAIN SELECT p.id, (SELECT COUNT(*) FROM tbl_pagina sp WHERE
sp.parent = p.id) as Aantal
-> FROM tbl_pagina p;
+----+--------------------+-------+-------+---------------+---------+---------+----------+------+--------------------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+--------------------+-------+-------+---------------+---------+---------+----------+------+--------------------------+
| 1 | PRIMARY | p | index | NULL | PRIMARY |
4 | NULL | 4 | Using index |
| 2 | DEPENDENT SUBQUERY | sp | ref | parent | parent |
5 | foo.p.id | 2 | Using where; Using index |
+----+--------------------+-------+-------+---------------+---------+---------+----------+------+--------------------------+

Meestal laat ik overigens dit soort zaken (navigatie/pagina's/etc.) gewoon
verwijderen als er nog 'kinderen' zijn. Een foreign key constraint & on
delete cascade in een InnoDB tabel nemen je veel werk uit handen, met maar
1 query alle descendants ook verwijderd.
--
Rik Wasmus

[Jan Smeelen]

"Rik Wasmus" <luiheidsgoeroe@hotmail.com> schreef in bericht
newsp.t2y57qxg5bnjuv@metallium.lan...
> On Fri, 07 Dec 2007 17:56:13 +0100, Jan Smeelen <ivalid@invalid.invalid>
> wrote:
>
>> Ik heb een MySQL table(tbl_pagina) met structuur :
>>
>> - id
>> - title
>> - subpagina_van
>>
>> Opbouw v.d. pagina's als volgt :
>>
>> id(=1) - title
>> - id(=3) - titel // records met id=3 heeft een waarde bij in het
>> veld
>> subpagina _van = 1
>> - id(=4) - titel // records met id=4 heeft een waarde bij in het
>> veld
>> subpagina _van = 1
>> id (=2) -titel
>>
>> Nu wil ik in één query weten of een id wél of geen subpagina's heeft.
>> Ik probeer het als volgt maar dat lukt niet 123...
>>
>> Ik krijg niet de juiste waardes terug voor Aantal...
>>
>> SELECT * , (
>> SELECT COUNT( * )
>> FROM tbl_pagina AS t1
>> WHERE t1.subpagina_van = tbl_pagina.id
>> ) AS Aantal FROM tbl_pagina
>> WHERE 1
>>
>> Ben al wat suf van teveel scripten de laatste dagen, mogelijk iemand hier
>> met een heldere blik
>> die ziet wat ik fout doe ? :-)
>>
>> De reden dat ik het aantal subpagina wil weten is ivm een delete knop :
>> Er mogen geen pagina's verwijderd worden waar nog subpagina's onder
>> zitten,
>> ik vind het dan logischer om geen "delete knop" te tonen.
>
> Joins zijn vaak een stuk sneller:
>
> SELECT p.id, count(sp.id)
> FROM tbl_pagina p
> LEFT JOIN tbl_pagina sp
> ON sp.subpagina_van = p.id
> GROUP BY p.id

Werkt perfect, bedankt ! :-)

> Meestal laat ik overigens dit soort zaken (navigatie/pagina's/etc.) gewoon
> verwijderen als er nog 'kinderen' zijn. Een foreign key constraint & on
> delete cascade in een InnoDB tabel nemen je veel werk uit handen, met maar
> 1 query alle descendants ook verwijderd.

Dit begrijp ik, echter pas ik het toe in een CMS, en ik denk dat klanten
mogelijk
anders soms té snel een "hoofdpagina" mogelijk zouden verwijderen, zonder in
de gaten te hebben dat er subpagina's onder zitten, en dan krijg je vragen
zoals :
"waar zijn die subpagina's toch gebleven" ;-)

i.i.g. bedankt voor de query en de perfecte uitleg, ben weer wat wijzer
geworden.

Groeten,
Jan.

[Rik Wasmus]

On Sat, 08 Dec 2007 22:07:56 +0100, Jan Smeelen <ivalid@invalid.invalid>
wrote:
> "Rik Wasmus" <luiheidsgoeroe@hotmail.com> schreef in bericht
> newsp.t2y57qxg5bnjuv@metallium.lan...
>> On Fri, 07 Dec 2007 17:56:13 +0100, Jan Smeelen <ivalid@invalid.invalid>
>> wrote:
>>> De reden dat ik het aantal subpagina wil weten is ivm een delete knop :
>>> Er mogen geen pagina's verwijderd worden waar nog subpagina's onder
>>> zitten,
>>> ik vind het dan logischer om geen "delete knop" te tonen.
>>
>> Meestal laat ik overigens dit soort zaken (navigatie/pagina's/etc.)
>> gewoon
>> verwijderen als er nog 'kinderen' zijn. Een foreign key constraint & on
>> delete cascade in een InnoDB tabel nemen je veel werk uit handen, met
>> maar
>> 1 query alle descendants ook verwijderd.
>
> Dit begrijp ik, echter pas ik het toe in een CMS, en ik denk dat klanten
> mogelijk
> anders soms té snel een "hoofdpagina" mogelijk zouden verwijderen,
> zonder in
> de gaten te hebben dat er subpagina's onder zitten, en dan krijg je
> vragen
> zoals :
> "waar zijn die subpagina's toch gebleven" ;-)

Ik snap waar je vandaan komt, ik heb ook nog veel zaken met dat in het
achterhoofd geschreven. Voor grotere sites wordt dit echter frustrerend
voor de klant die juist wel hele groepen hierarchische data wil weggooien.
De tree door en stuk voor stuk steeds leafnodes verwijderen is dan een erg
vervelende zaak. Een waarschuwing 'deze pagina bevat subpagina's, weet je
heel erg zeker dat je deze allemaal wil verwijderen?' werkt dan een stuk
efficienter (mogelijk met nog een expliciete weergave van 'uitgeklapte'
lijst van de te verwijderen subtree).
--
Rik Wasmus

[Floor]

Jan Smeelen:

> Ik probeer het als volgt maar dat lukt niet 123...

Ik ben zelf een redelijke nono op MySQL gebied, maar sinds ik phpMyAdmin
gebruik kan ik uit de query redelijk wijs.
Floor