Keskustelu: Nettisivujen teko: MySQL-haku kuvista

Minulla on sivuilla lomake, jolla voi hakea tietokannasta löytyviä kuvia haluamillaan hakuehdoilla. Hakuehdoiksi voi laittaa kansion johon kuva kuuluu, henkilön, joka kuvassa esiintyy ja kaksi vuosilukua, joiden väliin kuvan täytyy kuulua.

Lähetän lomakkeen tiedot yksinkertaisesti get:llä ja PHP:llä sitten kirjoitan SQL-kyselyn oikeaan muotoon. Ongelmana on nyt se, etten keksi järkevintä tapaa hakea kuvia tietokannasta.

Tietokannassa on neljä taulua pictures, pics_album, pics_people ja people.
Pictures sisältää jokaisen kuvan tiedot, people jokaisen henkilön tiedot, pics_album sisältää kuvan id:n (picture) sekä kansion id:n (album) ja pics_people sisältää kuvan id:n (picture) sekä henkilön id:n (person).

Yritin kaikenlaisia purkkaviritelmiä kehitellä ja alla on niistä viimeisin. Ongelmana erilaisilla viritelmilläni on, että kuva saattaa tulla useaan kertaan, jos se sisältää useita henkilöitä tai kuuluu moneen kansioon, tai kuvaa ei tule ollenkaan, jos siinä ei esiinny henkilöitä.

SELECT pictures.id, pictures.name, pictures.description, pictures.year, pics_album.album
FROM pictures, pics_album, pics_people, people
WHERE
	(pictures.id = pics_album.picture OR pictures.id = pics_people.picture)
	AND pics_people.person = people.id
	AND ((people.firstname = ? AND people.lastname = ?))
	AND (pictures.year BETWEEN ? AND ?)
GROUP BY pictures.id

$values = array($firstname, $lastname, $first_year, $last_year);
$contact->execute($values);

Jotain alkeellisia virheitä tuosta varmastikin löytyy..
Pitäisiköhän kokeilla käyttää JOIN:ia tuolla välissä?

Opasta ongelmaan: https://www.ohjelmointiputka.net/oppaat/opas.php?tunnus=mysqlphp08

Lisäksi sinun ei pitäisi laittaa parametriksi henkilöä, jos haettavissa kuvissa ei ole henkilöä, tai tehdä henkilö 'ei henkilöä'. Voit tarkistaa tuloksesta pois samat kuvat.

Olenhan minä tuon oppaan lukenut ja nuo pics_album ja pics_people onkin tehty siltä pohjalta kertomaan ketä henkilöitä kuvissa esiintyy ja mihin kansioihin kuvat kuuluvat. En vain keksi, että millaisilla ehdoilla tämä (minulle) monimutkainen haku pitäisi tehdä.

Jos en laita parametriksi henkilöä niin silloinhan kuvien hakeminen henkilön nimellä ei onnistu halutulla tavalla, vai olenko väärässä? Pitäisikö siis laittaa pics_people tauluun jokaiselle kuvalle vielä erillinen rivi, jossa on kuvan id ja henkilön id:nä esim. 0? Ei kuulosta kovin järkevältä.

Eikös tuloksesta lähde pois samat kuvat tuolla GROUP BY pictures.id?

En nyt jaksa miettiä kantarakennettasi tai mistä tarkalleen haetaan (eihän esittämässäsi kyselyssäsi edes ole mitään albumiin liittyvää ehtoa), mutta pikaisesti ajateltuna LEFT JOIN voisi auttaa:

SELECT pictures.* FROM pictures
LEFT JOIN pics_album ON pics_album.picture = pictures.id AND pics_album.album = ?
LEFT JOIN pics_people ON pics_people.picture = pictures.id
LEFT JOIN people ON pics_people.person = people.id AND people.firstname = ? AND people.lastname = ?
WHERE
  (pics_album.album IS NOT NULL OR people.id IS NOT NULL)
  AND pictures.year BETWEEN ? AND ?
GROUP BY pictures.id

Ahaa.. Nyt kun tuon näkee valmiina pakettina niin huomaa, että tottakai se menee noin. :D Sitä kun ei vain osaaminen vielä riitä tuollaisen kokoamiseen. Valmiina pakettina sitä kyllä osaa käyttää. Ja se albumiin liittyvä ehto tosiaankin jäi vahingossa tuosta pois. Eipä sillä näin jälkikäteen ole enää merkitystä.

Enää tuosta Metabolixin antamasta kyselystä jäi epäselväksi kohta:

(pics_album.album IS NOT NULL OR people.id IS NOT NULL)

Eikö tuon people.id:n pitäisi olla pics_people.person? Ja silloinhan se katsoo varmaan, että kuva joko löytyy jostain kansiosta tai siinä esiintyy joitain henkilöitä?? Haun pitäisi kuitenkin toimia niin, että kuva löytyy vaikka se ei kuuluisi mihinkään kansioon eikä siinä esiinny yhtään henkilöä. Eli se voisi löytyä esim. asettamalla pelkkä vuosiluku-hakuehtoihin. Toimiiko jos jätän tuon kooditageissa esittelemäni kohdan kokonaan pois?

AkeMake kirjoitti:

Eikö tuon people.id:n pitäisi olla pics_people.person?

Ei, koska kannastahan löytyy joka tapauksessa monta pics_people-riviä, joilla on oikea picture.id. Tässä halutaan tarkistaa nimenomaan, täyttikö joku noista henkilöistä nimiehdot.

Jos haluat, että tyhjällä nimellä haettaessa käytetään pelkkää vuosilukua, kannattaisi tarkistaa jo PHP:n puolella, onko nimi tyhjä, ja käyttää silloin eri kyselyä, jossa ei turhaan tehdä kaikkia JOINeja. Toinen vaihtoehto on laittaa tuohon mainitsemaasi kohtaan (people.id IS NOT NULL OR (? = "" AND ? = "")), missä ? ja ? ovat etunimi ja sukunimi.

Jos taas haluat, että haku termeillä "Pekka" ja "2005–2008" antaisi tulokset "Pekka (1982)" ja "Maija (2006)", vaihda vain ANDin paikalle OR ennen vuosilukuehtoa.

Yksi kikka on vielä lisätä järjestysperusteeksi osuneiden ehtojen määrä:

ORDER BY
  IF(picture.year BETWEEN ? AND ?, 1, 0) +
  IF(pics_album.album = ?, 1, 0) +
  IF(blaa, 1, 0)

Tarkoitus olisi, että minkä tahansa hakukentän voi täyttää tai jättää täyttämättä ja haku tehdään täytettyjen ehtojen mukaan niin, että jokaisen ehdon täytyy olla tosi. Olenkin toteuttamassa tuota melko pitkälti niin, että rakennan koko kyselyn PHP:llä sen mukaan mitä kenttiä on täytetty ja mitä ei. Tämä mitä alunperin kysyin oli vaihtoehto, jossa kaikki kentät oli täytetty. Nyt PHP:llä rakennan tuon kyselyn oikeaan muotoon eli voin jättää noita JOINeja pois tai ottaa mukaan tarpeen mukaan.

Enköhän tällä saa tuon haun toimimaan. Kiitos paljon. :D

Mitenkäs sitten sellainen monipuolisempi (hankalampi) haku, jossa kenttiin voi syöttää monta henkilöä (esim. 3) ja haku edelleen tarkistaa, että jokainen annettu henkilö löytyy kuvasta? Tietäisin kyllä miten saan tarkistettua, että joku henkilöistä on kuvassa, mutta miten ehdoksi saa, että jokaisen pitää löytyä kyseisestä kuvasta?

Silloin joudut laittamaan kaikki henkilöön liittyvät LEFT JOINit moneen kertaan ja tauluille aliakset:

LEFT JOIN pics_people AS pics_people_1 ON pics_people_1.picture = picture.id
LEFT JOIN people AS people_1 ON people_1.id = pics_people_1.person AND people_1.name = "Pekka"

LEFT JOIN pics_people AS pics_people_2 ON pics_people_2.picture = picture.id
LEFT JOIN people AS people_2 ON people_2.id = pics_people_2.person AND people_2.name = "Maija"

WHERE people_1.id IS NOT NULL AND people_2.id IS NOT NULL

Tähän kuvahakuun tuli nyt hiukan jatkokysymystä.. Lisäsin tuonne pics_people tauluun sarakkeen public, jolla jokainen kuvassa esiintyvä henkilö voi määritellä kuvan julkiseksi tai ei julkiseksi (arvot 1 tai 0). Idea on siis se, että jos kaikki samassa kuvassa esiintyvät henkilöt ovat määritelleet kuvan julkiseksi niin kyseinen kuva näkyy kaikille. Jos yksikin on valinnut arvon ei julkinen, kuva näkyy vain ennalta määritellyille henkilöille.

Miten lisään siis tuohon edelliseen hakuun kohdan, jolla valitaan ainoastaan ne kuvat, joiden kaikki pics_people.public kohdat ovat arvossa 1 (eli kaikki kuvan henkilöt ovat antaneet luvan kuvan julkiselle esittämiselle)?

Omilla aivoillani olen kehitellyt ratkaisua, että pitää laskea montako henkilöä kuvassa esiintyy ja sen jälkeen laskea yhteen kaikki näiden henkilöiden pics_people.public arvot. Jos tulos ei ole sama, niin kuva ei ole julkinen. En vain saa sopivaa koodia aikaiseksi, jolla tuo tapahtuu. Vain onko järkevintä vain hakea kaikki hakuehtoihin sopivat kuvat ja tämän jälkeen vielä erikseen rajata pois ei julkiset kuvat toisella tietokantahaulla?

Hae kyselyyn mukaan rivit, joilla henkilö on kieltänyt kuvan julkaisun:

LEFT JOIN pics_people AS pics_people_kielto ON pics_people_kielto.picture = picture.id AND pics_people_kielto.public = 0

Tarkista WHERE-kohdassa, että yhtään tuollaista ei löytynyt:

WHERE pics_people_kielto.picture IS NULL

Noinhan se tietysti menee.. Näyttää yllättävänkin simppeliltä ratkaisulta, nyt kun koodin näkee. Omat aivoni eivät vain taipuneet tajuamaan, että kaikkein yksinkertaisinta on tarkistaa ettei yhtään 0 arvoa ole annettu, vaan koetin vain etsiä ratkaisua 1 arvojen määrän laskemiseksi.. Kiitos! :)