[SQL] Maximum aantal rows per unieke FK

[Verwijderd]

Hallo allemaal,

Ben bezig met een forum en natuurlijk gaat dat niet probleemloos. Het lijkt zo simpel, en toch lukt het niet. Volgens mij kijk ik ergens keihard overheen.

Wat ik probeer te bereiken: per forumcategorie wil ik een maximum aantal topics selecteren. Uiteraard kan dat met meerdere query's (eerst een query om alle forumcategorieën te selecteren, daarna voor elke forumcategorie een query voor een max aantal topics), maar het liefst doe ik dat in één query. Als dat kan natuurlijk, en volgens mij moet dat kunnen.

Ik heb in totaal drie tabellen: fora, topics en posts. In dit geval heb ik dus alleen fora en topics nodig. De tabel topics heeft een FK forum_id, die vanzelfsprekend naar de tabel fora verwijst.

Doel is dus om per forum_id een maximum aantal rijen (laten we zeggen, een stuk of 10) te selecteren, gesorteerd op datum. GROUP BY is niet de oplossing volgens mij, die is alleen voor aggregate functions bedoeld. ORDER BY komt er dichter bij in de buurt, maar dan ben je er nog niet: hoe zorg je er voor dat-ie er max 10 pakt?

Ik heb het idee dat de oplossing echt doodsimpel is. Ik zie het gewoon niet!

Bij voorbaat dank

[Verwijderd]

en ik moet beter lezen

[ Voor 94% gewijzigd door Verwijderd op 15-05-2004 19:10 ]

[ludo]

code:

SELECT * FROM topics WHERE forum_id = <id> ORDER BY date LIMIT 0,10

[ Voor 14% gewijzigd door ludo op 15-05-2004 19:28 ]

[blizt]

max 10 lijkt mij limit?

edit:

Ludo

[ Voor 20% gewijzigd door blizt op 15-05-2004 19:29 ]

[Verwijderd]

ludo schreef op 15 mei 2004 @ 19:27:

code:

SELECT * FROM topics WHERE forum_id = <id> ORDER BY date LIMIT 0,10

Dan krijg je dus in totaal maximaal 10 rows van slechts één forumcategorie. Ik zal waarschijnlijk ergens tussen de 5 en 15 verschillende forumcategorieën hebben, waarvan ik per stuk maximaal tien topics van wil selecteren. En het liefst dus in één query.

[nescafe]

Wat ik heb gedaan:

Start google en ga [google=limit per group] waarbij we een aantal resultaten krijgen:
http://forums.devshed.com/archive/t-115426
http://www.webmasterworld.com/forum88/2850.htm
http://www.mail-archive.com/[e-mail]/msg46252.html

Deze laatste link is erg interessant en laat het gebruik van variabelen in queries zien. Ik maak een tabel "posts" met een forumId, een userId en een timestamp. Ik neem aan dat je er een paar velden bij wilt hebben, maar dit is voor het idee:

code:

mysql> select * from posts;
+---------+--------+----------------+
| forumId | userId | date           |
+---------+--------+----------------+
|       1 |      1 | 20040516011949 |
|       1 |      2 | 20040516011951 |
|       1 |      3 | 20040516011954 |
|       2 |      3 | 20040516012012 |
|       2 |      1 | 20040516012014 |
|       3 |      1 | 20040516012017 |
|       3 |      5 | 20040516012018 |
|       3 |      4 | 20040516012021 |
+---------+--------+----------------+
8 rows in set (0.00 sec)

Maak nu een query waarbij in elke regel wordt bijgehouden wat de vorige forumId was:

code:

mysql> set @lastForumId = 0; select forumId, userId, date, @lastForumId,
    -> @lastForumId:=forumId from posts;
Query OK, 0 rows affected (0.00 sec)

+---------+--------+----------------+--------------+-----------------------+
| forumId | userId | date           | @lastForumId | @lastForumId:=forumId |
+---------+--------+----------------+--------------+-----------------------+
|       1 |      1 | 20040516011949 |            0 |                     1 |
|       1 |      2 | 20040516011951 |            1 |                     1 |
|       1 |      3 | 20040516011954 |            1 |                     1 |
|       2 |      3 | 20040516012012 |            1 |                     2 |
|       2 |      1 | 20040516012014 |            2 |                     2 |
|       3 |      1 | 20040516012017 |            2 |                     3 |
|       3 |      5 | 20040516012018 |            3 |                     3 |
|       3 |      4 | 20040516012021 |            3 |                     3 |
+---------+--------+----------------+--------------+-----------------------+
8 rows in set (0.01 sec)

Maak nu een query waarbij je een tellertje @a op laat lopen, tenzij het laatste forum anders is dan het huidige forum. Zet hem dan weer op 1.

code:

mysql> select forumId, userId, date, @a:=IF(@lastForumId=forumId, @a+1, 1) countPerForumId,
    -> @lastForumId:=forumId from posts;
+---------+--------+----------------+-----------------+-----------------------+
| forumId | userId | date           | countPerForumId | @lastForumId:=forumId |
+---------+--------+----------------+-----------------+-----------------------+
|       1 |      1 | 20040516011949 |               1 |                     1 |
|       1 |      2 | 20040516011951 |               2 |                     1 |
|       1 |      3 | 20040516011954 |               3 |                     1 |
|       2 |      3 | 20040516012012 |               1 |                     2 |
|       2 |      1 | 20040516012014 |               2 |                     2 |
|       3 |      1 | 20040516012017 |               1 |                     3 |
|       3 |      5 | 20040516012018 |               2 |                     3 |
|       3 |      4 | 20040516012021 |               3 |                     3 |
+---------+--------+----------------+-----------------+-----------------------+
8 rows in set (0.00 sec)

Het enige wat je nu wil is countPerForumId kleiner dan 3 houden. Helaas kan dit niet in een WHERE (je weet niet wanneer deze wordt uitgevoerd, voor/na het berekenen van de velden en je kan geen toegewezen veldnaam als parameter in je where-clausule gebruiken) en bovendien moet je nog een ORDER BY toepassen zodat ie alle forumId's netjes op een rij houdt. Na even proberen (zonder group by werkt die bijvoorbeeld niet, dat zal met het sorteren te maken hebben) heb ik de volgende query gebakken welke de volgende resultaten geeft:

code:

mysql> select forumId, userId, date, @a:=IF(@lastForumId=forumId, @a+1, 1) countPerForumId,
    -> @lastForumId:=forumId from posts group by forumId, userId, date
    -> having countPerForumId < 3 order by forumId, date desc;
+---------+--------+----------------+-----------------+-----------------------+
| forumId | userId | date           | countPerForumId | @lastForumId:=forumId |
+---------+--------+----------------+-----------------+-----------------------+
|       1 |      2 | 20040516011951 |               2 |                     1 |
|       1 |      1 | 20040516011949 |               1 |                     1 |
|       2 |      1 | 20040516012014 |               2 |                     2 |
|       2 |      3 | 20040516012012 |               1 |                     2 |
|       3 |      5 | 20040516012018 |               2 |                     3 |
|       3 |      1 | 20040516012017 |               1 |                     3 |
+---------+--------+----------------+-----------------+-----------------------+
6 rows in set (0.01 sec)

Ik zie nu alleen dat dit niet de gewenste resultaten geeft m.b.t. de sortering (in forum 3 zou timestamp 20040516012021 zichtbaar moeten zijn bijv.). De velden worden berekend vóór het sorteren, wat natuurlijk ook logisch is.

Misschien duw ik je zo een eindje in de goede richting (je query wordt alleen wel behoorlijk databasetype-afhankelijk). Heeft iemand anders nog ideeën?