[MySQL] extreme query?

[jbweb]

Ik ben bezig met een banner systeem. Ik heb een tabel met advertenties die random worden weergegeven (7 stuks). Elke weergave wordt getracked in een aparte tabel. Nu wil ik er voor zorgen dat iedereen een advertentie maar 3x mag zien.

Mijn tabellen:
ADVERTENTIE
id
titel
url
max_view (dit is dus 3 in dit voorbeeld)
startdatum
einddatum

TRACE
id
advertentie_id
sessie

Ik heb nu de volgende query:
SELECT * FROM `ADVERTENTIE` WHERE (`startdatum` < UNIX_TIMESTAMP() OR `startdatum` = 0) AND (`einddatum` > UNIX_TIMESTAMP() OR `einddatum` = 0) ORDER BY RAND() LIMIT 7

Maar ik heb geen idee hoe ik nu in deze query kan checken of het maximum aantal views al is behaald. Ik kan natuurlijk een SELECT COUNT(`id`) AS `tel` FROM `TRACE`, maar ik weet (aangezien het een RAND() betreft, natuurlijk nooit welke ad ik moet tellen...

Wie kan mij helpen?

[NMe]

Zoiets, even uit de losse pols:

SQL:

SELECT `ADVERTENTIE`.`id`, `ADVERTENTIE`.`titel`, `ADVERTENTIE`.`url`,
       `ADVERTENTIE`.`startdatum`, `ADVERTENTIE`.`einddatum`, COUNT(`TRACE`.`id`) AS `views`
FROM `ADVERTENTIE` LEFT JOIN `TRACE` ON `ADVERTENTIE`.`id` = `TRACE`.`advertentie_id`
WHERE (`startdatum` < UNIX_TIMESTAMP() OR `startdatum` = 0)
  AND (`einddatum` > UNIX_TIMESTAMP() OR `einddatum` = 0)
GROUP BY `ADVERTENTIE`.`id`, `titel`, `url`, `startdatum`, `einddatum`
HAVING COUNT(`TRACE`.`id`) < `ADVERTENTIE`.`max_view`
ORDER BY RAND()
LIMIT 7

Als je versie van MySQL subqueries ondersteunt wordt je probleem trouwens iets makkelijker. Mocht deze query niet werken, laat dan even je MySQL-versie weten.

Overigens heb je hier geen probleem met de MySQL-software maar met een specifieke query, en dat past in mijn straatje, Programming dus.

WSS>>PRG

[jbweb]

Bedankt, deze query werkt inderdaad voor een groot deel!
Ik begrijp niet hoe iemand zo'n query kan bedenken, respect!

Ik heb MySQL 4.1.14, dus in principe zouden subqueries mogelijk moeten zijn. Ik zie echter niet hoe het de query zou kunnen versimpelen!
Trouwens, volgens mij wordt er nu nog niet op views per sessie gecontroleert toch, alleen op totaal aantal views... krijg je dan een group by in je having clause???

[ Voor 27% gewijzigd door jbweb op 19-01-2007 11:02 ]

[NMe]

Nee, dan maak je dit van je GROUP BY:

SQL:

GROUP BY `ADVERTENTIE`.`id`, `TRACE`.`sessie`, `titel`, `url`, `startdatum`, `einddatum`

Overigens is dit nog niet zo'n hele lastige query. Als je weet hoe GROUP BY werkt en welke joins doen wat jij wil ben je er zo uit, als het goed is.
Programming FAQ - SQL: Hoe werkt dat GROUP BY nu eigenlijk?
Programming FAQ - SQL: Hoe werken joins?

[jbweb]

Dank, maar helaas werkt dit niet goed bij mij.
Ik denk dat ik het inmiddels een redelijk stuk boven mijn eigen pet aan het zoeken ben. Om een flinke redundant info tegen te gaan hebben ze namelijk de TRACE tabel uit elkaar getrokken.
Een SESSIE tabel, waar het sessie met enkele andere gebruikers info in opgeslagen word, en de TRACE tabel met een advertentie_is en een id van de sessie tabel.

SESSIE
id
sessie_id
browser
enz.

TRACE
id
SESSIE.id
ADVERTENTIE.id

Het wordt nu dus nog moeilijker, want de sessie_id staat niet meer in de TRACE tabel. Dus moet ik eerst nog de SESSIE en de TRACE koppelen.

Ik ben de vorige FAQ's aan het doorlezen, maar de WHERE en GROUP BY clausules beginnen me flink te duizelen.
Voordat ik er echt heel wat uren in ga stoppen. Is dit uberhaupt nog wel mogelijk? En welke richting moet ik het zoeken, dit is voor mij rocket science...

[NMe]

Gewoon nog een join.

SQL:

SELECT `ADVERTENTIE`.`id`, `ADVERTENTIE`.`titel`, `ADVERTENTIE`.`url`,
       `ADVERTENTIE`.`startdatum`, `ADVERTENTIE`.`einddatum`, `SESSIE`.`sessie_id`,
       COUNT(`TRACE`.`id`) AS `views`
FROM `ADVERTENTIE` LEFT JOIN `TRACE`
     ON `ADVERTENTIE`.`id` = `TRACE`.`advertentie_id` INNER JOIN `SESSIE`
     ON `TRACE`.`SESSIE.id` = `SESSIE`.`id`
WHERE (`startdatum` < UNIX_TIMESTAMP() OR `startdatum` = 0)
  AND (`einddatum` > UNIX_TIMESTAMP() OR `einddatum` = 0)
GROUP BY `ADVERTENTIE`.`id`, `sessie_id` `titel`, `url`, `startdatum`, `einddatum`
HAVING COUNT(`TRACE`.`id`) < `ADVERTENTIE`.`max_view`
ORDER BY RAND()
LIMIT 7

Uiteraard weer niet getest, maar zoveel complexer maakt het je probleem niet.

[jbweb]

Tuurlijk, nog eens JOIN, alsof het de normaalste zaak van de wereld is
Maaruhhh, het was hem inderdaad bijna! Hij liet nu niets meer zien, want een nieuwe sessie resulteert in 0 views per sessie. Maar met INNER JOIN koppelt ie niet op als er aan één kant geen gegevens heeft (net geleerd dankzij je links )
Maar toen ik er een RIGHT JOIN van maakte, voila!

@ -NMe- Bedankt

P.s. het was toch right join hier? Of had ik left join moeten gebruiken?

[MueR]

jbweb schreef op vrijdag 19 januari 2007 @ 10:57:
Ik begrijp niet hoe iemand zo'n query kan bedenken, respect!

offtopic:
Welcome in Programming.. the center of insanity. All hail our king, NMe

[NMe]

jbweb schreef op vrijdag 19 januari 2007 @ 16:15:
Tuurlijk, nog eens JOIN, alsof het de normaalste zaak van de wereld is

Is het ook.

P.s. het was toch right join hier? Of had ik left join moeten gebruiken?

Voor MySQL kun je right joins meestal beter herschrijven naar left joins aangezien die beter worden geïndexeerd, geloof ik. Je zal dan wel alles wat nu links van de woorden "RIGHT JOIN" staat rechts moeten zetten en alles wat rechts staat links ervan.

[jbweb]

Het is een tijdje geleden. De query werkt al een tijdje goed, totdat... ze weer wat nieuws verzonnen

Even ter info de huidige query:

PHP:

$sql = "SELECT `advertising`.`ads_id`, `advertising`.`ads_titel`, `advertising`.`ads_tekst`, 
    `advertising`.`ads_period_start`, `advertising`.`ads_period_end`,
    `advertising`.`ads_max_session`, `advertising`.`ads_max_total`, 
`advertising`.`ads_max_count`, `tracker`.`session_id`, COUNT(`trace_ads`.`id`) AS `views` 
    FROM `advertising` LEFT JOIN `trace_ads` 
        ON `advertising`.`ads_id` = `trace_ads`.`ad` RIGHT JOIN `tracker` 
        ON `trace_ads`.`tracker_id` = `tracker`.`id` 
    WHERE `advertising`.`ads_status` = 'enabled' 
        AND `advertising`.`ads_categorie` = 'text' 
        AND `tracker`.`session_id` = '" . session_id() . "' 
        AND (`advertising`.`ads_max_total` > `advertising`.`ads_max_count` OR `advertising`.`ads_max_total` = 0) 
        AND (`ads_period_start` < CURDATE() OR `ads_period_start` = 0) 
        AND (`ads_period_end` > CURDATE() OR `ads_period_end` = 0) 
    GROUP BY `advertising`.`ads_id` 
        HAVING (COUNT(`trace_ads`.`id`) < `advertising`.`ads_max_session`  OR `advertising`.`ads_max_session` = 0)
    ORDER BY RAND() 
    LIMIT 10";

Probleem is dat elke klant meerdere advertenties kan hebben, maar dat hiervan maar ééntje Random (!!!) moet worden weergegeven.
Met GROUP BY op dit klantnummer krijg ik echter steeds dezelfde advertentie terug (denk omdat groupby voor order by staat?)

Ik heb het volgende al in de having geprobeerd:

SQL:

HAVING ((COUNT(`trace_ads`.`id`) < `advertising`.`ads_max_session`  OR `advertising`.`ads_max_session` = 0) AND COUNT(`advertising`.`ads_klant`) < 2)

Maar dat werkt niet, al heb ik geen idee waarom (leek me zo slim van mijzelf ;-)
Iemand een idee?

[sky-]

Haal die lelijke backticks ( ` ) eens weg. Is helemaal niet nodig.

[ Voor 4% gewijzigd door sky- op 01-03-2007 11:06 ]

[NMe]

k8skaaay schreef op donderdag 01 maart 2007 @ 11:05:
Haal die lelijke backticks ( ` ) eens weg. Is helemaal niet nodig.

Als je niet weet waar je hebt over hebt, zeg dan niks. Als je niet begrijpt wat ik bedoel, probeer dan eens wat te selecteren uit een tabel die "user" heet, zonder backticks te gebruiken.

[jbweb]

Ben het helemaal eens met de backticks NMe, en buiten de usual problemen vind ik het zelf heel relaxt lezen.
Maar helaas weet ik nu nog niet hoe mijn probleem op te lossen. Ben ook zelf goed op zoek geweest, maar kan eigenlijk nergens echt vinden hoe je een GROUP BY Random maakt.

Het lijkt mij toch wel een nuttig iets, anders zou een random nooit echt random zijn als je een group by gebruikt, dat lijkt me vreemd.

[momania]

-NMe- schreef op donderdag 01 maart 2007 @ 11:31:
[...]

Als je niet weet waar je hebt over hebt, zeg dan niks. Als je niet begrijpt wat ik bedoel, probeer dan eens wat te selecteren uit een tabel die "user" heet, zonder backticks te gebruiken.

offtopic:
Wie gebruikt er dan ook reserved keywords als tabel namen?

[LuCarD]

momania schreef op donderdag 01 maart 2007 @ 15:18:
[...]

offtopic:
Wie gebruikt er dan ook reserved keywords als tabel namen?

Het geld niet alleen voor tabel namen maar ook voor functies en constants. Aangezien er in elke versie van MySQL wel een 1 of 2 bijkomen kan je daar nooit helemaal tegen indekken.

[NMe]

momania schreef op donderdag 01 maart 2007 @ 15:18:
[...]

offtopic:
Wie gebruikt er dan ook reserved keywords als tabel namen?

offtopic:
"User" vind ik niet eens zo'n gekke keuze voor een tabelnaam. Mij is in elk geval aangeleerd altijd enkelvoudige zelfstandige naamwoorden als tabelnaam te kiezen, en mijn code is altijd in het Engels. Een ander gangbaar Engels woord voor "gebruiker" ken ik niet en "tags" als t_ voor een tabelnaam vind ik al helemaal nergens op slaan. Gewoon backticks gebruiken = nooit gezeik hebben.

[momania]

-NMe- schreef op vrijdag 02 maart 2007 @ 23:44:
[...]

offtopic:
"User" vind ik niet eens zo'n gekke keuze voor een tabelnaam. Mij is in elk geval aangeleerd altijd enkelvoudige zelfstandige naamwoorden als tabelnaam te kiezen, en mijn code is altijd in het Engels. Een ander gangbaar Engels woord voor "gebruiker" ken ik niet en "tags" als t_ voor een tabelnaam vind ik al helemaal nergens op slaan. Gewoon backticks gebruiken = nooit gezeik hebben.

offtopic:
Ik gebruik altijd meervoud voor m'n tabelnamen, zit je ook redelijk veilig 'users', 'groups', etc. Klinkt imo ook logischer. Het is een bv een 'user row' uit de tabel 'users'
Gewoon betere tabel namen gebruiken is wmb dus nooit gezeik met ergens een backtick vergeten en je zuur zoeken naar de fout in je query Tuurlijk zal je dan zeggen: moet je betere editor gebruiken, maar goed, ik ik zeg dan: gebruik dan ook een echte DBMS

[JochemK]

momania schreef op zaterdag 03 maart 2007 @ 00:08:
[...]

[ot]
Ik gebruik altijd meervoud voor m'n tabelnamen, zit je ook redelijk veilig 'users', 'groups', etc. Klinkt imo ook logischer. Het is een bv een 'user row' uit de tabel 'users'

offtopic:
Ik heb net als NMe geleerd dat je juist enkelvoud moet gebruiken, je tabel bevat objecten van een bepaalde entiteit, bijvoorbeeld de entiteit user, en dus noem je de tabel user.