connectie 2 tabellen met koppeltabel
Ik probeer om in 1 query ALLE gegevens en ook de JUISTE te hebben.
Ik moet ALLE logins hebben maar ook ALLE gallerijen. Deze worden gekoppeld via hun resectievelijke id.
Als ik geen 'on' koppeling gebruik krijg ik gewoon het product van de records van alle tabellen. Wat logisch is. Maar op deze maniet weet ik niet wat het toegangsniveau is van de gallerij. Op de ene gallerij kan je andere rechten hebben dan op de andere. Als ik wel een 'on' koppeling gebruik dan ontbreek ik gallerijen welke nog geen rechten gekregen hebben.
In het verleden gebruikte ik 3 queries en liet ik php het werk doen maar ik denk dat het wel moet kunnen via sql.
Jan
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select
l.id lid,
l.login,
l.schermnaam,
g.titel,
g.id gallerijid,
lg.niveau
from
logins l
join
gallerijen g
#on g.id=lg.gallerij
join
logins2gallerij lg
#on l.id=lg.login
l.id lid,
l.login,
l.schermnaam,
g.titel,
g.id gallerijid,
lg.niveau
from
logins l
join
gallerijen g
#on g.id=lg.gallerij
join
logins2gallerij lg
#on l.id=lg.login
De naamgeving is een beetje verwarrend, en ik kan het gebruik van Foreign Keys aanbevelen, dus ik doe het even zo.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- betere naamgeving
ALTER TABLE `logins` RENAME TO `login`;
ALTER TABLE `login` ALTER COLUMN `schermnaam` RENAME TO `naam`;
ALTER TABLE `gallerijen` RENAME TO `galerij`;
ALTER TABLE `galerij` ALTER COLUMN `gallerij` RENAME TO `id`; -- aanname dat dit de PK is
ALTER TABLE `galerij` ALTER COLUMN `schermnaam` RENAME TO `naam`;
ALTER TABLE `logins2gallerij` RENAME TO `login_galerij`;
ALTER TABLE `login_galerij` ALTER COLUMN `id` RENAME TO `login_fk`;
ALTER TABLE `login_galerij` ALTER COLUMN `<naam van kolom met id's van galerijen` RENAME TO `galerij_fk`;
-- database moet garanties geven op consistentie van gegevens
ALTER TABLE `login_galerij` ADD CONSTRAINT `login_galerij_galerij_fk_fkey` FOREIGN KEY (`galerij_fk`)
REFERENCES `galerij` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE `login_galerij` ADD CONSTRAINT `login_galerij_login_fk_fkey` FOREIGN KEY (`login_fk`)
REFERENCES `login` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
-- query voor het ophalen van alle gegevens zoals gevraagd
SELECT
`login`.`id` AS `login_id`,
`login`.`login`,
`login`.`naam` AS `gebruiker`,
`galerij`.`naam` AS `galerij`,
`galerij`.`id` AS `galerij_id`,
`login_galerij`.`niveau`
FROM `login_galerij`
INNER JOIN `galerij`
ON `login_galerij`.`galerij_fk` = `galerij`.`id`
INNER JOIN `login`
ON `login_galerij`.`login_fk` = `login`.`id`
-- ORDER BY `login`.`naam`, `galerij`.`naam`
ALTER TABLE `logins` RENAME TO `login`;
ALTER TABLE `login` ALTER COLUMN `schermnaam` RENAME TO `naam`;
ALTER TABLE `gallerijen` RENAME TO `galerij`;
ALTER TABLE `galerij` ALTER COLUMN `gallerij` RENAME TO `id`; -- aanname dat dit de PK is
ALTER TABLE `galerij` ALTER COLUMN `schermnaam` RENAME TO `naam`;
ALTER TABLE `logins2gallerij` RENAME TO `login_galerij`;
ALTER TABLE `login_galerij` ALTER COLUMN `id` RENAME TO `login_fk`;
ALTER TABLE `login_galerij` ALTER COLUMN `<naam van kolom met id's van galerijen` RENAME TO `galerij_fk`;
-- database moet garanties geven op consistentie van gegevens
ALTER TABLE `login_galerij` ADD CONSTRAINT `login_galerij_galerij_fk_fkey` FOREIGN KEY (`galerij_fk`)
REFERENCES `galerij` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE `login_galerij` ADD CONSTRAINT `login_galerij_login_fk_fkey` FOREIGN KEY (`login_fk`)
REFERENCES `login` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
-- query voor het ophalen van alle gegevens zoals gevraagd
SELECT
`login`.`id` AS `login_id`,
`login`.`login`,
`login`.`naam` AS `gebruiker`,
`galerij`.`naam` AS `galerij`,
`galerij`.`id` AS `galerij_id`,
`login_galerij`.`niveau`
FROM `login_galerij`
INNER JOIN `galerij`
ON `login_galerij`.`galerij_fk` = `galerij`.`id`
INNER JOIN `login`
ON `login_galerij`.`login_fk` = `login`.`id`
-- ORDER BY `login`.`naam`, `galerij`.`naam`
Dit is hoe ik het zou doen, maar het kan ook anders. Omdat het INNER JOINs zijn kan je ook schrijven:
Code (php)
1
2
3
4
2
3
4
[..]
FROM `login_galerij`, `galerij`, `login`
WHERE `login_galerij`.`galerij_fk` = `galerij`.`id`
AND `login_galerij`.`login_fk` = `login`.`id`
FROM `login_galerij`, `galerij`, `login`
WHERE `login_galerij`.`galerij_fk` = `galerij`.`id`
AND `login_galerij`.`login_fk` = `login`.`id`
Gewijzigd op 20/11/2021 23:18:06 door Frank Nietbelangrijk
Maar ik heb het gevonden met een cross join en dan een left join
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
select
l.id lid,
l.login,
l.schermnaam,
g.titel,
g.id gallerijid,
lg.niveau
from
logins l
cross join
gallerijen g
left join
logins2gallerij lg
on l.id=lg.login and
g.id=lg.gallerij
order by
l.schermnaam,
g.id
l.id lid,
l.login,
l.schermnaam,
g.titel,
g.id gallerijid,
lg.niveau
from
logins l
cross join
gallerijen g
left join
logins2gallerij lg
on l.id=lg.login and
g.id=lg.gallerij
order by
l.schermnaam,
g.id
Bedankt
Gewijzigd op 21/11/2021 04:15:33 door Jan R
En het is inefficiënt om een CROSS JOIN te doen met een carthesisch product als resultaat, om daarop weer een LEFT JOIN te doen op een tabel waarin alle relaties al staan.
Het uitgangspunt voor het FROM statement moet zijn de tabel `logins2gallerij` (sic) waarna je met een LEFT JOIN naar `logins` en met een LEFT JOIN naar `gallerij` (sic) koppelt. Niet alleen is dat efficiënter, maar ook simpeler om te lezen.
Overigens heb je zonder FK-relaties met een LEFT JOIN nog steeds een mogelijkheid op lege rijen met NULL-waarden in kolommen, die je dan weer moet filteren in PHP. Dat kan je voorkomen met FK-relaties en/of gebruik van INNER JOIN.
Voor het overige moet je natuurlijk zelf weten welke naamgeving je aanhoudt. PHP staat er om bekend als een omgeving waarin iedereen fijn zelf kan ontdekken.
Het gaat hier over tonen en aanpassen van de rechten dus moet ik ze toch allemaal hebben.
Jan
Als je alles tegelijk wilt tonen dan is jouw aanpak inderdaad de beste.
Bepalend voor de aanpak is het aantal rechten en gebruikers.
Bij weinig gebruikers en rechten kan je alles tegelijk op het scherm tonen zonder dat prestaties daaronder veel te lijden hebben.
Bij complexere applicaties met veel rechten, rollen en gebruikers is het niet te doen om alles op 1 scherm te hebben, en is er veel te zeggen voor de andere aanpak.
Overigens fijn dat het gelukt is!
Ik heb al wat en voor wie wil lachen hier staat het https://gallerij.janr.be/
Nee ik heb niet gelachen, ik vind vooral de hondjes leuk. Daarmee oogst je meteen sympathie :-)
https://gallerij.janr.be/
inloggen met (e-mail/ww) [email protected] / Test.mij
Je kan (als alles goed is) enkel testen op "Doe even wat jullie willen".
Gelieve login en wachtwoord niet te wijzigen of te herstellen naar huidig indien je het toch test. Anders kan er niemand meer op deze login.
opmerkingen
Deze gallerij blijft slechts een paar weken openlijk toegankelijk.
Laad geen politieke, racistische, pornografische illegale of copyright foto's op.
Jan
Als ik een foto wilt droppen (klein vlak trouwens), dan krijg ik: "Fout bij opladen![object Promise]"