Aufzählungen mit GROUP_CONCAT()

Für verschiedene Anwendungsfälle ist es übersichtlicher, wenn 1:n Beziehungen als Aufzählung angezeigt werden. Für dieses Beispiel verwende ich eine kleine, übersichtliche Tabelle mit Emailadressen und deren Aliase.

CREATE TABLE `mails` (
  `id` int(11) NOT NULL auto_increment,
  `parent_id` int(11) NOT NULL,
  `addy` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Jede Emailadresse hat eine Id und eine ParentId. Sind beide Ids identisch, dann ist es die Originaladresse, ansonsten ein Alias.

mysql> select * from mails;
+----+-----------+------------------+
| id | parent_id | addy             |
+----+-----------+------------------+
|  1 |         1 | m1@example.com   |
|  2 |         1 | a1m1@example.com |
|  3 |         3 | m2@example.com   |
|  4 |         1 | a2m1@example.com |
|  5 |         3 | a1m2@example.com |
+----+-----------+------------------+
5 rows in set (0.00 sec)

Mit einem selbstreferenzierenden Join werden die Daten nun wieder aus der Tabelle ausgelesen. Dabei wird die Tabelle ‘mails’ durch Aliasnamen wie zwei Tabellen behandelt. Wir selektieren die ‘addy’ aus der Tabelle ‘m1′ und die gejointen Aliase aus ‘m2′. Durch GROUP_CONCAT() werden die Werte nun kommasepariert in der Spalte ‘aliases’ ausgegeben.

mysql> SELECT m1.addy, GROUP_CONCAT(m2.addy SEPARATOR ', ') AS aliases
 ->   FROM mails m1
 ->   JOIN mails m2 ON m2.parent_id = m1.id
 ->  WHERE m2.id <> m2.parent_id
 ->  GROUP BY m1.id;
+----------------+--------------------------------------+
| addy           | aliases                              |
+----------------+--------------------------------------+
| m1@example.com | a1m1@example.com, a2m1@example.com   |
| m2@example.com | a1m2@example.com                     |
+----------------+--------------------------------------+
2 rows in set (0.00 sec)

Das funktioniert natürlich auch mit den Ids der Alias-Adressen. In diesem Fall muss dann aber der Rückgabewert von GROUP_CONCAT() gecastet werden, da sonst entweder nur die erste Id oder [BLOB] in der Ausgabe stehen würde.

mysql> SELECT m1.addy,
 ->           CAST(GROUP_CONCAT(m2.id SEPARATOR ', ') AS char) AS alias_ids
 ->   FROM mails m1
 ->   JOIN mails m2 ON m2.parent_id = m1.id
 ->  WHERE m2.id <> m2.parent_id
 ->  GROUP BY m1.id;
+----------------+-----------+
| addy           | alias_ids |
+----------------+-----------+
| m1@example.com | 2, 4      |
| m2@example.com | 5         |
+----------------+-----------+
2 rows in set (0.00 sec)

Die Funktion kann nicht nur gruppieren und mit beliebigen Trennzeichen aufzählen, es ist auch möglich, eine Sortierrichtung mit anzugeben. Mehr dazu im MySQL-Manual.

6 Kommentare

  1. Wenn man nun eine Tabelle hat wie “mails” in diesem Beispiel, und ‘id’ ist ein auto_increment Feld, wie kann ich am einfachsten sicherstellen, dass bei einer neuen Row bei einem INSERT identische ‘id’ und ‘parent_id’ Werte hat?

  2. Je nach dem wie die Daten eingetragen werden, entweder mit einem AFTER INSERT Trigger oder in einer Transaktion über das Model (PHP, Java etc.)

  3. Aber Vorsicht, falls der Join viele Ergebnisse aufweist.
    In der MySQL Konfiguration sollte man dann den Wert group_concat_max_len hoch genug ansetzen. Dieser gibt an, wie lange (wieviele Zeichen) das Ergebnis eines GROUP_CONCATs sein darf. Wird es länger, wird es einfach abgeschnitten, das kann durchaus auch mitten in einer ID oder einem String passieren.

    vg
    Michael

  4. AFTER INSERT trigger! Danke für den Tipp.

  5. Vorsicht! Group concat ist per default auf 1024 Limitiert, und schneidet ab da hart ab!

Schlagwörter: Adapter, Amazon, Animation, Annotations, Anonyme Klasse, Ant, Apache, API, Array, ArrayAccess, Attachment, AutoLoader, Bedienung, Bedingung, Benchmark, Bildbearbeitung, BOM, Bootstrap, Bot, Byte Order Mark, Callback, CamelCase, Canvas, Captcha, Cheatsheet, CLI, Closure, Cloud, CodeSniffer, Community, Comparator, Contest, Controller, Converter, CouchDB, Countable, Cronjob, CSV, CustomLibrary, Custom_Model, Data Mapper, Datei, Datenbank, Datenstruktur, Datentypen, Dating, Decorator, Dekorierer, Design Patterns, Dump, Duplikat, each, Eclipse, Entwicklung, Entwurfsmuster, Enum, Erweiterung, Eventhandling, Exception-Handling, Extension, Factory, Fehler, Flash, Foreach, Formatierung, Formular, Funktion, Futon, Header, HTML5, HTTP, IDE, If, Implementierung, InnoDB, Interceptor, Interface, isset, Iterator, Java, JavaScript, jQuery, Konfiguration, Konsole, Kontrollstruktur, kopieren, Late Static Binding, Layout, Linux, Listeners, Logging, Löschen, Magento, Magic Methods, Marketing, Methode, Model, MVC, MySQL, NetBeans, Objekt, Observable, Observer, OOP, Operator, Parameter, Partnersuche, Performance, PHP, phpMyAdmin, PHPUnit, Plugin, Proxy, Qualitätssicherung, Query, Reflection, Request, Response, Rest-API, Rockstar, Routing, S3, Samba, Scheifen, Schleife, Schutz, Secure Shell, Selbstreferenz, Shop, Sicherheit, Sicherung, Singleton Pattern, SOAP, Sortierung, Sourcecode, Spam, Speicherproblem, Spickzettel, SPL, SSH, Statement, Stellvertreter, Strategy Pattern, Stream, String, Sun VirtualBox, Support, Switch, Symfony, Symfony2, Symfony Live, Tag, Template, Template Method, Ternär Operator, Testing, Thumbnail, Tool, Tour, Twig, Type-Cast, Umwandlung, Underscore, unset, Vererbung, Verzweigung, Video, Videospiel, Virtualisierung, Visitor Pattern, Vorschaubild, walk, Webserver, Webservice, Weiterleitung, Wrapper, Youtube, Zeitsteuerung, Zend Framework, Zend_Cloud, Zend_CodeGenerator, Zend_Http_Client, Zend_Service, Zugriffsmethode