[MySQL] Score berekenen met meerdere tabellen*

[Eskimootje]

Ik ben net aan het stoeien geweest met een query maar hij wil nog niet echt lukken. Ik wil graag de score's voor mensen op mn site bereken maar het lukt nog niet. Ze kunnen punten krijgen voor het geven van links, stemmen op links en aangeven dat de link dood is. Nu staat dat dus allemaal in 4 verschillende tabbelen (te weten users, votes, links, dodelinks) ze hebben allemaal als danwel primary key danwel foreign key: userid, hier wil ik dus met count gaan tellen hoevaak iets voorkomt in de database. Dat gaat best goed met 1 tabel, maar met 2 worden de scores voor allebei de onderdelen gelijk. Ik vraag me af waardoor dat probleem opgelost kan worden.

code:

SELECT
      users.userid, users.nickname, 
         COUNT(links.referer) AS linkpoints,
         COUNT(votes.userid) AS votepoints 
FROM
       users, links, votes 
WHERE
       links.verify > -1 AND links.userid = users.userid AND 
         votes.userid = users.userid 
GROUP BY 
       users.userid

Ik heb het ook nog met een JOIN geprobeerd maar zelfde resultaat

code:

SELECT
     users.userid, users.nickname, 
     COUNT(links.referer) AS linkpoints,
     COUNT(votes.userid) AS votepoints
FROM
     users 
LEFT JOIN 
      links
ON 
      users.userid = links.userid
LEFT JOIN
      votes 
ON 
      votes.userid = users.userid 
GROUP BY 
      links.referer

Volgens mij telt gewoon alles maar bij elkaar op en niet wat ik wil hebben. Maar hoe het wel moet

[whoami]

Kun je misschien ff een klein voorbeeldje geven van de data die in je tabellen zit, en dan een voorbeeldje van wat je wilt hebben?

[Eskimootje]

[users]
userid
nickname
email
password
etc....

[links]
linkid
referer (foreign key > userid)
url
verify (is het een link die gebruikt kan/moet worden alles boven de -1 moet punten
opleveren
etc..

[votes]
userid (foreign key)
linkid (foreign key)
mark

hoe het er uit moet komen te zien:

code:

|| Userid || Nickname   || Linkpoints || Votepoints ||
   2          Eskimootje     12                45

Waarbij Linkpoints en votepoints allebei het aantal keren is dat een bepaalde userid in de tabel voorkomt.

[ Voor 4% gewijzigd door Eskimootje op 04-03-2003 15:49 ]

[whoami]

en waar zitten die linkpoints en votepoints dan in? Die zitten in de tabel votes? (mark ofzo?)

[Eskimootje]

whoami schreef op 04 March 2003 @ 15:51:
en waar zitten die linkpoints en votepoints dan in? Die zitten in de tabel votes? (mark ofzo?)

Neej die worden gemaakt door het aantal keren dat iemand gestemd heeft of het aantal linken wat deze heeft aangemeld.

[momania]

Welke score krijg je er nu uit? Niet precies, maar is dat bv bij linkpoints en votepoints de waarde die je alleen bij votepoints verwacht (de grootste dus? )

[Eskimootje]

momania schreef op 04 maart 2003 @ 15:57:
Welke score krijg je er nu uit? Niet precies, maar is dat bv bij linkpoints en votepoints de waarde die je alleen bij votepoints verwacht (de grootste dus? )

Ik krijg 2 keer dezelfde waarde, en dat is ws. niet geheel toevallig het aantal keren dat het userid/referer in de tabel: links voorkomt keer het aantal keren dat de userid in de tabel votes voorkomt.

[whoami]

Dus dan moet je doen:

code:

select tblUser.userId , count(tblVotes.userid)
from tblUser, tblVotes
where tblUser.userid = tblVotes.userId
GROUP BY tblUser.userId

Hoe je kunt weten hoeveel links een bepaalde user heeft aangemeld weet ik zo niet. Je houdt blijkbaar niet bij welke link door welke user werd aangemeld.

[Eskimootje]

whoami schreef op 04 March 2003 @ 16:01:
Dus dan moet je doen:

code:

select tblUser.userId , count(tblVotes.userid)
from tblUser, tblVotes
where tblUser.userid = tblVotes.userId
GROUP BY tblUser.userId

Hoe je kunt weten hoeveel links een bepaalde user heeft aangemeld weet ik zo niet. Je houdt blijkbaar niet bij welke link door welke user werd aangemeld.

Ik hou per link bij door wie die werd aangemeld, alleen 1 tabel joinen was geen probleem alleen die 2e lukt er niet bij.

[momania]

Eskimootje schreef op 04 maart 2003 @ 15:59:
[...]

Ik krijg 2 keer dezelfde waarde, en dat is ws. niet geheel toevallig het aantal keren dat het userid/referer in de tabel: links voorkomt keer het aantal keren dat de userid in de tabel votes voorkomt.

Ik bedoelde ook geen vermenigvuldiging van de twee waardes, maar ik kan me voorstellen dat er bijvoorbeeld dit gebeurt:
- alleen links joinen geeft 15 linkspoints
- alleen votes joinen geeft 45 votepoint
- allebei joinen geeft bij beide 45 (de grootste dus)

[Eskimootje]

momania schreef op 04 March 2003 @ 16:03:
[...]

Ik bedoelde ook geen vermenigvuldiging van de twee waardes, maar ik kan me voorstellen dat er bijvoorbeeld dit gebeurt:
- alleen links joinen geeft 15 linkspoints
- alleen votes joinen geeft 45 votepoint
- allebei joinen geeft bij beide 45 (de grootste dus)

In mijn geval gebeurde dus:
- alleen links joinen geeft 5 linkspoints
- alleen votes joinen geeft 3 votepoint
- allebei joinen geeft bij beide 15 (vermenigvuldigd)

[momania]

Wat gebeurt er al je group by zo maakt:
dus:

GROUP BY
users.userid AND
links.referer AND
votes.userid;

[Eskimootje]

momania schreef op 04 maart 2003 @ 16:08:
Wat gebeurt er al je group by zo maakt:
dus:

GROUP BY
users.userid AND
links.referer AND
votes.userid;

Nope zelfde probleem.

[momania]

Ik heb nog even wat documentatie erbij gepakt en zoals jij het wil kan het iig niet.
Met een count krijg je het aantal regels in de result en omdat je 2 tabellen joined kan je em nooit zo groeperen dat je 2 veschillende waardes krijgt omdat ze allbei dezeflde regels in een resultset tellen alleen op een andere kolom. Vandaar dat ze altijd hetzelfde zullen zijn.

Dit zal je dus moeten opsplitsen of op een andere manier moeten oplossen.

Wat makkelijker is om te doen is een extra kolom maken in de tabellen votes en links waarin je punten bijhoudt. Die kan je dan met SUM optellen, dat gaat veel makkelijker. Dan krijg je zoiets:

code:

SELECT
      users.userid, users.nickname, 
      SUM(links.points)          AS Linkpoints         
      SUM(votes.points)        AS votepoints 
FROM
      users, links, votes 
WHERE
      links.verify > -1                     AND 
      links.userid = users.userid   AND 
      votes.userid = users.userid 
GROUP BY 
      users.userid

[ Voor 53% gewijzigd door momania op 04-03-2003 16:24 ]

[Eskimootje]

Het hoeven niet perse de resultaten per ding te worden het mag ook in 1 keer het totaal aantal punten worden, of lukt dat ook niet?

[momania]

Eskimootje schreef op 04 March 2003 @ 16:25:
Het hoeven niet perse de resultaten per ding te worden het mag ook in 1 keer het totaal aantal punten worden, of lukt dat ook niet?

Dat wordt ook lastig.. Een COUNT moet je eigenlijk alleen maar gebruiken op 1 kolom in 1 tabel.

[dusty]

Het zou gedaan kunnen worden met gebruik van subqueries, aangezien dat dat niet kan met MYSQL zal je dus verschillende queries moeten afvuren en dan in je scripttaal de juiste antwoorden bij elkaar moeten gooien.

[Eskimootje]

dusty schreef op 04 March 2003 @ 16:29:
Het zou gedaan kunnen worden met gebruik van subqueries, aangezien dat dat niet kan met MYSQL zal je dus verschillende queries moeten afvuren en dan in je scripttaal de juiste antwoorden bij elkaar moeten gooien.

Daar was ik al bang voor, is een stuk inefficienter als puur een sql query maar ik kan iig weer verder

[dusty]

Moet je een echte RDBMS gaan gebruiken

[Eskimootje]

dusty schreef op 04 March 2003 @ 20:35:
Moet je een echte RDBMS gaan gebruiken

Betaal jij dat? Een MySQL db is op elke webserver wel aanwezig en is ook gratis daarentegen is Oracle/etc. dat niet en mag je dat soort dingen niet echt op je wevbserver kwijt. Imho overkill.

[whoami]

Eskimootje schreef op 04 March 2003 @ 20:38:
[...]

Betaal jij dat? Een MySQL db is op elke webserver wel aanwezig en is ook gratis daarentegen is Oracle/etc. dat niet en mag je dat soort dingen niet echt op je wevbserver kwijt. Imho overkill.

Een echt RDBMS hoeft niet duur te zijn hoor.

MSDE van Microsoft (SQL Server engine) is gratis te verkrijgen en is een echt RDBMS. (Vanaf 5 concurrente connecties zit er echter een 'vertraging' in).
InterBase van Borland is ook gratis te verkrijgen. (En ook een echt RDBMS).
En voor zover ik hier gehoord heb, is PostgreSQL ook een echt RDBMS en ook gratis.

Er zijn zoveel (gratis) alternatieven voor MySQL. Maarja, iedereen blaat maar over MySQL, en zo verlies je de anderen al snel uit het oog natuurlijk.

[slm]

Zodra je iets met MySQL niet voor elkaar kunt krijgen, moet je je niet gaan afvragen of je misschien naar een ander systeem moet overstappen, maar jezelf eerst afvragen of je database ontwerp wel zo geschikt is voor datgene wat je wilt.

Waarom maak je niet gewoon 1 extra tabel met daarin de votes? Waarom maak je geen apart veld bij elke user met de totale votecount? Zodra een user een link heeft toegevoegd doe je een update set votecount=votecount+1 where id=userid en je bent klaar.

[whoami]

slm schreef op 04 March 2003 @ 22:57:
Zodra je iets met MySQL niet voor elkaar kunt krijgen, moet je je niet gaan afvragen of je misschien naar een ander systeem moet overstappen, maar jezelf eerst afvragen of je database ontwerp wel zo geschikt is voor datgene wat je wilt.

Hmmm.....
Een goed database-ontwerp is natuurlijk een eerste vereiste om een goed systeem te hebben, maar daarnaast heb je natuurlijk ook nog een goed RDBMS nodig. (Iets wat MySQL niet is).

Waarom maak je niet gewoon 1 extra tabel met daarin de votes? Waarom maak je geen apart veld bij elke user met de totale votecount? Zodra een user een link heeft toegevoegd doe je een update set votecount=votecount+1 where id=userid en je bent klaar.

En dat is een goed database-ontwerp?

[slm]

whoami schreef op 04 March 2003 @ 23:09:
Hmmm.....
Een goed database-ontwerp is natuurlijk een eerste vereiste om een goed systeem te hebben, maar daarnaast heb je natuurlijk ook nog een goed RDBMS nodig. (Iets wat MySQL niet is).
[...]
En dat is een goed database-ontwerp?

Leuk dat je het allemaal zo goed weet, maar ik zie jou verder geen andere suggesties geven behalve dan het adviseren van een ander systeem dan MySQL wat waarschijnlijk totaal nicht im frage is omdat zijn host dat gewoon niet aanbiedt, waardoor de toegevoegde waarde van je post eigenlijk behoorlijk laag is.... Vervelende gevolg is dat ik me op mijn beurt genoodzaakt voel om hier een reply op te geven, die de topicstarter waarschijnlijk geen meter verder helpt.

[whoami]

slm schreef op 04 March 2003 @ 23:21:
[...]

Leuk dat je het allemaal zo goed weet, maar ik zie jou verder geen andere suggesties geven behalve dan het adviseren van een ander systeem dan MySQL wat waarschijnlijk totaal nicht im frage is omdat zijn host dat gewoon niet aanbiedt, waardoor de toegevoegde waarde van je post eigenlijk behoorlijk laag is.... Vervelende gevolg is dat ik me op mijn beurt genoodzaakt voel om hier een reply op te geven, die de topicstarter waarschijnlijk geen meter verder helpt.

Nou, ik zie jou anders ook geen nuttige bedrage leveren met jouw post. (zoals je zelf al aangeeft).
Dusty heeft de TS al op weg geholpen, dus, waarom zou ik hem nog eens moeten herhalen.

Als de topicstarter zegt 'een echt RDBMS, wie gaat dat betalen?', dan voel ik mij genoodzaakt om te melden dat er ook 'echte' RDBMS'en bestaan die gratis zijn. _{Ik heb trouwens nergens gezegd dat hij één daarvan moest gebruiken.}

[ Voor 5% gewijzigd door whoami op 04-03-2003 23:27 ]

[Eskimootje]

Goed voordat jullie gaan vechten, zal ik even in moeten grijpen de reden waarom ik geen PostgressSQL en andere dingetjes gebruik is idd de webserver en ik zal nu idd via een stomme extra rij alles bij moeten houden. Waarom ik dat liever niet wil/wou is dat het overbodige informatie is in een database, het komt namelijk al voor. Maar wat niet anders kan doen we ook maar niet