Project

General

Profile

Actions

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' => 'hans@mail.de'])->execute();

//Lange Form
$insert = $this->db()->insert();
$userId = $insert->into('User')
    ->values(['name' => 'Hans', 'email' => 'hans@mail.de'])
    ->execute();

Diese Beispiele entsprechen der Query

INSERT INTO `[prefix]_User` (`name`, `email`) VALUES ("Hans","hans@mail.de");

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:

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);                // -> 'hans@mail.de'
$email = $result->fetchCell('email');          // -> 'hans@mail.de'
$invalid = $result->fetchCell('gibtsnicht');   // -> false

//fetchArray
$array = $result->fetchArray();                // -> [0 => 'Hans', 1 => 'hans@mail.de', 'name' => 'Hans', 'email' => 'hans@mail.de']

//fetchRow
$array = $result->fetchRow();                  // -> [0 => 'Hans', 1 => 'hans@mail.de']

//fetchAssoc
$array = $result->fetchAssoc();                // -> ['name' => 'Hans', 'email' => 'hans@mail.de']

//fetchObject
$array = $result->fetchObject();               // -> stdClass('name' => 'Hans', 'email' => 'hans@mail.de')

//fetchRows 
$array = $result->fetchRows();                 // -> [0 => ['name' => 'Hans', 'email' => 'hans@mail.de']]
$array = $result->fetchRows('name');           // -> ['Hans' => ['name' => 'Hans', 'email' => 'hans@mail.de']]

//fetchList
$list  = $result->fetchList();                 // -> [0 => 'Hans']
$list  = $result->fetchList('name');           // -> [0 => 'Hans']
$list  = $result->fetchList('email');          // -> [0 => 'hans@mail.de']
$list  = $result->fetchList('name', 'email');  // -> ['Hans' => 'hans@mail.de']

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

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

Diese Beispiele entsprechen der Query

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

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

Updated by -, blackcoder 7 days ago · 6 revisions