Keskustelu: Nettisivujen teko: SQL (& PHP): Käyttäjän kavereiden tietojen haku

Hei!

Minulla on tietokanta, joka sisältää taulut kaverit ja kayttajat.

Kaverit-taulu voisi näyttää esimerkiksi tältä:

   kaveri_yksi  |  kaveri_kaksi
---------------------------------
        1       |       2
        2       |       1
        1       |       3
        3       |       1
        1       |       4
        4       |       1
Jatkuu, jatkuu...

Käyttäjät näyttäisivät vaikka tältä:

      id      |      nimi       |      osoite        |    paikalla
       1      |     Jaakko      |     Testikuja 1    |       0
       2      |      Mari       |    Keksijäntie 5   |       1
       3      |      Kaija      |     Erottaja 1     |       1
       4      |      Juho       |     Erottaja 2     |       0

Tarvitsisin saada kaikki Jaakon kaverit, jotka ovat paikalla. (Ylemmässä tilanteessa pitäisi saada Mari sekä Kaija). Listaa kuitenkin jatkettaisi pidempään, ja käyttäjiä voisi olla vaikka 100. Kavereitakin olisi sillon paljon enemmän. Jos Jaakolla onkin toisessa skenaariossa 50 kaveria, mutta vain 3 paikalla, ensimmäisenä näytetään ne 3, jotka on paikalla, mutta sen jälkeen sattumanvaraisesti 7 muuta, jotta rivejä tulee yhteensä 10.

Tässä koodi, joka ei toimi, ja jonka ei ole edes tarkoitus toimia. Ehkä saat siitä paremman käsityksen:

SELECT * FROM kayttajat WHERE kayttajaOnKaverina AND id != omaid ORDER BY paikalla LIMIT 10

Kiitos vastauksista! En yllättyisi, vaikka tämä olisikin todella helppo, mutta nyt vaan ne aivosolut ei saa ratkaistua tätä ongelmaa. Kiitos jo vastauksista!

Kaverit saat haettua yksinkertaisella liitoksella:

SELECT * FROM kayttajat
JOIN kaverit ON
  (kaverit.kaveri_yksi = kayttajat.id AND kaverit.kaveri_kaksi = ?)
  OR (kaverit.kaveri_yksi = ? AND kaverit.kaveri_kaksi = kayttajat.id)

Ensin kannattaa hakea ne, jotka ovat paikalla.

WHERE paikalla
LIMIT 10 -- jos et halua kaikkia

Tämän jälkeen voi laskea, montako puuttuu, ja hakea joukon satunnaisia, jotka eivät ole paikalla. Rajaavaksi ehdoksi tulee siis helposti seuraava:

WHERE NOT paikalla

Tai, jos haluat hienosti huomioida mahdollisuuden, että jonkun käyttäjän tila on juuri näiden kyselyiden välissä muuttunut, voit listata ehtoon jo valitut käyttäjät:

WHERE kayttajat.id NOT IN (valittu_1, valittu_2, valittu_N)

Satunnaisuuteen on erilaisia ratkaisuja: helppo ratkaisu on hidas, kun taas nopea ratkaisu on vaikeampi.

Helppo ratkaisu on laittaa järjestystavaksi ORDER BY RAND(). Tässä kuitenkin tietokanta joutuu tekemään paljon ylimääräistä työtä: Joka riville luodaan satunnaisluku, ja rivit järjestetään luvun mukaan, jotta voidaan valita muutama ensimmäinen. Optimoitu tietokanta saattaisi jossain tilanteessa arpomisen jälkeen vain etsiä joukosta pienimmät luvut, mutta ei sekään ole nopeaa. Jos kuitenkin kaverimäärä on varmasti pieni (enintään sadan luokkaa), tämäkin ratkaisu toimii. Loppuun tulee tietenkin LIMIT lisäkavereiden_määrä.

ORDER BY RAND() LIMIT n

Suuresta joukosta voi olla tehokkaampaa tehdä arvonta rivimäärän mukaan: ensin katsotaan COUNT-kyselyllä, paljonko kavereita on, ja sitten vain asetetaan LIMIT niin, että saadaan satunnaisesta kohdasta muutama kaveri. Tämä tietenkin vääristää saatavaa jakaumaa, koska valitut rivit ovat peräkkäisiä, mutta usein asialla ei ole merkitystä.

LIMIT määrä OFFSET satunnainen

Yksi mahdollisuus on myös laittaa jo hakuehdoksi suodatus satunnaisluvun mukaan ja tarvittaessa toistaa kysely pari kertaa, jos suodatus on liian tiukka.

Kiitos, pääsin tällä loistavasti alkuun!