Schick programmiert

Die Idee von n-n relations und MySQLs GROUP_CONCAT

Ich bin einige Zeit mit dem Gedanken gegangen, wie man Daten effektiv laden kann, die in einer NN-Verknüpfung miteinander stehen.

Letztens bin ich dabei auf die Funktion GROUP_CONCAT() gestoßen: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

Nehmen wir ein Beispiel, wo wir ein System haben, in dem eine große Liste an Artikeln verwaltet wird in einer übersichtlichen Gruppe von Kategorien.

Vorher habe ich es so aufgebaut, dass ich bei jedem Zugriff auf die Artikel immer zwei Abfragen gesendet habe: eine um die Artikeldaten zu bekommen und eine um die zugehörigen Kategorien zu laden. Zusätzlich hatte ich eine Liste aller Kategorien im Speicher, da sie im weiteren Verlauf des Scripts immer wieder gebraucht wurden.

Warum nicht alle Kategorien am Anfang in den Speicher laden (vielleicht zusätzlich als Objekte serialisieren und in einem Speicher wie Memcache lagern) und mit nur einer Abfrage mehrere Artikel und deren Kategorien zu laden?

Ein SQL-Befehl zu dem gegebenen Beispiel würde ungefähr so aussehen:

Zuerst alle Kategorien laden:

SELECT * FROM category;

Dann kommt die Liste der Artikel mit Kategorie-Ids (als csv):

SELECT article.*, GROUP_CONCAT(article_has_category.category_id)
  FROM article LEFT JOIN article_has_category ON (article.id = article_has_category.article_id)
  GROUP BY article.id;

Der gesamte PHP-Code könnte dann so aussehen:

class Article {
    private $_categoryIds = array();

    public function __construct($row) {
        $this->_categoryIds = explode(",", $row['category_ids']);
    }

    public function getCategories() {
        $cat = array();
        foreach($this->_categoryIds as $id)
            $cat[] = CategoryRepository::getCategoryById($id);
        return $cat;
    }
}

class Category {}

class CategoryRepository {
    private static $objects;

    public static function load() {
        $res = mysql_query("SELECT * FROM category");
        while(($row = mysql_fetch_array($res))) {
            self::$objects[$row['id']] = new Category($row);
        }
    }

    public static function getCategoryById($id) {
        return self::$objects[$id];
    }
}

class ArticleRepository {
    public static function getArticles() {
        $objects = array();
        $res = mysql_query("SELECT article.*, GROUP_CONCAT(article_has_category.category_id) AS 'category_ids' FROM article LEFT JOIN article_has_category ON (article.id = article_has_category.article_id) GROUP BY article.id");
        while(($row = mysql_fetch_array($res))) {
            $objects[] = new Article($row);
        }
        return $objects;
    }
}

mysql_connect('localhost', 'root', 'root');
mysql_select_db('testdb');
CategoryRepository::load();
$articles = ArticleRepository::getArticles();

foreach($articles as $article)
    $article->getCategories();

Wer das gleich ausprobieren will, kann diese Testdaten verwenden:

CREATE TABLE IF NOT EXISTS `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

INSERT INTO `article` (`id`, `title`) VALUES
(1, 'Lorem Ipsum'), (2, 'Cooler Collins');

CREATE TABLE IF NOT EXISTS `article_has_category` (
  `article_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `article_has_category` (`article_id`, `category_id`) VALUES
(1, 1), (1, 3), (2, 3);

CREATE TABLE IF NOT EXISTS `category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=4 ;

INSERT INTO `category` (`id`, `title`) VALUES
(1, 'Gruppe1'), (3, 'Gruppe2');

CREATE TABLE IF NOT EXISTS `friendships` (
  `initiator_id` int(11) NOT NULL,
  `reciprocator_id` int(11) NOT NULL,
  UNIQUE KEY `initiator_id` (`initiator_id`,`reciprocator_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Diese Funktion hat nicht nur MySQL sondern auch PostgreSQL – nur hat sie da einen anderen Namen:
http://stackoverflow.com/questions/2560946/postgresql-group-concat-equivalent

Für MSSQL sieht es hier schwierig aus. Wenn sich die Situation noch nicht geändert hat muss ein Workarround geschrieben werden:
http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005


Categorised as: Uncategorized


Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.