Több sor és oszlop egy cellaként MySQL és MariaDB-ben: JSON függvények és a GROUP_CONCAT() együtt

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
}