[MySQL] Distinct? Join? Group by? Huuu?

[Verwijderd]

Hellup! We snappen het niet meer!!!

Maar voordat ik mijzelf van het balkon werp zal ik het probleem beschrijven.

We hebben de volgende tables:

code:

mysql> explain topics;
+------------+------------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra     |
+------------+------------------+------+-----+---------+----------------+
| id       | int(11)        |   | PRI | NULL    | auto_increment |
| postid     | int(10) unsigned | YES  |     | NULL    |            |
| subject    | char(64)    | YES  |     | NULL    |         |
| flags | int(11)       | YES  |     | NULL    |            |
| forumid    | int(10) unsigned | YES  |     | NULL    |            |
| lastpostid | int(10) unsigned | YES  |     | NULL    |            |
+------------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> explain posts;
+--------------+------------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra     |
+--------------+------------------+------+-----+---------+----------------+
| id         | int(11)      |   | PRI | NULL    | auto_increment |
| authorid     | int(10) unsigned | YES  |     | NULL    |          |
| timestamp    | datetime      | YES  |     | NULL    |         |
| edited     | int(11)      | YES  |     | NULL    |            |
| editedtime   | datetime      | YES  |     | NULL    |         |
| editeduserid | int(10) unsigned | YES  |     | NULL    |          |
| ip         | char(16)    | YES  |     | NULL    |         |
| messageid    | int(10) unsigned | YES  |     | NULL    |          |
| topicid   | int(10) unsigned | YES  |     | NULL    |         |
+--------------+------------------+------+-----+---------+----------------+

Nu willen wij in een query voor de searchengine (op topicstarter) als resultaat topics.* krijgen waar (als voorbeeld):

topics.forumid = 5
posts.authorid = 1
posts.topicid = topics.id

maar we moeten maar 1 posts.authorid hebben, namelijk de eerste van het topic (dus degene met de laagste posts.timestamp), alleen we komen er niet uit... we krijgen nu elke post in het topic waarvan de authorid 1 is, omdat al deze posts als topicid 8 hebben en topic.forumid 5 is...

eigenlijk zouden we dus gewoon de lijst met posts moeten orderen op timestamp en dan limitten tot 1... maarjah...

we hebben nu iets als

code:

SELECT topics.* from topics, posts where topics.forumid = 5 AND posts.authorid = 1 AND posts.topicid = topics.id;

en dat zou in psuedo code zoiets worden als

code:

SELECT topics.* from topics, posts where topics.forumid = 5 AND posts.authorid = 1 AND posts.topicid = topics.id AND posts.timestamp = laagste van dit topic;

Maar... helaasch ontdekten wij na lang zoeken dat er geen mysql functie "laagste van dit topic" bestaat

En plz kom niet aan met nuttige tips als "RTFM" en "UTFS" want dat zitten we al uren te doen.

Tnx alvast

[Janoz]

Max, Min icm Group by?

[TheRookie]

via 6.3 Functions for Use in SELECT and WHERE Clauses kwam ik hier terecht, waar men het over een LEAST functie heeft .....

[Verwijderd]

Op zondag 20 januari 2002 21:26 schreef Janoz het volgende:
Max, Min icm Group by?

uh hoe zou dat er uit moeten gaan zien ?

least is wel erg leuk enzw... alleen het probleem is dat we niet zoiets kunnen doen als least(timestamp) omdat least gewoon tussen meerdere parameters de laagste zoekt... niet tussen de laagste van meerdere resultaten... uhm.. ben ik nog begrijpbaar ofzow want bier is futile

snif ween

[Janoz]

SELECT topics.*, MIN(posts.timestamp)
FROM topics, posts
WHERE topics.forumid = 5 AND posts.authorid = 1 AND posts.topicid = topics.id
GROUP BY topics.id;

[Verwijderd]

code:

mysql> SELECT topics.*, MIN(posts.timestamp)
    -> FROM topics, posts
    -> WHERE topics.forumid = 5 AND posts.authorid = 1 AND posts.topicid = topics.id
    -> GROUP BY topics.id;
+-----+--------+------------------------------------------------------------------+-------+---------+------------+----------------------+
| id  | postid | subject                                        | flags | forumid | lastpostid | MIN(posts.timestamp) |
+-----+--------+------------------------------------------------------------------+-------+---------+------------+----------------------+
|  15 |     65 | icq 2001 Alpha                                |     0 |     5 |     3774 | 2001-10-10 03:22:58  |
|  65 |    482 | GD-library's updaten?!?                            |     0 |    5 |      795 | 2001-10-16 12:29:31  |
|  75 |    548 | De eeuwen oude vraag: wat is het beste mp3 seach and downloadprg |     0 |  5 |     1735 | 2001-10-16 03:04:11  |
| 153 |   1210 | Windooz XP NL                                  |     0 |    5 |     1706 | 2001-11-03 15:47:10  |
| 223 |   1982 | acrobat writer                                |     0 |     5 |     2252 | 2001-11-17 06:39:16  |
| 311 |   3022 | mirc java webchat versie                            |     0 |   5 |     4245 | 2001-12-26 10:02:09  |
| 324 |   3414 | Zuurstokkleurtjes whoei                            |     0 |    5 |     4244 | 2001-12-26 10:01:40  |
| 362 |   3891 | Mirc finger                                    |     0 |    5 |     4243 | 2002-01-03 11:00:31  |
| 407 |   4405 | XP of 2000pro?                                |     0 |     5 |     4620 | 2002-01-17 06:21:53  |
| 433 |   4695 | ISOtje branden??                                |     0 |   5 |     4735 | 2002-01-20 04:41:38  |
+-----+--------+------------------------------------------------------------------+-------+---------+------------+----------------------+
10 rows in set (0.02 sec)

mysql> SELECT topics.*, posts.timestamp
    -> FROM topics, posts
    -> WHERE topics.forumid = 5 AND posts.authorid = 1 AND posts.topicid = topics.id
    -> GROUP BY topics.id;
+-----+--------+------------------------------------------------------------------+-------+---------+------------+---------------------+
| id  | postid | subject                                        | flags | forumid | lastpostid | timestamp       |
+-----+--------+------------------------------------------------------------------+-------+---------+------------+---------------------+
|  15 |     65 | icq 2001 Alpha                                |     0 |     5 |     3774 | 2001-10-10 03:22:58 |
|  65 |    482 | GD-library's updaten?!?                            |     0 |    5 |      795 | 2001-10-16 12:29:31 |
|  75 |    548 | De eeuwen oude vraag: wat is het beste mp3 seach and downloadprg |     0 |  5 |     1735 | 2001-10-16 03:04:11 |
| 153 |   1210 | Windooz XP NL                                  |     0 |    5 |     1706 | 2001-11-03 15:47:10 |
| 223 |   1982 | acrobat writer                                |     0 |     5 |     2252 | 2001-11-17 06:39:16 |
| 311 |   3022 | mirc java webchat versie                            |     0 |   5 |     4245 | 2001-12-26 10:02:09 |
| 324 |   3414 | Zuurstokkleurtjes whoei                            |     0 |    5 |     4244 | 2001-12-26 10:01:40 |
| 362 |   3891 | Mirc finger                                    |     0 |    5 |     4243 | 2002-01-03 11:00:31 |
| 407 |   4405 | XP of 2000pro?                                |     0 |     5 |     4620 | 2002-01-17 06:21:53 |
| 433 |   4695 | ISOtje branden??                                |     0 |   5 |     4735 | 2002-01-20 04:41:38 |
+-----+--------+------------------------------------------------------------------+-------+---------+------------+---------------------+
10 rows in set (0.02 sec)

dat is em dus niet... nu krijg je de minimale timestamp per row en daar er maar een timestamp is per row is dat altijd dezelfde...

voor de duidelijkheid; dit zijn dus niet allemaal topics van dezelfde user... (ofwel waar de eerste post in het topic van user met id 1 is)

[Janoz]

Dan lijkt me de limit 1 oplossing toch het beste werken (Ik ben er trouwens nog steeds niet helemaal achter wat nu precies de bedoeling van je query moet zijn eigenlijk...)

[Verwijderd]

Op zondag 20 januari 2002 22:04 schreef Janoz het volgende:
Dan lijkt me de limit 1 oplossing toch het beste werken (Ik ben er trouwens nog steeds niet helemaal achter wat nu precies de bedoeling van je query moet zijn eigenlijk...)

de bedoeling is dat we een lijstje krijgen met topics waarvan de topicstarter userid 1 heeft.
en de eigenschap van een topicstarter is dat hij de laagste timestamp heeft in dat topic.
met limit 1 krijg je dus maar 1 topic maar die topicstarter kan best hondermiljoen topics gestart hebben dus dat gaat niet werken snap je

[drZymo]

Wat wil je precies? Gewoon degene die als laatste gereageert heeft of degene die de topic geopent heeft?

Beide oplossingen kan je mischien beter opslaan in de topics tabel zelf. Een extra veld met de naame userid of lastuserid of iets dergelijks. Werkt mischien wat makelijker.

[Janoz]

Ah, als je alle topics wil die een bepaalde user gestart heeft, dan kun je idd beter zoals hierboven is vermeld in het topic een extra veld voor de starter opnemen.. Anders zul je waarschijnlijk met subqueries moeten gaan werken, en dat ondersteund mysql niet..

[Verwijderd]

we hebben ook al een veld 'lastpostid'... als we nog meer functies gaan inbouwen hebben we strax voor alles waar we geen query op kunnen vinden een extra veld nodig... beetje inefficient? kan het echt niet anders??

[Verwijderd]

een subquery kan idd niet in mysql. Maar een subquery is niets meer dan een query een input geven van een ander query. Je kunt natuurlijk ook een query een input geven van een ander query door met variabele te werken. Ik zou graag willen helpen maar ik begrijp de vraag nog niet super goed. succes

[OxiMoron]

kijk ook eens naar ORDER BY timestamp [ASC/DESC] [LIMIT start,stop]

Dan zet hij het ook gewoon in volgorde en geef je (optioneel) een limit op.

[drm]

Volgens mij zat * Janoz heel dicht in de buurt, maar vergat 1 where clausuletje

code:

SELECT 
     topics.*, 
     MIN(posts.timestamp) AS firstPost # <-- die

FROM 
     topics, 
     posts

WHERE 
     topics.forumid = 5 
AND  posts.authorid = 1 
AND  posts.topicid = topics.id
AND  posts.timestamp = firstPost  # <-- die

GROUP BY 
     topics.id;

even op de vroege ochtend

don't shoot me if i'm wrong

[_-= Erikje =-_]

ik neem aan dat je losse posts opslaat met een topic_id?



dan kun je gewoon van een topic de laagste post_id pakken, das altijd de starter namelijk.

je moet dus groeperen op topic_id en daar de laagste post_id van pakken.

[drm]

_-= Erikje =-_:
ik neem aan dat je losse posts opslaat met een thread_id?

dan kun je gewoon van een thread de laagste post_id pakken

niet doen. Het ID veld heeft namelijk niet als betekenis dat het de tijd van posten relatief aan andere posts bevat.

In theorie zou het namelijk zo kunnen zijn dat je post_id lager is dan dat van een andere post, terwijl het toch later gepost is, snappie? Da's dus puur qua ontwerp op z'n minst niet netjes.

En dan nog lost dat het probleem niet op want dan heb je ipv. min(timestamp) min(post_id) nodig... in feite hetzelfde

[Goodielover]

Wat is de bedoeling van de post_id in topic?
Je werkt met auto increment, dus je zou ook het laagste post_id kunnen nemen.

code:

select t.*
from   topic t
    ,post p1
    ,post p2
where  t.id = ...
and    p1.topicid = t.id
and    p1.author_id = ...
and    p2.topicid = t.topicid
and    p2.id <= p1.id
group by t.*
having count(p2.id) = 1

Ik ben te lui geweest naar je tabeldefinities te kijken qua exacte naamgeving, maar deze doet het volgens mij.
Normaal zou ik een subquery gebruiken, maar dat ondersteund mysql (nog) niet.

[drm]

Goodielover:
Wat is de bedoeling van de post_id in topic?
Je werkt met auto increment, dus je zou ook het laagste post_id kunnen nemen.

Zie mijn post hierboven.

[Goodielover]

Ok drm hierbij dan zonder id vergelijking

code:

select t.*
from   topic t
    ,post p1
    ,post p2
where  t.id = ...
and    p1.topicid = t.id
and    p1.author_id = ...
and    p2.topicid = t.topicid
and    p2.timestamp<= p1.timestamp
group by t.*
having count(p2.id) = 1

Wat vind jij verder van mijn oplossing?

[drm]

Goodielover:
Wat vind jij verder van mijn oplossing?

ik denk dat dit een redelijk zware query is voor dit probleem... Zou je even moeten testen, maar ik denk dat deze query behoorlijk wat tijd kost vergeleken bij een limit 0,1 -oplossing

code:

SELECT     topics.*,
         posts.timestamp
FROM     topics, 
         posts
WHERE   topics.id=5
     AND posts.authorid = 1
     AND posts.topicid = topics.id
ORDER BY   posts.timestamp ASC
LIMIT   0,1;

prima query, toch

[Orphix]

Op maandag 21 januari 2002 13:50 schreef drm het volgende:

[..]

ik denk dat dit een redelijk zware query is voor dit probleem... Zou je even moeten testen, maar ik denk dat deze query behoorlijk wat tijd kost vergeleken bij een limit 0,1 -oplossing

code:

SELECT     topics.*,
         posts.timestamp
FROM     topics, 
         posts
WHERE   topics.id=5
     AND posts.authorid = 1
     AND posts.topicid = topics.id
ORDER BY   posts.timestamp ASC
LIMIT   0,1;

prima query, toch

Maar op deze manier kan je slechts 1 topic tegelijk laten zien. Terwijl dezelfde topic starter veel meer topics kan hebben gestart.

Baz_ ik zou gewoon een extra veld 'topicstarter' toevoegen. Dit kost nauwelijks extra geheugen en geeft je veel meer flexibiliteit. Hier op GoT lijkt het me ook dat niet voor elk topic de 'post met de laagste timestamp' wordt gezocht, is gewoon te intensief.