Doku Entwickler Database QueryBuilder

Database QueryBuilder

Inhaltsverzeichnis

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

Insert

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

Delete

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";

Update

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";

Select und Result

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";

Result

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.

Funktionen

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`;

Verwendung von Aliasen

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`;

Kompliziertere Bedingungen - 1. andere Operatoren als =

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

Kompliziertere Bedingungen - Oder Bedingung

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");

Kompliziertere Bedingungen - Verschachtelte Bedingung mit AND und OR

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");

Sortierung

//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.

Verwendung von Limit (ggf. mit offset und page)

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;

Gruppierung (GROUP BY)

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']);

Dieses Beispiel entspricht der Query

SELECT * FROM `[prefix]_User` GROUP BY `name`;

Es ist ebenfalls möglich Funktionen wie MONTH() oder MAX() zu nutzen. Sofern in einem Feld ein Funktionsaufruf gefunden wird, werden keine Quotes gesetzt.

Joins

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`;

Wiki für ilch-2

Herzlich Willkommen

Benutzer

Inhaltsverzeichnis
Hier findet man u.a. die Installationsanleitung, sowie Fehler- und Problem-behebungen

Entwickler

Inhaltsverzeichnis
Hier findet man Informationen zum Script, Konzept und dem Programmierstil

Designer

Inhaltsverzeichnis
Hier findet man Informationen über den Aufbau und Anpassung von Layouts und Modulen

Video-Tutorials

Inhaltsverzeichnis
Hier findet man einige Video-Tutorials um und mit unserem CMS

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Doku Entwickler Database QueryBuilder

Database QueryBuilder

Insert

Delete

Update

Select und Result

Result

Funktionen

Verwendung von Aliasen

Kompliziertere Bedingungen - 1. andere Operatoren als =

Kompliziertere Bedingungen - Oder Bedingung

Kompliziertere Bedingungen - Verschachtelte Bedingung mit AND und OR

Sortierung

Verwendung von Limit (ggf. mit offset und page)

Gruppierung (GROUP BY)

Joins

Wiki für ilch-2

Benutzer

Entwickler

Designer

Video-Tutorials

Clone this wiki locally