-
Notifications
You must be signed in to change notification settings - Fork 33
Doku Entwickler Database QueryBuilder
Der QueryBuilder sollte direkt vom Mysql erstellt werden (also $this->db() in einem Mapper z.B.). Es gibt 4 verschiedene QueryBuilder für die verschiedenen Arten von Queries:
- Select
- Insert
- Update
- Delete
Es wurde versucht die QueryBuilder zum einen intuitiv benutzbar, zum anderen aber auch einfach benutzbar zu machen, daher können Queries auf unterschiedliche Art und Weise erzeugt werden.
Bei Folgenden Codebeispiel wird davon ausgegangen, dass man sich in einem Mapper befindet und $this->db() ein \Ilch\Database\Mysql Objekt ist. Für jeden QueryBuilder gibt es eine Kurzform und auch die Möglichkeit der Langform, die Methoden where (samt orWhere und andWhere) und execute sind bei jedem QueryBuilder verfügbar, auch wenn where bei Insert ignoriert werden kann.
Die in den Beispielen verwendeteten Tabellen müssen nicht zur Verfügung stehen und sind lediglich zur Anschauung.
Zur Vereinfachung werden die Klassennamen auf dieser Seite abgekürzt und einfach von Delete, Insert, Select, Update und Mysql gesprochen, auch wenn diese in Ilch\Database[\Mysql] Namespace sind.
Methoden der Mysql-Klasse zum Erzeugen der QueryBuilder
- insert(string $into = null, array $values = null): Insert
- delete(string $from = null, array $where = null): Delete
- update(string $table = null, array $values = null, array $where = null): Update
- select(string|array $fields = null, string|array $table = null, array $where = null, array $orderBy = null, int|array $limit = null): Select
Fangen wird mit einfachsten an, dem Einfügen von neuen Daten mittels dem Insert Query Builder. Dieser bringt nur 2 Methoden eigene Methoden mit (execute ist Teil jedes QueryBuilders):
- into(string $table): Insert
- values(array $values): Insert
- execute(): int der erstelle Primary Key
Codebeispiel:
//Kurzform
$userId = $this->db()->insert('User', ['name' => 'Hans', 'email' => '[email protected]'])->execute();
//Lange Form
$insert = $this->db()->insert();
$userId = $insert->into('User')
->values(['name' => 'Hans', 'email' => '[email protected]'])
->execute();
Diese Beispiele entsprechen der Query
INSERT INTO `[prefix]_User` (`name`, `email`) VALUES ("Hans","[email protected]");
Es ist ebenfalls möglich mehrere Zeilen mit einem Insert hinzuzufügen. Es können maximal 1000 Zeilen gleichzeitig hinzugefügt werden.
$this->db()->insert('User')
->columns(['name', 'email'])
->values([['Hans', '[email protected]'], ['Peter', '[email protected]'], ['Bob', '[email protected]']])
->execute();
- columns(array $columns): Insert
- values(array $arrayOfValues): Insert
Kommen wir zum nächsten, dem Löschen. Beim Löschen kann optional und meist will man nicht alle Daten einer Tabelle löschen! eine Bedingnung angeben werden, dafür gibt es dann die where Methode.
Methoden:
- from(string $table): Delete
- where(array $where): Delete
- execute(): int Anzahl gelöschter Zeilen
Codebeispiel:
//Kurzform
$deleted = $this->db()->delete('User', ['name' => 'Hans'])->execute();
//Lange Form
$delete = $this->db()->delete();
$deleted = $delete ->from('User')
->where(['name' => 'Hans')
->execute();
Diese Beispiele entsprechen der Query
DELETE FROM `[prefix]_User` WHERE `name` = "Hans";
Ohne lange Vorrede nun zum Update, dies hat nun die Möglichkeit Daten und Bedingungen anzugeben.
Methoden:
- table(string $table): Update
- values(array $values): Update
- where(array $where): Update
- execute(): int Anzahl geänderter Zeilen
Codebeispiel:
//Kurzform
$updated= $this->db()->update('User', ['name' => 'Hans Wurst'], ['name' => 'Hans'])->execute();
//Lange Form
$update = $this->db()->update();
$updated = $update->table('User')
->values(['name' => 'Hans Wurst'])
->where(['name' => 'Hans'])
->execute();
Diese Beispiele entsprechen der Query
UPDATE `[prefix]_User` SET `name` = "Hans Wurst" WHERE `name` = "Hans";
Kommen wir nun zum komplexesten QueryBuilder, dem Select. Es ist nun auch möglich Queries mit Joins und Group By zu erzeugen. Als Ergebnis beim Ausführen der Query wird ein Result Objekt zurück geliefert, welches in diesem Kapitel auch etwas näher beleuchtet wird.
Methoden:
- from(string $table): Select
- fields(string|array $fields, bool $replace = true): Select
- where(array $where, string $type = 'and'): Select
- andWhere(array $where): Select
- orWhere(array $where): Select
- limit(int|array $limit): Select
- offset(int $offset): Select
- page(int $offset): Select
- join(string|array $table, array $conditions, string $type = Join::INNER, array $fields = null): Select
- createJoin(string|array $table, string $type = Join::INNER): Join
- addJoin(Join $join): Select
- group(array $fields, bool $replace = true): Select
- useFoundRows(bool $useFoundRows = true): Select
- execute(): Result Ergebnisobjekt
Ein erstes einfaches Beispiel:
//Kurzform
$result = $this->db()->select(['name', 'email'], 'User', ['id' => 5])
->execute();
//Lange Form
$select = $this->db()->select();
$result = $select->fields(['name', 'email'])
->from('User')
->where(['id' => 5])
->execute();
Diese Beispiele entsprechen der Query
SELECT `name`,`email` FROM `[prefix]_User` WHERE `id` = "5";
Da man damit ja noch nicht so viel anfangen kann, kommen wir nun direkt zum Result Objekt.
- getMysqliResult(): mysqli_result
- fetchCell(int|string $name = null): string|false|null Gibt den Inhalt der Zelle zurück
- fetchArray(int $type: Result::BOTH): array|null Gibt ein der nächsten Array zurück (je nach $type, indiziert, assoziativ oder beides)
- fetchRow(): array|null Gibt ein indiziertes Array der nächsten Zeile zurück (numerische Keys)
- fetchAssoc(): array|null Gibt ein assoziatives Array der nächsten Zeile zurück (Spaltennamen als Keys)
- *fetchObject(string $className = null): object|stdClass|null Gibt object der nächsten Zeile zurück
- fetchRows(): array[] Gibt alle Zeilen des Ergebnis als assoziative Arrays in einem Array zurück
- fetchList(string $keyField = null, string $field = null): string[] Gibt eine Array mit den Werten einer Spalte aller Zeilen zurück
- getNumRows(): int Anzahl der Zeilen in dem Ergebnis
- getFieldCount: int Anzahl der Spalten in dem Ergebnis
- *setCurrentRow(int $position): bool Setzt die interne Position der aktuellen Zeile der Ergebnismenge
- getFoundRows: int Anzahl der aller Zeilen, die auf die Bedingung treffen, wenn Select::limit() in Verbindung mit Select::useFoundRows() verwendet wird
Am Beispiel der vorherigen Select Query nun einige Beispiele, weiter folgen mit später bei weiteren Select Beispielen. Nehmen wir an, die Zeile hat die Werte:
- name -> 'Hans'
- email -> '[email protected]'
Jede Zeile sollte nur für sich alleine stehen können, da ein weiterer Aufruf einer Funktion zur nächsten Zeile des Ergebnis springt, am Ende der Ergebnismenge wird null zurückgegeben.
//fetchCell
$name = $result->fetchCell(); // -> 'Hans'
$name = $result->fetchCell(0); // -> 'Hans'
$name = $result->fetchCell('name'); // -> 'Hans'
$email = $result->fetchCell(1); // -> '[email protected]'
$email = $result->fetchCell('email'); // -> '[email protected]'
$invalid = $result->fetchCell('gibtsnicht'); // -> false
//fetchArray
$array = $result->fetchArray(); // -> [0 => 'Hans', 1 => '[email protected]', 'name' => 'Hans', 'email' => '[email protected]']
//fetchRow
$array = $result->fetchRow(); // -> [0 => 'Hans', 1 => '[email protected]']
//fetchAssoc
$array = $result->fetchAssoc(); // -> ['name' => 'Hans', 'email' => '[email protected]']
//fetchObject
$array = $result->fetchObject(); // -> stdClass('name' => 'Hans', 'email' => '[email protected]')
//fetchRows
$array = $result->fetchRows(); // -> [0 => ['name' => 'Hans', 'email' => '[email protected]']]
$array = $result->fetchRows('name'); // -> ['Hans' => ['name' => 'Hans', 'email' => '[email protected]']]
//fetchList
$list = $result->fetchList(); // -> [0 => 'Hans']
$list = $result->fetchList('name'); // -> [0 => 'Hans']
$list = $result->fetchList('email'); // -> [0 => '[email protected]']
$list = $result->fetchList('name', 'email'); // -> ['Hans' => '[email protected]']
//getNumRows
$count = $result->getNumRows(); // -> 1
//getFieldCount
$fields = $result->getFieldCount(); // -> 2
Kommen wir zurück zum Select Query Builder, und kommen wir zu etwas komplzierteren Queries.
Wenn man nicht nur Felder sondern auch eine MySQL Funktion wie z.B. COUNT oder SUM verwenden möchte, so ist dies möglich. Sofern in einem Feld ein ( gefunden wird, wird angenommen, dass es eine Funktion ist und keine Quotes gesetzt.
//Kurzform
$result = $this->db()->select('COUNT(*)', 'User')
->execute();
//Lange Form
$select = $this->db()->select();
$result = $select->fields('COUNT(*)')
->from('User')
->execute();
Diese Beispiele entsprechen der Query
SELECT COUNT(*) FROM `[prefix]_User`;
Es gibt Aliase für Tabellen und Felder, dabei ist die Verwendung die gleiche, als Arraykey kann man den Alias angeben. Es ist bei Feldnamen möglich, wie bei MySQL auf den Alias (oder auch den Tabellenname) mit . zu selektieren, was so richtig sinnvoll allerdings erst mit Joins wird.
//Kurzform
$result = $this->db()->select(['userId' => 'u.id', 'userName' => 'u.name'], ['u' => 'User'])
->execute();
//Lange Form
$select = $this->db()->select();
$result = $select->fields(['userId' => 'u.id', 'userName' => 'u.name'])
->from(['u' => 'User'])
->execute();
Diese Beispiele entsprechen der Query
SELECT `u`.`id` AS `userId`,`u`.`name` AS `userName` FROM `[prefix]_User` AS `u`;
Natürlich soll es auch mit dem QueryBuilder möglich sein, komplizierte Bedinungen zusammenzubauen. Diese sind im übrigen bei allen QueryBuilder verfügbar. Es ist möglich bei Operatoren im Key die Bedingung mit anzugeben. Mehrere Bedingungen werden mit und-verknüpft.
//Kurzform
$result = $this->db()->select(null, ['u' => 'User'], ['u.id >' => 5, 'u.id <=' => 10]])
->execute();
//Lange Form
$select = $this->db()->select();
$result = $select->from(['u' => 'User'])
->where(['u.id >' => 5, 'u.id <=' => 10])
->execute();
Diese Beispiele entsprechen der Query
SELECT * FROM `[prefix]_User` AS `u` WHERE (`u`.`id` > "5" AND `u`.`id` <= "10");
Gültige Vergleichsoperatoren
Operator | Bedeutung |
---|---|
= | gleich |
<= | kleiner oder gleich |
>= | größer oder gleich |
< | kleiner |
> | größer |
!= | ungleich |
<> | ungleich |
LIKE | Zeichenkettenvergleich |
Natürlich ist es auch möglich Bedingungen per oder zu verknüpfen. Es gibt viele verschiedene Wege dies zu bewerkstelligen.
//QB initialisieren
$select = $this->db()->select(null, ['u' => 'User']);
//1. Variante - setzte Bedingungen neu (alte Bedingungen werden überschrieben)
$select->where(['u.id >' => 5, 'u.id <=' => -10], 'or');
//2. Variante - setzt angegebene Bedingungen als Oder zu den schon gesetzten Bedingunen (in diesem Fall keine)
$select->orWhere(['u.id >' => 5, 'u.id <=' => -10]);
//3. Variante - setzte Bedingungen neu (alte Bedingungen werden überschrieben)
$select->where([$select->orX(['u.id >' => 5, 'u.id <=' => -10])]);
//Ergebnis erzeugen
$result = $select->execute();
Diese Beispiele entsprechen der Query
SELECT * FROM `[prefix]_User` AS `u` WHERE (`u`.`id` > "5" OR `u`.`id` <= "-10");
Weiter können die Bedingungen beliebig verschachtelt werden, dies kann noch viel weiter als in dem Beispiel getrieben werden.
//QB initialisieren
$select = $this->db()->select(null, ['u' => 'User']);
//1. Variante - nachträgliches Hinzufügen von weiteren Bedingungen !Achtung:! Reihenfolge der Aufrufe beachten
$select->where(['u.id >' => 5, 'u.id <=' => -10], 'or');
$select->andWhere(['u.age >' => 20]);
//2. Variante - komplette zusammenbauen und setzen
$select->where([$select->orX(['u.id >' => 5, 'u.id <=' => -10]), 'u.age >' => 20]);
//Ergebnis erzeugen
$result = $select->execute();
Diese Beispiele entsprechen der Query
SELECT * FROM `[prefix]_User` AS `u` WHERE ((`u`.`id` > "5" OR `u`.`id` <= "-10") AND `u`.`age` > "20");
//Kurzform (direkt bei Aufruf von select())
$select = $this->db()->select(null, 'User', null, ['name' => 'DESC']);
//Lange Variante
$select = $this->db()->select()
->from('User')
->order(['name' => 'DESC']);
Diese Beispiele entsprechen der Query
SELECT * FROM `[prefix]_User` ORDER BY `name` DESC;
Natürlich können mehrere Sortierungen angeben werden.
Kommen wir wieder zur Auswahl von Bereichen mit Limit und co.
//Kurzform (direkt bei Aufruf von select())
$select = $this->db()->select(null, 'User', null, null, 5);
//Lange Variante
$select = $this->db()->select()
->from('User')
->limit(5);
Diese Beispiele entsprechen der Query
SELECT * FROM `[prefix]_User` LIMIT 5;
Natürlich kann wir zur Auswahl eines Bereiches, dafür gibt es mehrere Möglichkeiten.
//Kurzform (direkt bei Aufruf von select())
$select = $this->db()->select(null, 'User', null, null, [20, 10]);
//Lange Variante - nur limit (Offset von 20 und Limit von 10)
$select = $this->db()->select()
->from('User')
->limit([20, 10]);
//Lange Variante - mit limit und offset
$select = $this->db()->select()
->from('User')
->limit(10)
->offset(20);
//Lange Variante - mit limit und page
$select = $this->db()->select()
->from('User')
->limit(10)
->page(3);
Diese Beispiele entsprechen der Query
SELECT * FROM `[prefix]_User` LIMIT 20, 10;
Die Gruppierung ist nur als lange Variante verfügbar, wobei der QueryBuilder natürlich auch über select() vorkonfigiert werden kann. Man kann die Felder wie bei fields() angeben.
//Lange Variante
$select = $this->db()->select()
->from('User')
->group(['name']);
Diese Beispiele entsprechen der Query
SELECT * FROM `[prefix]_User` GROUP BY `name`;
Naja kommen wir am Ende noch zu Joins, dem wohl schwierigsten Thema. An dieser Stelle noch der Hinweis, für die Join Typen sollten die Konstanten der Join Klasse verwendet werden, um das Beispiel kürzer zu halten, habe ich direkt die Stringrepräsentationen ('INNER' statt \Ilch\Database\Mysql\Expression\Join::INNER) verwendet.
Bei der Angabe der Bedingungen für die Joins ist zu beachten, dass wenn man Felder miteinander vergleichen will. Der Vergleich als ein einzelner String anzugeben ist.
//Kurze Variante
$select = $this->db()->select()
->fields(['u.name', 'group' => 'g.name'])
->from(['u' => 'User'])
->join(['g' => 'Group'], 'u.group_id = g.id');
//2 . Kurze Variante
$select = $this->db()->select()
->fields(['u.name'])
->from(['u' => 'User'])
->join(['g' => 'Group'], 'u.group_id = g.id', 'INNER', ['group' => 'g.name']);
//Lange Variante
$select = $this->db()->select()
->fields(['u.name'])
->from(['u' => 'User']);
$join = $select->createJoin(['g' => 'Group']);
$join->setFields(['group' => 'g.name'])
->setConditions(['u.group_id = g.id']);
$select->addJoin($join);
Diese Beispiele entsprechen der Query
SELECT `u`.`name`, `g`.`name` AS `group` FROM `[prefix]_User` AS `u` INNER JOIN `[prefix]_Group` AS `g` ON `u`.`group_id` = `g`.`id`;
Es ist natürlich möglich mehrere Joins auf einmal zu verwenden und auch normale Vergleiche zu verwenden, wenn man nicht Felder miteinander vergleicht.
$select = $this->db()->select()
->fields(['u.name'])
->from(['u' => 'User'])
->join(['g' => 'Group'], 'u.group_id = g.id', 'INNER', ['group' => 'g.name'])
->join(['a' => 'Article'], ['u.id = a.user_id', 'a.category' => 'Test'], 'LEFT', ['articles' => 'COUNT(a.id)'])
->group(['u.name']);
Dieses Beispiel entspricht der Query (zur besseren Lesbarkeit Umbrüche eingefügt)
SELECT `u`.`name`, `g`.`name` AS `group`, COUNT(a.id) AS `articles`
FROM `[prefix]_User` AS `u`
INNER JOIN `[prefix]_Group` AS `g` ON `u`.`group_id` = `g`.`id`
LEFT JOIN `[prefix_]Article` ON (`u`.`id` = `a`.`user_id` AND `a`.`category` = "Test")
GROUP BY `u`.`name`;
Dokumentation für ilch 2 | Content-Management-System | Copyright © 2004-2024 by ilch.de
-
Inhaltsverzeichnis
Hier findet man u.a. die Installationsanleitung, sowie Fehler- und Problem-behebungen
-
Inhaltsverzeichnis
Hier findet man Informationen zum Script, Konzept und dem Programmierstil
-
Inhaltsverzeichnis
Hier findet man Informationen über den Aufbau und Anpassung von Layouts und Modulen
-
Inhaltsverzeichnis
Hier findet man einige Video-Tutorials um und mit unserem CMS