[MySQL] 4 dubbele join, gaat niet goed... - Softwareontwikkeling

woensdag 19 april 2006 14:40

Acties:

one life, live it!

Topicstarter

Voor een website met phpBB forum wil ik de moderators een mogelijkheid geven om snel de gebruikers te kunnen overzien. Het forum is uitgebreid met onder andere de gebruikelijke phpBB gebeuren, een cash mod, een bank mod, een loterij mod, een waarschuwingen systeem en ook zijn er speciale mod-tags mogelijk.

In het overzicht wil ik de volgende velden van alle gebruikers laten zien:
ID, Gebruikersnaam, Laatste bezoek, Credits, Bank, % Credits, Loten, Waarschuwingen, Aantal mod balkjes

De velden ID, gebruikersnaam, laatste bezoek, credits en waarschuwingen kunnen gewoon uit de tabel php_users worden gehaald.

Het veld Bank wordt uit de tabel phpbb_bank gehaald, waar het veld phpbb_bank.name moet overeen komen met phpbb_users.user_id. Gebruikers kunnen kiezen een bank aan te maken, deze hoeft dus niet perse te bestaan!

Het veld % credits wordt berekend door het aantal credits van een gebruiker op te tellen bij zijn bank saldo, en dit door het totaal te delen.

Het veld loten wordt wordt uit phpbb_lottery gehaald, voor elk lot dat een gebruiker koopt wordt er een nieuwe kolom aan deze tabel toegevoegd, het kan dus ook voorkomen dat de gebruikers ID niet in phpbb_lottery voorkomt.

Het veld aantal mod balkjes wordt berekend door het aantal keer te tellen dat er in een bericht van een gebruiker de ubb code "[mod" voorkomt. Deze moet in phpbb_posts_text worden gezocht, welke verwijst (met een post_id) naar de tabel phpbb_posts, welke verwijst (met een poster_id) naar de tabel phpbb_users.

Om dit allemaal in een query te gooien had ik zelf het volgende bedacht:

SQL:

SELECT (bank.holding + user.user_points) AS total, COUNT(lottery.user_id) AS lottery, user.user_id, user.user_active, user.username, user.user_session_time, user.user_points, user.user_warnings, bank.holding
FROM
(
 (
  (
   (
    phpbb_users AS user
    LEFT JOIN phpbb_bank AS bank ON user.user_id = bank.name
   )
   LEFT JOIN phpbb_lottery AS lottery ON user.user_id = lottery.user_id
  )
  LEFT JOIN phpbb_posts AS post ON post.poster_id = user.user_id
 )
 LEFT JOIN phpbb_posts_text AS text ON post.post_id = text.post_id AND text.post_text LIKE "%[mod:%"
)
GROUP BY user.user_id
ORDER BY user.user_id ASC

Het probleem hierbij is dat er nu voor er voor het aantal loten nu "het aantal loten maal het totaal aantal berichten" van een gebruiker uit komt, bij het aantal blauwe balkjes komt er "het aantal blauwe balkjes maal het aantal loten" uit.

Ziet iemand wat ik fout doe?

2 swim or not 2 swim, that's the question

woensdag 19 april 2006 15:00

Acties:

ripexx

bibs

Ik zie even niet in wat er mis gaat, wellicht is het handiger als je de haken uit de joins verwijderd:

SQL:

SELECT 
   (bank.holding + user.user_points) AS total, 
   COUNT(lottery.user_id) AS lottery, 
   user.user_id, 
   user.user_active, 
   user.username, 
   user.user_session_time, 
   user.user_points, 
   user.user_warnings, 
   bank.holding
FROM
   phpbb_users AS user LEFT JOIN phpbb_bank AS bank ON user.user_id = bank.name
   LEFT JOIN phpbb_lottery AS lottery ON user.user_id = lottery.user_id
   LEFT JOIN phpbb_posts AS post ON post.poster_id = user.user_id
   LEFT JOIN phpbb_posts_text AS text ON post.post_id = text.post_id 
WHERE 
   text.post_text LIKE "%[mod:%"
GROUP BY
   totaal,
   user.user_id,
-- /* etc etc. alle velden behlave de count */
ORDER BY
    user.user_id ASC

Veranderd:
* alle haakjes bij de joins weggehaald
* where clause toegevoegd voor like value
* group by moet aangepast worden (zie ook fe FAQ over groeperen in SQL)

buit is binnen sukkel

woensdag 19 april 2006 15:29

Acties:

aqua_man

one life, live it!

Topicstarter

* Haakjes weghalen maakt niet veel uit, maar kan ook geen kwaad om ze te verwijderen.
* Where clause lijkt me onlogisch, op deze manier pakt hij alleen de gebruikers die een bericht hebben met een mod-tag erin ("[mod")

* Group by kan ik me nu in vergissen, maar moet deze niet alleen user_id zijn...? Als je namelijk nog eens group by text.post_id toevoegt, krijg je een gebruiker meerdere keren te zien als hij meerdere berichten heeft met een mod-tag... En ik wil elke gebruiker natuurlijk maar een keer in de lijst zetten...

2 swim or not 2 swim, that's the question

woensdag 19 april 2006 16:00

Acties:

Verwijderd

Je krijgt de verkeerde count omdat je query meerdere rijen (namelijk het het aantal posts keer een rij per user) met dezelfde user_id teruggeeft! Wil je de user count hebben dan zul je toch een andere query moeten verzinnen.

woensdag 19 april 2006 16:09

Acties:

Dido

heforshe

aqua_man schreef op woensdag 19 april 2006 @ 15:29:
* Haakjes weghalen maakt niet veel uit, maar kan ook geen kwaad om ze te verwijderen.

e.e.a. wordt er iig wat overzichtelijker op, want de layout wordt door je openingspost zo vernaggelt dat het niet meer te volgen is wat je wilt

* Where clause lijkt me onlogisch, op deze manier pakt hij alleen de gebruikers die een bericht hebben met een mod-tag erin ("[mod")

Wat anders denk jij dan te bereiken door het in je join op te nemen

* Group by kan ik me nu in vergissen, maar moet deze niet alleen user_id zijn...? Als je namelijk nog eens group by text.post_id toevoegt, krijg je een gebruiker meerdere keren te zien als hij meerdere berichten heeft met een mod-tag... En ik wil elke gebruiker natuurlijk maar een keer in de lijst zetten...

Waarom je op text.post.uid zou groupen is me een raadsel, dat zegt ook niemand, maar alle andere velden in je select moet je wel op groupen. Lees die link hierboven maar eens door over GROUP BY.

Als jij iedere gebruiker maar 1 keer iun je lijst wilt, moet je voor alle andere informatie aanegven welke van de gevonden regels je wilt zien, anders snapt geen enkele zichzelf respecterende DB-engine wat je wilt.

Wat betekent mijn avatar?

woensdag 19 april 2006 17:06

Acties:

WvdWest

In je select gebruik je total in je group by totaal. Ligt het daar soms aan?

I'm not a complete idiot - several parts are missing.