Keskustelu: Nettisivujen teko: Käyttäjien top -lista

Otsikko ei nyt ollut paras mahdollinen.. Perimmäinen ongelma on mikä on järkevin tapa hakea käyttäjät taulusta, jotta saa helposti tutkittua montako kertaa ketäkin henkilöä taulusta löytyi.
Olen lukenut tuon MySQL ja PHP -oppaan pariin kertaan läpi ja siltä pohjalta yrittänyt miettiä ratkaisua ongelmaan, mutten millään löydä oikeita hakuehtoja..

Minulla on taulussa useita sarakkeita, joista tiettyihin tulee käyttäjien id:tä. Millä hakuehdoilla saisin laskettua montako kertaa kukin id esiintyy koko taulussa?

Tavoite on tehdä top-lista, jossa näkyy montako kertaa kukin käyttäjä on esiintynyt taulussa.

Esim.

Taulu josta haetaan henkilöiden id:t

id  sarake1  sarake2  sarake3  pv
1    1        1        4       2010-11-28
2    2        3        4       2010-11-30
3    3        4        1       2010-12-09

Henkilöt taulussa

id   nimi
1    Matti
2    Maija
3    Heikki
4    Hanna

Käyttäjät listattuna

1. - 3 kpl - Hanna ja Matti *
3. - 2 kpl - Heikki
4. - 1 kpl - Maija

* Tasatilanteessa käyttäjät järjestetään aakkosjärjestykseen

Voisitteko antaa vinkkejä miten tällaisen saisi toteutettua mahdollisimman yksinkertaisesti..

Piti vähän aikaa tutkia mitä tuossa tapahtuu, mutta luulisin saaneeni tuosta nyt kiinni. Eikö tuo COUNT(taulu.id) käy läpi ainoastaan sarakkeen 'id', vaikka pitäisi käydä läpi nuo kolme saraketta (sarake1, sarake2 ja sarake3)?

Entä mistä tuohon on saatu taulu.henkilo_id? Mistä siis tulee tuo "henkilo_id"? Miten tuosta ensimmäisestä taulusta saadaan kolmesta eri sarakkeesta ulos henkilöiden id:t? DISTINCT(taulu.sarake1, taulu.sarake2, taulu.sarake3) tai jotain..??

Ajattelin, että osaisit hieman soveltaakin neuvoa, joten en vaivautunut käyttämään samaa taulun nimeä ja rakennetta.

COUNT laskee, montako ei-NULL-arvoa tulee vastaan. Siksi id on hyvä valinta sarakkeeksi: sen ei pitäisi koskaan olla NULL, paitsi tällaisen JOIN-kyselyn kohdalla, jos yhtään sopivaa riviä toisesta taulusta ei löydy. Useammin nähty COUNT(*) toimisi tässä väärin, koska se palauttaisi yhden osuman myös silloin, kun henkilöä ei löydy lainkaan.

Jos rivin kolme henkilöä eivät voi olla samoja (näin oletin), voit yksinkertaisesti muuttaa yhtäsuuruusvertailun IN-ehdoiksi tai yhdistää nuo kolme erillistä vertailua OR-sanalla.

LEFT JOIN taulu ON henkilo.id IN (taulu.sarake1, taulu.sarake2, taulu.sarake3)

LEFT JOIN taulu ON
  henkilo.id = taulu.sarake1 OR
  henkilo.id = taulu.sarake2 OR
  henkilo.id = taulu.sarake3

(Saattaa olla, että palvelin suorittaa jälkimmäisen näistä nopeammin, vaikka merkitys on aivan sama.)

Jos kuitenkin on mahdollista, että jollain rivilä on kolme Heikkiä ja ne kaikki halutaan laskea erikseen mukaan, lausekkeesta tulee mutkikkaampi. Yksi toteutusvaihtoehto on jokseenkin tällainen:

SELECT
  henkilo.*,
  SUM(
    IF(taulu.sarake1 = henkilo.id, 1, 0) +
    IF(taulu.sarake2 = henkilo.id, 1, 0) +
    IF(taulu.sarake3 = henkilo.id, 1, 0)
  ) AS maara
FROM henkilo
LEFT JOIN taulu ON henkilo.id IN (taulu.sarake1, taulu.sarake2, taulu.sarake3)
GROUP BY henkilo.id
ORDER BY maara DESC, nimi ASC

Toinen vaihtoehto on käyttää alikyselyitä, mutta tätä pidetään usein rumana ja hitaana tapana:

SELECT
  henkilo.*,
  (
    (SELECT COUNT(*) FROM taulu WHERE taulu.sarake1 = henkilo.id) +
    (SELECT COUNT(*) FROM taulu WHERE taulu.sarake2 = henkilo.id) +
    (SELECT COUNT(*) FROM taulu WHERE taulu.sarake3 = henkilo.id)
  ) AS maara
FROM henkilo
LEFT JOIN taulu ON henkilo.id IN (taulu.sarake1, taulu.sarake2, taulu.sarake3)
GROUP BY henkilo.id
ORDER BY maara DESC, nimi ASC

(En testannut mitään kyselyistä, joten pienet lipsahdukset ovat mahdollisia.)

Metabolix kirjoitti:

Ajattelin, että osaisit hieman soveltaakin neuvoa

Painosanalla hieman. :)

Minua jäikin COUNT:ssa ihmetyttämään se, kun katsoit montako riviä taulussa (COUNT(taulu.id)) on, joka ei mielestäni liity käyttäjiin millään tavoin. Yhdellä rivillä kun voi olla (tai onkin) useita käyttäjien id:tä. Kai se meni kuitenkin niin kuin tarkoitinkin?

Metabolix kirjoitti:

Jos rivin kolme henkilöä eivät voi olla samoja (näin oletin)

Henkilöt voivat olla samoja. Tämä tuli esille esimerkissänikin, jossa ensimmäisellä rivillä oli kaksi samaa id:tä.

Tämä IF:eillä tehty haku oli täsmälleen sitä mitä hainkin. Tein siis tuolla tavoin ja toimi täydellisesti! Kiitos. :) Plussaa tuossa on vielä se, että ymmärrän hyvin mitä tapahtuu missäkin kohtaa..

Taulukossa olevat käyttäjien id:t voivat olla myös nollia (ei käyttäjää). Miten tuo haku suhtautuu niihin? Vastinetta nolla-id:lle ei tietenkään löydy käyttäjien taulusta, joten nollat jäävät tieten huomiotta niin kuin kuuluukin?

Tästä onkin todella helppo tehdä lista, kun tuloksia ei tarvitse millään tavoin lajitella tms. Kiitos tuhannesti! Jatkan tästä, kunhan saan yön yli nukuttua. :)