[SQL] nog niet bekeken items voor multi-user-site

[moozzuzz]

Hoi,

op onze website kan elke gebruiker items posten (in dit geval fotoalbums). We willen deze items nu uitlijsten en uiteraard tonen welke items nog niet bekeken werden door de gebruiker. Ik vind echter niet hoe ik deze lijst in één query kan trekken.

foto_album	id	...
fotoalbumviewed	fid	uid

albums
- 1 'album1'
- 2 'fotoshoot2'
- 3 'nog iets 3'
- 4 '4de vb.'

users
- 1001, 'jantje'
- 2002, 'pietje'
- 3003, 'nog iemand'

albumsviewed ~= log
- 1, 1001
- 2, 1001
- 2, 2002
- 2, 3003
- 3, 3003

Om te weten hoeveel er niet bekeken zijn hebben we voorlopig deze:

SQL:

    SELECT COUNT(A.fid) 
    FROM fotoalbumviewed AS A LEFT JOIN foto_album AS B ON A.fid=B.id 
    WHERE A.uid = 1003 AND  B.status=1;

en daar trekken we

SQL:

SELECT COUNT(id) FROM foto_album AS B WHERE B.status=1;

van af.

Voor de lijst heb ik op dit moment het volgende:

SQL:

SELECT A.id, A.type, A.activiteit, A.tijdstip, A.aantal, A.uid, B.uid AS gezien 
FROM foto_album AS A 
LEFT JOIN fotoalbumviewed AS B   ON A.id  = B.fid 
WHERE A.status=1 
GROUP BY A.id
ORDER BY A.type, A.activiteit, A.tijdstip
LIMIT 0, 500;

... doch op basis van deze query kan je enkel zien welke items nog door niemand bekeken werden (gezien = een uid of NULL). Hoe kan ik deze query aanpassen dat ie enkel toont welke items door user 1003 al dan niet bekeken werden?

Alternatief:
Ik herschrijf een bestaande website en daar gebruiken ze nu per item een aparte query om te checken of men het item al gezien heeft. Dat lijkt me niet zo efficient...

[ Voor 7% gewijzigd door moozzuzz op 29-08-2008 17:10 . Reden: voorbeeld toegevoegd ]

[r0bert]

Ik weet niet of het handig is, maar je kunt natuurlijk ook op basis van een soort log schrijven. Dan doe je een omgekeerde join met je table van bestaande items met de records van de betreffende user. Misschien is dat ook wat je nu probeert te benaderen? Een veld status is trouwens overbodig als je alleen maar wél of niet een item kunt kijken. Items die niet bekeken zijn, log je niet, dus bij de join blijven die NULL. Is er wel een geldige join gedaan, weet je dat dat een view is.

[ Voor 41% gewijzigd door r0bert op 29-08-2008 17:01 ]

[Noork]

Je post is een beetje onduidelijk omdat je niet duidelijk aangeeft hoe je de registratie exact bij houdt. Status=1 houdt in dat de foto wel/niet bekeken is? Hou je alleen de foto's bij de bekeken zijn? Of maak je direct rijen aan voor een toegevoegde gebruiker wanneer een nieuwe foto is toegevoegd?

Kun je niet iets doen als ruwweg: "select foto from blabla where foto_id not in (subselect). Waarbij subselect dus alle bekeken foto's zijn.

[moozzuzz]

r0bert schreef op vrijdag 29 augustus 2008 @ 16:59:
Ik weet niet of het handig is, maar je kunt natuurlijk ook op basis van een soort log schrijven. Dan doe je een omgekeerde join met je table van bestaande items met de records van de betreffende user.

Dat is wat ik denk ik wel gedaan heb:

albums
- 1 'album1'
- 2 'fotoshoot2'
- 3 'nog iets 3'
- 4 '4de vb.'

users
- 1001, 'jantje'
- 2002, 'pietje'
- 3003, 'nog iemand'

albumsviewed ~= log
- 1, 1001
- 2, 1001
- 2, 2002
- 2, 3003
- 3, 3003

Status is of het album uberhaubt online mag staan (admins kunenn een item tijdelijk offline halen).

Noork schreef op vrijdag 29 augustus 2008 @ 17:01:
Je post is een beetje onduidelijk

• Sorry, status geeft weer of het album mag getoond worden (niet relevant voor dit topic dus).
• Ik registreer (of zou dit toch zo willen) enkel de items die reeds gezien zijn en door wie (albumviewed). Zie voorbeeld.
• Je vb-query zal dan enkel de items weergeven die niet gezien zijn (ik zou in de lijst ook de items willen die wel al gezien zijn; maar bij de items die nog niet gezien zijn een "new" oid willen plaatsen).

Ik voeg het vb even toe aan de TS.

[Noork]

Als je een inner join gebruikt, ipv een left join zou je volgens mij alle records terug moeten krijgen.

En daarbij voeg je dan b.v. ook nog een extra kolom toe aan je albumviewid. B.v. status=1 (als default waarde).

vv idd, checken of er wat in staat en de CASE constructie kan natuurlijk ook.

[ Voor 15% gewijzigd door Noork op 29-08-2008 17:26 ]

[Kentsfield]

SQL:

CASE 
            WHEN 
                 VELDNAAM IS NULL THEN 0
            ELSE 1
END) AS Bekeken

Even snel denk ik dat je hier wel iets mee zou kunnen. Sorry heb geen tijd het beter uit te werken, Succes.

[moozzuzz]

Kentsfield schreef op vrijdag 29 augustus 2008 @ 17:23:

SQL:

CASE 
...
END AS Bekeken

Succes.

Bleek inderdaad wat ik zocht. Ter info:

• 1003 is het id van de testuser.
• een foto-album heeft een status (= goed of afgekeurd), deze heeft niets te zien met het feit of een album al gezien is of niet.
• fotoalbumviewed bevat alle albums die reeds door de gebruikers gezien zijn (uid + albumid; primary key)
• GROUP BY A.id is nodig omdat meerdere gebruikers één album kunnen gezien hebben
• we willen ook uitschrijven wie het album gepost heeft (dus koppeling nr de gebruikers-tabel)

SQL:

SELECT A.id, A.type, A.activiteit, A.tijdstip, A.aantal, A.uid, C.username, 
    CASE WHEN B.uid =1003
        THEN 1 
        ELSE 0 
    END AS Bekeken
FROM foto_album AS A
LEFT JOIN fotoalbumviewed AS B ON A.id = B.fid
LEFT JOIN wusers AS C ON A.uid = C.uid
WHERE A.status =1
GROUP BY A.id
ORDER BY A.type , A.activiteit, A.tijdstip
LIMIT 0 , 500 

[Voutloos]

Dat is een random data query. Je groepert enkel op A.id, maar selecteert ook uit andere tabellen. Welke C.username of B.uid overblijft na het groeperen berust puur op toeval.
Zie P&W FAQ - SQL en leest de opmerking over mysql.

[Noork]

Ik snap de group by ook niet zo erg. Kun je niet gewoon een where foto_album.userid = 1003 toevoegen?

[moozzuzz]

Voutloos schreef op maandag 01 september 2008 @ 13:12:
Dat is een random data query. Je groepert enkel op A.id, maar selecteert ook uit andere tabellen. Welke C.username of B.uid overblijft na het groeperen berust puur op toeval.
Zie P&W FAQ - SQL en leest de opmerking over mysql.

ik vrees dat je gelijk hebt. C.username is 1-1 met A.id, maar B.uid niet. Vond het al uiterst grappig dat dit werkte (dacht dat het aan de CASE-toestand lag).

Ik vermoed dat ik ga moeten overstappen op 2 queries: ééntje om alle albums gezien door 1003 op te lijsten en ééntje om alle albums die niet gezien zijn door 1003 op te lijsten.

Noork schreef op maandag 01 september 2008 @ 13:24:
Ik snap de group by ook niet zo erg. Kun je niet gewoon een where foto_album.userid = 1003 toevoegen?

Neen, want er zijn 2 uid's:

• één zit in de tabel fotoalbumviewed om vast te leggen wie wat gezien heeft
• één zit in de tabel foto_album om vast te stellen wie het album gepost heeft

Ik wil niet weten wie wat gepost heeft, maar wie wat gezien heeft. Als ik selecteer op fotoalbumviewed.uid=1003 dan worden echter enkel de items getoond die al gezien zijn.

Als ik niet group by, dan komen er meerdere rijen terug per fotoalbum: namelijk zoveel als er personen het item geopend hebben.

[MBV]

Bedoel je niet zoiets?

SQL:

SELECT A.id, A.type, A.activiteit, A.tijdstip, A.aantal, A.uid, 
    CASE WHEN B.uid IS NULL
      THEN 1
      ELSE 0
     END AS Bekeken
FROM foto_album AS A 
LEFT JOIN fotoalbumviewed AS B   
  ON A.id  = B.fid 
  AND B.uid = 1003
WHERE A.status=1 
ORDER BY A.type, A.activiteit, A.tijdstip
HAVING Bekeken = 0
LIMIT 0, 500;

Hierbij ga ik er vanuit dat je een view maar 1x opslaat, o.a. omdat je geen tijdstip eraan hangt. Als je ze wel elke keer opslaat, dan moet je een GROUP BY op B.uid of B.fid leggen. Je zou ook simpel b.uid in de SELECT kunnen zetten, en dan HAVING b.uid IS NULL, maar ik heb hem zo neergezet voor het geval je ook de bekeken items wil laten zien

[moozzuzz]

MBV schreef op maandag 01 september 2008 @ 19:00:
Bedoel je niet zoiets?

SQL:

de oplossing

Hierbij ga ik er vanuit dat je een view maar 1x opslaat, o.a. omdat je geen tijdstip eraan hangt. Als je ze wel elke keer opslaat, dan moet je een GROUP BY op B.uid of B.fid leggen. Je zou ook simpel b.uid in de SELECT kunnen zetten, en dan HAVING b.uid IS NULL, maar ik heb hem zo neergezet voor het geval je ook de bekeken items wil laten zien

Goed uitgangspunt en ik vermoed ook meteen het juiste antwoord. Ik wist niet dat je in de join ook een soort selectie kon doen (AND B.uid = 1003) want dat is echt wat ik zocht. Bovendien zijn we dankzij die "join-select" die vuile group by ook meteen kwijt.

Voor het nageslacht, dit is de uiteindelijke query (er van uitgaande dat je je userid zet waar de 1003 staat:

SQL:

SELECT A.id, A.type, A.activiteit, A.tijdstip, A.aantal, A.uid, A.cat, C.username, 
   CASE WHEN B.uid IS NULL 
      THEN 0 
      ELSE 1 
   END AS gezien
FROM foto_album AS A
LEFT JOIN fotoalbumviewed AS B
   ON A.id = B.fid
   AND B.uid =1003
LEFT JOIN wusers AS C
   ON A.uid = C.uid
WHERE A.status =1
ORDER BY A.type, A.activiteit, A.tijdstip
LIMIT 0 , 500;

Met de bovenstaande code kan je een lijst produceren van alle items (in dit geval enkel foto_album) die goedgekeurd zijn (status = 1) en kan je het onderscheid maken of het item reeds gezien is of niet.

* moozzuzz doet hoedje af voor MBV

offtopic:
met dit topic meteen ook geleerd dat ikmijn TS beter moet verwoorden

[ Voor 2% gewijzigd door moozzuzz op 02-09-2008 18:47 . Reden: indenting ]

[MBV]

Ach ja, geleerd van mijn oud-collega. Die deed daar altijd smerige dingen mee, hier is het ook nuttig

Ik zou zelf trouwens de ORDER BY beginnen met 'gezien', vziw mag dat (anders doe je 'ORDER BY B.uid IS NULL', dat mag zeker), zodat je begint met de lijst van ongelezen items

[Confusion]

MBV schreef op maandag 01 september 2008 @ 19:00:
Bedoel je niet zoiets?

SQL:

SELECT A.id, A.type, A.activiteit, A.tijdstip, A.aantal, A.uid, 
    CASE WHEN B.uid IS NULL
      THEN 1
      ELSE 0
     END AS Bekeken
FROM foto_album AS A 
LEFT JOIN fotoalbumviewed AS B   
  ON A.id  = B.fid 
  AND B.uid = 1003
WHERE A.status=1 
ORDER BY A.type, A.activiteit, A.tijdstip
HAVING Bekeken = 0
LIMIT 0, 500;

Die 'having' gaat niet werken, aangezien je geen 'group by' hebt. Dat moet gewoon een 'where' zijn.

[MBV]

Ik heb hem serieus getest op mijn MySQL servertje, en hij werkt. HAVING werkt nadat alles is geweest, dus qua performance niet echt top WHERE B.uid IS NULL zal sneller zijn, en werkt ook

edit:
zie je wel

[ Voor 6% gewijzigd door MBV op 03-09-2008 12:08 ]

[Confusion]

Ahja, in de SQL99 standaard staat inderdaad dat er een impliciete GROUP BY wordt aangenomen als er een HAVING wordt gebruikt, terwijl de GROUP BY ontbreekt.

[drm]

MBV:
Ach ja, geleerd van mijn oud-collega. Die deed daar altijd smerige dingen mee, hier is het ook nuttig

Wat is er precies smerig aan dan? Als het een conditie voor de join betreft is het m.i. altijd beter om die in de ON clause te zetten, ipv in de WHERE. Nog afgezien van het feit dat wanneer je een conditie anders dan IS [NOT] NULL opneemt in de WHERE de join impliciet een INNER JOIN wordt (wat dus in de topicstart een probleem was), wordt je query er ook veel duidelijker mee.

moozzuzz:
Goed uitgangspunt en ik vermoed ook meteen het juiste antwoord. Ik wist niet dat je in de join ook een soort selectie kon doen (AND B.uid = 1003) want dat is echt wat ik zocht. Bovendien zijn we dankzij die "join-select" die vuile group by ook meteen kwijt.

... en is die group by nou vuil omdat je eigenlijk niet precies begrijpt wat die doet, of omdat hij er sowieso niet hoorde?

[moozzuzz]

drm schreef op donderdag 04 september 2008 @ 00:58:
... en is die group by nou vuil omdat je eigenlijk niet precies begrijpt wat die doet, of omdat hij er sowieso niet hoorde?

offtopic:
Wel,... nu begrijpen is gelukkig geen probleem, maar tot dit topic wist ik niet dat MySQL dit soort zaken at random afhandelde en mijn testresultaten zuiver toevallig juist waren (hoewel ik dit natuurlijk wel kon vermoeden).

De group by was nodig in mijn eerste oplossing maar enkel als een truck (=vuil) om één resultaat per item te krijgen. de uiteindelijke oplossing heeft natuurlijk geen group by nodig

[drm]

De eerste had prima gekund, mits je er een group function bij had gezet op de B.uid kolom en alle velden uit de A tabel had gegroepeerd.

Als je als group function COUNT had gebruikt, was je volgens mij ook een heel eind gekomen Maar: mosterd na de maaltijd