A fejlesztők 2 fajtája nem foglalkozik az adatbázis lekérdezések sebességével több kapcsolódó adatnál: a kezdők és a profik.
A kezdők lekérdezik az alap adatokat, majd egy foreach()-en belül futatják le a kapcsolódó adatok lekérdezését:
foreach ( $products as $product ) {
$db->query( "SELECT id, thumbnail, url FROM product_images WHERE product_id = :product_id" );
}
A profik is ez csinálják, csak sokkal csillibb kivitelben. Először csinálnak egy Image class-t. Majd egy getAllByProduct( int $product_id ) metódust hozzá. A termék lekérdezésénél pedig vagy a Product konstruktorába teszik – amit a FETCH_CLASS -al használnak ki -, vagy a Product::get( int $product_id ) -be, egy ciklusban. Ami megint ugyanaz csak pepitában.
Persze mindkettő lassú és minél több a kapcsolódó adat, annál durvábban gyilkoljuk a szervert. Mindkét változat nagyon sok profi termékben is megfigyelhető, nem véletlenül vannak olyan horror gépigényeik, annak ellenére, hogy valójában ezredmásodpercek alatt elérhető lenne a teljes Product objektum. Ja igen, akkor csúnya lenne a kód, mert nem lenne tele rengeteg újrafelhasználható objektummal és metódussal.
A kezdők persze azzal védekeznek, hogy „sz@r a PHP”, a profik meg azzal, hogy „ha 100 termékes webshopról van szó, napi 1000 látogatóval, akkor 32GB RAM, 16vCPU alapnak….”. Mindkettő hülyeség.
Most mégis azt javaslom, hogy próbáld ki a JSON függvényeket és a GROUP_CONCAT-et együtt. Ezekhez jellemzően a MySQL-ből és a MariaDB-ből is a legújabb változat kell, valamint a JSON oszloptípust csak a MySQL kezeli, a MariaDB esetén ezt valamilyen karakteres típusban kell tárolni (pl MEDIUMTEXT).
Az alábbiakban egy MariaDB kompatibilis módját mutatom meg, egy termék lekérésének, amihez címkék is kapcsolódnak, a végeredmény egy terméklista lesz, ahol a címkék egy JSON tömbként lesznek meg a cellában, amit a PHP-vel json_decode()-al már szét is tudunk bontani.
SELECT
p.id,
p.title,
p.desc,
COALESCE(
CONCAT("[",JSON_UNQUOTE(
(
SELECT ( GROUP_CONCAT(
JSON_OBJECT(
"id", t.id,
"slug", t.slug,
"tag", t.tag
))
) FROM rel_product_tag r_pt LEFT JOIN tags t ON t.id = r_pt.tag_id WHERE r_pt.product_id = p.id)
),"]"), JSON_ARRAY()
) AS tags
FROM products
De lássuk ezt darabonként:
JSON_OBJECT():
Ennek segítségével egy JSON objektumot kapunk válaszként. A páratlan paraméter mindig az objektum elem kulcsa, a páros pedig az értéke pl.:
SELECT JSON_OBJECT( "id", product.id, "title", product.title ) FROM products WHERE product.id = 1
Ez ilyen választ fog adni:
{ "id" : 1, "title" : "Termék" }
GROUP_CONCAT():
Ezzel pedig a több soros találatból készítünk egy vesszővel elválasztott listát. Figyeljünk arra hogy a GROUP_CONCAT alapértelmezett hossza kicsi, ezért meg kell változtatni a kérés előtt!
SELECT GROUP_CONCAT(JSON_OBJECT( "id", product.id, "title", product.title )) AS results FROM products
Ennek az eredménye pedig ilyen lesz:
{ "id" : 1, "title" : "Termék 1" },{ "id" : 2, "title" : "Termék 2" },{ "id" : 3, "title" : "Termék 3" }
Ez már majdnem JSON tömb, de ahhoz még kell elé és mögé is egy [ és egy ].
SELECT CONCAT("[", GROUP_CONCAT(JSON_OBJECT( "id", product.id, "title", product.title )),"]") AS results FROM products
Viszont ha nincs eredmény, akkor probléma van, mert nem JSON tömb lesz a válasz, hanem NULL, ezért:
SELECT COALESCE(CONCAT("[", GROUP_CONCAT(JSON_OBJECT( "id", product.id, "title", product.title )),"]"), JSON_ARRAY()) AS results FROM products
Amikor ezt subqueryként használjuk – amiből az alapvető probléma is indult – akkor figyelnünk kell arra, hogy a CONCAT escapeli az időzőjeleket a subqueryben, amik a JSON objektumon belül vannak, ezért kell a JSON_UNQUOTE() trükk. A COALESCE(subquery_result,JSON_ARRAY()) pedig azért kell, hogy ha a subquery NULL eredménnyel tér vissza, akkor is szabványos (üres) JSON tömböt kapjunk vissza.
Természetesen a JSON függvények arra is felhasználhatók, hogy NoSQL szerűen JSON-ként kapjuk vissza az egyszerűbb lekérések adatait is. Köszönhetően ennek a trükknek egy viszonylag gyors queryben lekérdezhetjük például a termékeket, a hozzá kapcsolódó képeket, címkéket, hozzászólásokat, …
MySQL esetén picivel egyszerűbb a JSON tömbként való használat, ugyanis ott lehet CAST-olni JSON típusra. MariaDB esetén pedig találkoztam olyan érdekes jelenséggel, hogy ha nem tárolt eljárásban van használva a JSON_OBJECT vagy a JSON_ARRAY akkor problémái vannak az ékezetes betűkkel, bármilyen collation és charset beállításokkal is játszottam.
Az alábbi JSON egy API kéréshez készült, egyetlen SQL query-ből kapjuk az eredményt, 10 táblából – tárolt eljárásként – eleve JSON formátumban, stringként, MySQL-ben. Az url-eket módosítottam kamu címekre, titokban tartva ezzel a projektet.
{
"ean": null,
"mpn": null,
"web": {
"de": {
"url": "http://legyes.hu/de",
"shown": 1,
"description": "Beschriebung2"
},
"en": {
"url": "http://legyes.hu/en",
"shown": 1,
"description": "Description2"
},
"hu": {
"url": "http://legyes.hu/hu",
"shown": 1,
"description": "Leírás2"
}
},
"name": {
"de": Rozé Pezsgő",
"en": "Rozé Pezsgő",
"hu": "Rozé Pezsgő"
},
"tags": {
"de": [
"günstig",
"süß"
],
"en": [
"sale",
"sweet"
],
"hu": [
"akciós",
"édes"
]
},
"images": [
{
"url": "http://legyes.hu/uploads/roz%C3%A9.jpg",
"title": "Rozé Pezsgő",
"width": 1920,
"height": 501,
"watermark_url": null
},
{
"url": "http://legyes.hu/uploads/roz%C3%A9.jpg",
"title": "Rozé Pezsgő",
"width": 600,
"height": 200,
"watermark_url": null
}
],
"vintage": "2014",
"producer": {
"name": "Valami Pince",
"country": "Valahol",
"youtubeUrl": null,
"wine_country": "Magyarország",
"winemakerName": null
},
"guarantee": null,
"signature": {
"de": [
],
"en": [
],
"hu": [
]
},
"thumbnail": {
"normal": "http://legyes.hu/uploads/roz%C3%A9.jpg",
"thumbnail": "http://legyes.hu/uploads/roz%C3%A9_kicsi.jpg"
},
"dimensions": {
"depth": null,
"width": null,
"bottle": null,
"height": null,
"weight": null
},
"composition": {
"de": [
{
"Furmint": 30
},
{
"Kékfrankos": 70
}
],
"en": [
{
"Furmint": 30
},
{
"Kékfrankos": 70
}
],
"hu": [
{
"Furmint": 30
},
{
"Kékfrankos": 70
}
]
},
"description": {
"de": "Beschriebung",
"en": "description",
"hu": "Leírás"
},
"external_id": 403,
"feedback_url": null,
"product_updated_at": "2017-08-06 22:50:11",
"category_external_id": 1
}