[mysql] Query naar zichzelf joinen

[Martine]

Op mijn recentelijk online geplaatste website loopt het aardig goed. Nu is mijn idee om er een zoekveld aan toe te voegen. Eerst een klein opzetje maken.

PHP:

$arr = explode(" ", addslashes(htmlspecialchars(trim($_POST['val']))));

for($q=0;$q<count($arr);$q++) {
  $where .= "title LIKE '%".$arr[$q]."%'";
  $where .= "OR fotoinfo LIKE '%".$arr[$q]."%'";
  $where .= "OR fotoinfoalg LIKE '%".$arr[$q]."%'";
            
  if ($q < (count($arr)-1))
    $where .= " OR ";
}

$sql = mysql_query("SELECT * FROM fotos WHERE ".$where." GROUP BY parentid");
//while... lees uit

Toevoegen
Bij het uploaden van de foto's, wordt het veld base van de eerste foto op 1 gezet, de rest blijft gewoon op 0 staan. De eerste foto krijgt ook de titel mee van alle foto's die geupload worden. De uploader kan onder iedere foto een regeltje tekst plaatsen.

Klein stukje databeest

code:

------------------------------------------------------------
| id | parentid |    title       |  fotoinfo        | base |
------------------------------------------------------------
| 51 |   51     | auto gespot    | rode auto        |  1   |
| 52 |   51     |                | auto beschadigd  |  0   |
| 53 |   51     |                | zonsondergang    |  0   |
| 54 |   54     | fietsers       | groepje fietsers |  1   |
| 55 |   54     |                | race fietsers    |  0   |

Probleem
Als er nu gezocht wordt op 'race' zal record met id 55 getoond worden, alleen kan de titel niet getoond worden omdat hier geen titel bij zit. Hoe krijg ik daar nu de titel van id 54 weergegeven?

Wie kan me even een fikse duw in de rug geven?

Voor de volledigheid;

code:

CREATE TABLE fotos (
  id int(11) NOT NULL auto_increment,
  userid int(11) NOT NULL default '0',
  filename varchar(100) NOT NULL default '',
  originalname varchar(200) NOT NULL default '',
  post_date datetime NOT NULL default '0000-00-00 00:00:00',
  title varchar(42) NOT NULL default '',
  fotoinfo varchar(150) NOT NULL default '',
  fotoinfoalg text NOT NULL,
  filesize int(11) NOT NULL default '0',
  bigimage int(11) NOT NULL default '0',
  active int(11) NOT NULL default '0',
  parentid varchar(50) NOT NULL default '0',
  base int(11) NOT NULL default '0',
  views int(11) NOT NULL default '0',
  UNIQUE KEY id (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

[Gomez12]

Gewoon met een inner join het parentid erbij joinen, dan krijg je een breder record wat wel een gevulde title bevat.

Of het gewoon goed oplossen en even wat nazoeken over normalisatie. Hint : zodat je title in table 1 gooit, fotoinfo in tabel 2 en een 3e koppeltabel erbij zodat je weet wat van tabel 1 bij tabel 2 hoort.

btw, 3 likes vind je dbase beestje waarschijnlijk niet leuk als je meer records krijgt. Ik weet niet wat je verwachting is qua aantal foto's, maar jij hebt het over aardig goed. Dus mijn simpele stelregel : Als je meer als 10.000 records hebt geen like meer gebruiken behalve als het echt niet anders kan...

[ Voor 32% gewijzigd door Gomez12 op 16-11-2007 00:17 ]

[Martine]

Gomez12 schreef op vrijdag 16 november 2007 @ 00:12:
btw, 3 likes vind je dbase beestje waarschijnlijk niet leuk als je meer records krijgt. Ik weet niet wat je verwachting is qua aantal foto's, maar jij hebt het over aardig goed. Dus mijn simpele stelregel : Als je meer als 10.000 records hebt geen like meer gebruiken behalve als het echt niet anders kan...

Bedankt voor je snelle reactie. Hoe moet ik het dan oplossen? Want ik kan nu nog wat aanpassen, als over een tijdje de website geheel wordt uitgebouwd minder snel.

// edit;
Alleen op de onderstaande manier blijft het gehele record weg, ook als ik de group by niet gebruik.

SQL:

SELECT a.*, b.title AS titel2 FROM fotos a INNER JOIN fotos b ON
a.id = b.parentid WHERE ".$where." GROUP BY a.parentid

[ Voor 17% gewijzigd door Martine op 16-11-2007 00:41 ]

[curry684]

Full-text search is op grote lappen tekst (exponentieel) efficienter dan LIKE. Ik vraag me sterk af of het in dit geval boeit though, ik betwijfel of een kleine fotosite snel over de 100k foto's gaat en/of meer dan 1 zoekquery per 10 seconden gaat verwerken, en dan is het verschil niet bepaald boeiend.

[Martine]

Het zijn totaal niet grote lappen tekst, meestal een regeltje van 200, 300 soms 400 tekens. En met foto's valt het ook wel wat mee, nu bijna vier weken online en zit rond de 300 foto's.

[curry684]

Hmmm bij 200+ tekens per tekstregel die je doorzoekt zou ik dan wel weer even naar full-text search kijken, ik ging uit van 10~40. LIKE met leading wildcard wordt per definitie lineair trager naarmate je dataset en de gemiddelde lengte van de content toenemen (dus exponentieel bij beide!)

En met MySQL is het feitelijk een moeite van niets om aan te brengen, en je krijgt relevantere search results.

[ Voor 16% gewijzigd door curry684 op 16-11-2007 02:59 ]

[Martine]

Die wildcard heb ik er alleen in de test versie even staan, in een later stadium - als hij online gaat - worden alleen de velden id, title getoond.

[curry684]

Ik bedoelde de procentjes in de LIKE

SQL:

-- Zonder leading wildcard - gebruikt index waar mogelijk
WHERE PizzaName LIKE 'Marg%'

-- Met leading wildcard, per definitie pijnlijke table scan
WHERE PizzaName LIKE '%arg%'

[Verwijderd]

curry684 schreef op vrijdag 16 november 2007 @ 03:36:

SQL:

-- Met leading wildcard, per definitie pijnlijke table scan
WHERE PizzaName LIKE '%arg%'

Ik weet niet hoe je dit bedoeld dus misschien begrijp ik de context niet, maar met een index over de kolom wordt het zeker niet per definitie een full table scan.

[Voutloos]

Geen definitie, maar 'grote kans op'. En als je zo in meerdere kolommen zoekt is het misschien nog wel een van de snelste manieren om de query uit te voeren.

Punt is gewoon dat je goed moet nadenken of je een leading wildcard wil hebben.

Martine schreef op vrijdag 16 november 2007 @ 00:20:
// edit;
Alleen op de onderstaande manier blijft het gehele record weg, ook als ik de group by niet gebruik.

SQL:

SELECT a.*, b.title AS titel2 FROM fotos a INNER JOIN fotos b ON
a.id = b.parentid WHERE ".$where." GROUP BY a.parentid

Gebruik je nu in de where clause ook tabelnamen op? Zo niet, had je dat al kunnen spotten door naar de mysql error te kijken. Is er sowieso een error of een lege resultset?

Denk verder na over wat er bij die group by moet gebeuren: je groepeert de 2 records met parentid 54, en dan? Van welke rij moet de db dan de title pakken?
Antwoord: een willekeurige rij, en dat is een mysql-only 'feature'. Andere db's weigeren random data terug te geven en geven een error. HIer is veel over geschreven en mocht het hoe en waarom van dit gedrag niet duidelijk zijn, raad ik je aan wat over group by en aggregate functions te lezen.

[Martine]

Voutloos schreef op vrijdag 16 november 2007 @ 08:40:
Gebruik je nu in de where clause ook tabelnamen op? Zo niet, had je dat al kunnen spotten door naar de mysql error te kijken. Is er sowieso een error of een lege resultset?

Sjips, die had ik vergeten, zo is het nu geworden en werkt prima!

PHP:

// for()
  $where .= "a.title LIKE '%".$arr[$q]."%'";
  $where .= " OR a.fotoinfo LIKE '%".$arr[$q]."%'";
  $where .= " OR a.fotoinfoalg LIKE '%".$arr[$q]."%'";
  $where .= " OR b.title LIKE '%".$arr[$q]."%'";
  $where .= " OR b.fotoinfo LIKE '%".$arr[$q]."%'";
  $where .= " OR b.fotoinfoalg LIKE '%".$arr[$q]."%'";

SQL:

SELECT a.* FROM fotos a INNER JOIN fotos b ON a.id = b.parentid
WHERE ".$where." GROUP BY a.parentid

Fijn dat het werkt, maar ik wil echter niet een website hebben op de manier van 'Ach, zo kan het ook wel, maar het kan beter...'. Nee, ik wil een website hebben die 100% in orde is, is het daarom verstandig om de rambam om te bouwen op met onderstaande manier zoals Gomez12 vertelde?

Gomez12 schreef op vrijdag 16 november 2007 @ 00:12:
Of het gewoon goed oplossen en even wat nazoeken over normalisatie. Hint : zodat je title in table 1 gooit, fotoinfo in tabel 2 en een 3e koppeltabel erbij zodat je weet wat van tabel 1 bij tabel 2 hoort.

[Jaap-Jan]

Martine schreef op vrijdag 16 november 2007 @ 12:15:
(...)

Fijn dat het werkt, maar ik wil echter niet een website hebben op de manier van 'Ach, zo kan het ook wel, maar het kan beter...'. Nee, ik wil een website hebben die 100% in orde is, is het daarom verstandig om de rambam om te bouwen op met onderstaande manier zoals Gomez12 vertelde?

In dat kader zou ik zeker nog eens kijken naar de opmerking van gorgi_19 in je andere topic: gorgi_19 in "[mysql] Select in query doen?". Oftewel: beschouw een foto niet als child van een andere foto, maar als onderdeel van een album.

Ik moet eerlijk zeggen dat ik niet helemaal begrijp waarom Gomez12 de fotoinfo in een aparte tabel wil zetten, de fotoinfo is immers direct gerelateerd aan de foto. Maar misschien dat ik iets mis

[Dido]

Jaap-Jan schreef op vrijdag 16 november 2007 @ 12:54:
Ik moet eerlijk zeggen dat ik niet helemaal begrijp waarom Gomez12 de fotoinfo in een aparte tabel wil zetten, de fotoinfo is immers direct gerelateerd aan de foto. Maar misschien dat ik iets mis

Hij stelt voor om een n:m relatie tussen album en foto te maken. Dus albuminfo in tabel 1, fotoinfo in tabel 2, en dan een derde tabel om de foto's in albums te gooien. Dan kan een foto in meerdere albums zitten, en dat zou best wel handig kunnen zijn, misschien

[Jaap-Jan]

Dido schreef op vrijdag 16 november 2007 @ 13:26:
[...]

Hij stelt voor om een n:m relatie tussen album en foto te maken. Dus albuminfo in tabel 1, fotoinfo in tabel 2, en dan een derde tabel om de foto's in albums te gooien. Dan kan een foto in meerdere albums zitten, en dat zou best wel handig kunnen zijn, misschien

Oeps, ik zie nu pas dat titel de titel voor het album is , hier is dus echt nog werk aan de winkel, TS .

[Martine]

Het zal nooit voorkomen dat er dezelfde foto's in meerdere albums komen.

Zoals je hieronder kunt zien zijn het niet echt albums, het is meer een weblog idee, zo wordt iedere pagina opgebouwd.

code:

---------
|       |  <-- eerste foto, titel en fotoinfo
|       |      staan in dit record
---------

---------
|       |  <-- tweede foto, heeft parentid van
|       |      de eerste foto
---------

---------
|       |  <-- derde foto, heeft parentid van
|       |      de eerste foto
---------

[Voutloos]

Denk nou eens in entiteiten. Je hebt gewoon een blogpost en foto's, waarvan er toevallig eentje de eerste is. Kloar is je duidelijke datamodel.

[Jaap-Jan]

Dan noem je het geen album, maar fotogroep ofzo. Het maakt het principe niet anders.

Je hebt op het moment een raar databasemodel, wat je eigen moet opsplitsen in een paar tabellen. Als je zegt dat dezelfde foto's niet in meerdere fotogroepen kunnen voorkomen, dan heb je een 1:N (één-op-veel) relatie. Je moet dan van een foto aangeven bij welke groep hij hoort. Een iets verder uitgewerkt voorbeeldje hieronder:

code:

'          ----------------------------------------------------------------------------------------------------------------
foto:      | foto_id | groep_id | title | filename | originalname | post_date | fotoinfo | fotoinfoalg | bigimage | views |
           ----------------------------------------------------------------------------------------------------------------

           -----------------------------------------------------------
fotogroep: | groep_id | user_id | title | thumbnail_foto_id | active |
           -----------------------------------------------------------

Waar heb je trouwens filesize voor nodig? Ik heb 'm weggelaten, want er zijn andere manieren om aan die gegevens te komen.

In je tabel zie ik dit:

code:

-------------------------
|    title       | base |
-------------------------
| auto gespot    |  1   |
|                |  0   |
|                |  0   |
| fietsers       |  1   |
|                |  0   |

Bij alle kolommen waar je hetzelfde hebt als bij title (alleen ingevuld als base op 1 staat) is die kolom eigenlijk afhankelijk van fotogroep, maar niet van foto. Derhalve moet je ze ook verplaatsen naar die fotogroep. Als je dat hebt gedaan, heb je een datamodel wat redelijk genormaliseerd is.

edit:
Het hierboven gepostte strokendiagram is een idee, geen complete implementatie

[Martine]

Jaap-Jan schreef op vrijdag 16 november 2007 @ 14:58:
Waar heb je trouwens filesize voor nodig? Ik heb 'm weggelaten, want er zijn andere manieren om aan die gegevens te komen.

Het leek mij wel leuk om op een gegeven moment precies te weten hoeveel ruimte de foto's innemen, verder wordt er niets mee gedaan.

Ik zal de alles begin volgende week eens even ombouwen, in ieder geval bedankt voor het meedenken!

[curry684]

Verwijderd schreef op vrijdag 16 november 2007 @ 07:16:
[...]
Ik weet niet hoe je dit bedoeld dus misschien begrijp ik de context niet, maar met een index over de kolom wordt het zeker niet per definitie een full table scan.

Kun jij me even uitleggen hoe je DBMS anders in godesnaam kan weten dat Zarg, 684arg en Aarg en een tekst van 131Mb waarin achterin ergens "arg" allemaal matchen, zonder iedere row van de table erop na te zoeken, oftewel een table scan?

Je hebt alleen geluk als het veld toevallig klein genoeg is om als geheel in de index opgenomen te worden, dan kun je er met een index scan vanaf komen (nog steeds kut in vergelijk met een index seek, waar full-text search in beginsel intern uit bestaat). Maar gezien dat het over teksten van een paar honderd karakters in de (fotoinfoalg text NOT NULL) kolom gaat is dat niet het geval.