[mysql] Hulp gevraagd bij opstellen query

[reyan]

Ik heb een vrij simpel (?) probleem, maar kom er niet meteen uit. Ik ben niet eens zeker of het rechtsreeks kan met MySQL...

Ik heb een tabel waarin een last used veld staat (integer). Nu is het mijn bedoeling om per user_id (integer) enkel de drie laatste/recentste last used records bij te houden (dus hoogste last used waarden). De anderen moeten gedelete worden. Ik weet op voorhand niet hoeveel records er per user zijn, en dat kunnen er best wel eens minder dan 3 zijn.

Ik heb geëxperimenteerd met LIMIT, ORDER BY,... maar niets geeft een resultaat dat ietwat lijkt op hetgene ik wil. Is het wel mogelijk?

[hamsteg]

Snap het niet, kun je iets meer uitleggen ? Tabel posten?

[reyan]

Een extreem vereenvoudigde versie van mijn tabel:

code:

user_id  last_used
1        9000
1        8000
1        4000
1        3000
1        1000
2        8000
2        5000
3        8000
3        7000
3        6000
3        6000
3        5000
3        2000

Na de delete opdracht moet ik overhouden:

code:

user_id  last_used
1        9000
1        8000
1        4000
2        8000
2        5000
3        8000
3        7000
3        6000

[Verwijderd]

Heb je ook nog een sleutelveld?

Anders zoiets:

SQL:

DELETE FROM table WHERE record_id NOT IN 
(SELECT record_id FROM table ORDER BY last_used ASC GROUP BY user_id HAVING COUNT(*) <= 3)

Ik denk ascending, anders descending

[ Voor 86% gewijzigd door Verwijderd op 05-06-2006 12:58 ]

[hamsteg]

Ben bang, maar ben geen expert, dat dit niet in 1 of 2 queries is op te lossen.

Verder moet je je afvragen waarom je uberhaupt meer als drie opslaat als je toch maar in 3 ge"interesseerd bent. Bij opslaan kijk je of er meer als twee entries zijn. Delete je het aantal entries - 2 en voeg je de huidige entry toe. Dit zorgt ervoor dat je altijd drie entries hebt en dat je database langzaam wordt opgeschoont. Een permanente oplossing.

[Verwijderd]

hamssteggot: volgens mij kan het wel, maar het is nu inderdaad niet echt netjes...

[MLM]

als dat 3 een constante is, dan zou ik in je users tabel
[user_id] [... wat je al had ...] [last_used1] [last_used2] [last_used3]
doen of zoiets, misschien niet echt elegant, maar werkt wel
dan gebruik je
SELECT last_used1, last_used2, last_used3 FROM users WHERE user_id = <ID>
om ze op te halen en
UPDATE users SET <oudste veld> = <nieuwste veld> WHERE user_id = <ID>
om up te daten

(je moet de <oudste veld> waarde wel in PHP (of welke taal dan ook ) achterhalen)

[ Voor 7% gewijzigd door MLM op 05-06-2006 13:09 ]

[hamsteg]

De NOT IN is aan mijn aandacht ontsnapt. Maaar goed ik zoek ook alleen queries uit als ik ze nodig heb Ziet er goed uit ...

[hamsteg]

@MLM, nooit doen straks moet je naar 5 of 10 en dan?

Hou tabellen elementair, kost in het begin iets meer werk in opzetten maar betaalt zich 100% terug.

[MLM]

daarom staat er ook "als dat 3 een constante is"

dit soort tabellen verwerken kost minder resources, maar zijn niet geschikt als je dit wilt uitbreiden

alternatief gezien:
SELECT last_used FROM tabel WHERE user_id = <ID> ORDER BY last_used DESC LIMIT 3
en
DELETE FROM tabel WHERE user_id = <ID> ORDER BY last_used ASC LIMIT ((SELECT COUNT(*) FROM tabel WHERE user_id = <ID>) - 3)

(niet getest ofzo, maar ik neem aan dat het werkt)

[ Voor 6% gewijzigd door MLM op 05-06-2006 13:28 ]

[Clock]

Hmm, dat zou je moeten doen met een GROUP BY met een HAVING statement. En volgens mij is het dan het makkelijkst om dan een subquery icm met een IN te gebruiken. Maar helaas ondersteund MySQL (in neem maar even aan dat je dat gebruikt) geen subquery met een LIMIT icm met een IN.

Misschien kan het ook makkelijker, maar ik zou het zo snel niet zien.

MLM schreef op maandag 05 juni 2006 @ 13:27:
DELETE FROM tabel WHERE user_id = <ID> ORDER BY last_used ASC LIMIT ((SELECT COUNT(*) FROM tabel WHERE user_id = <ID>) - 3)

Dat is ook een mooie. Daar moet wel iets mee te doen zijn

[ Voor 30% gewijzigd door Clock op 05-06-2006 13:31 ]

[NMe]

Verwijderd schreef op maandag 05 juni 2006 @ 12:55:
Heb je ook nog een sleutelveld?

Anders zoiets:

SQL:

DELETE FROM table WHERE record_id NOT IN 
(SELECT record_id FROM table ORDER BY last_used ASC GROUP BY user_id HAVING COUNT(*) <= 3)

Ik denk ascending, anders descending

Die gaat, denk ik, niet werken. Ten eerste moet het sowieso DESC zijn, omdat je de hoogste waarden nodig hebt. Niet dat dat hier wat uitmaakt: doordat je een group by doet verlies je de id's die binnen die groep vallen. Je hebt dus per user_id één record dat je uiteindelijk niet verwijdert, waar dat er 3 moeten zijn.

MLM schreef op maandag 05 juni 2006 @ 13:27:
daarom staat er ook "als dat 3 een constante is"

Zelfs als het een "constante" is is het een ranzige oplossing. Dat heet nou denormalisatie en dat moet je alleen doen als je een verdomd goede reden hebt. Dit is geen verdomd goede reden. Niets is zo veranderlijk als de eisen van een klant. Als die straks ineens toch maar 5 van die dingen wil hebben, dan kun jij je datamodel en al je queries gaan aanpassen. Niet goed.

alternatief gezien:
SELECT last_used FROM tabel WHERE user_id = ORDER BY last_used DESC LIMIT 3
en
DELETE FROM tabel WHERE user_id = ORDER BY last_used ASC LIMIT ((SELECT COUNT(*) FROM tabel WHERE user_id = ) - 3)

(niet getest ofzo, maar ik neem aan dat het werkt)

Ik neem aan van niet. Voor zover ik weet mag je geen subquery in een limit gebruiken.

[reyan]

Ik ben er ondertussen al achter gekomen dat MySQL (4.1.19) weer niet wil meewerken. Subqueries die dezelfde tabel gebruiken als de hoofdquery zijn niet ondersteund. Dan krijg ik steevast de melding:

Error 1093
SQLSTATE = HY000
Message = "You can't specify target table 'tabel' for update in FROM clause"

Wat volgens de MySQL manual betekent:

This error occurs in cases such as the following:

UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1);

You can use a subquery for assignment within an UPDATE statement because subqueries are legal in UPDATE and DELETE statements as well as in SELECT statements. However, you cannot use the same table (in this case, table t1) for both the subquery's FROM clause and the update target.

Het zal dus met met een aantal queries moeten gebeuren

[NMe]

Temporary tables?

[hamsteg]

Normalisatie van tabellen, bedankt -NME-, ik kon er al niet meer op komen ... was dat niet een nederlander die daar wat voor uitgevonden had. NIAM ? Thiesen was zijn naam geloof ik.

[NMe]

hamsteggot schreef op maandag 05 juni 2006 @ 14:30:
Thiesen was zijn naam geloof ik.

offtopic:
Die naam komt me vaag bekend voor, maar het was een Brit die als eerste het idee uitwerkte; Edgar F. Codd.

[Verwijderd]

Ow ja, tuurlijk, door mijn group by werkt het niet...

Denk dat het toch mooier is om gewoon te zorgen dat er nooit meer dan 3 zijn, is efficienter en dan hoef je nooit je table op te ruimen op deze manier.

[MLM]

laatste wat ik hier mee doe dan

PHP:

//deze moet je zelf ergens vandaan halen
$tabel = "tabel naam";
$userid = "user_id";

//tel aantal entries
$query = "SELECT COUNT(*) AS aantal FROM ".$tabel." WHERE user_id=".$userid;
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
$aantal = $row["aantal"];

//indien teveel ...
if($aantal > 3)
{
    //... opruimen
    $query = "DELETE FROM ".$tabel." WHERE user_id=".$user_id." ORDER BY last_used ASC LIMIT ".($aantal - 3);
    mysql_query($query);
}

[reyan]

Ik weet wel dat het beter is om deze situatie te vermijden bij het invoegen, maar het probleem is dat het invoegen gebeurt door een applicatie waarvan we geen broncode hebben (en de originele auteur heeft geen "zin" meer om ze verder te ontwikkelen).Toch zijn we erg tevreden over dit programmaatje.

Maar na jaren en jaren gebruik bestaan sommige (ondertussen erg grote) tabellen uit meer dan 99% niet langer relevante data. Daarom dat we een opschoonmechanisme rond het programmaatje hebben gemaakt.

In één query lukt blijkbaar niet met MySQL. Niet erg, ik heb het ondertussen al met enkele queries opgelost en het werkt goed. Ik wou gewoon weten of het misschien mogelijk was met één query.

Toch bedankt voor het meedenken