Created
May 12, 2024 00:07
-
-
Save oplanre/b18b6823a6899e6825e9a16babfd8d42 to your computer and use it in GitHub Desktop.
PHP querybuilder WIP
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
readonly class RelationshipInverse | |
{ | |
public function __construct( | |
public string $pivotTable, | |
public string $relatedForeignKey, | |
public string $localKey, | |
public string $type | |
) { | |
} | |
public function toRelationship() | |
{ | |
return new Relationship($this->type, $this->pivotTable, $this->relatedForeignKey, $this->localKey, $this); | |
} | |
} | |
readonly class Relationship | |
{ | |
const ONE_TO_ONE = 'oneToOne'; | |
const ONE_TO_MANY = 'oneToMany'; | |
const MANY_TO_MANY = 'manyToMany'; | |
public function __construct( | |
public string $type, | |
public string $table, | |
public string $foreignKey, | |
public string $localKey, | |
public ?RelationshipInverse $inverse = null | |
) { | |
} | |
} | |
class QueryBuilder | |
{ | |
public function __construct( | |
protected PDO $pdo, | |
protected string $table, | |
protected string $select = '*', | |
protected array $where = [], | |
protected array $params = [], | |
protected array $orderBy = [], | |
protected array $join = [], | |
protected array $relationships = [], | |
protected bool $transactionStarted = false, | |
protected ?int $limit = null | |
) { | |
} | |
public function select(string|array $columns) | |
{ | |
$this->select = is_array($columns) ? implode(', ', $columns) : $columns; | |
return $this; | |
} | |
public function where(string $column, string $value, string $operator = '=') | |
{ | |
$this->where[] = "$column $operator :$column"; | |
$this->params[$column] = $value; | |
return $this; | |
} | |
public function orderBy(string $column, string $direction = 'ASC') | |
{ | |
$this->orderBy[] = "$column $direction"; | |
return $this; | |
} | |
public function limit(int $limit) | |
{ | |
$this->limit = $limit; | |
return $this; | |
} | |
public function join(string $table, string $firstColumn, string $operator, string $secondColumn) | |
{ | |
$this->join[] = "JOIN $table ON $firstColumn $operator :$firstColumn"; | |
$this->params[$firstColumn] = $secondColumn; | |
return $this; | |
} | |
public function beginTransaction() | |
{ | |
$this->pdo->beginTransaction(); | |
$this->transactionStarted = true; | |
} | |
public function commit() | |
{ | |
if ($this->transactionStarted) { | |
$this->pdo->commit(); | |
$this->transactionStarted = false; | |
} | |
} | |
public function rollback() | |
{ | |
if ($this->transactionStarted) { | |
$this->pdo->rollBack(); | |
$this->transactionStarted = false; | |
} | |
} | |
public function insert(array $data): int | |
{ | |
[$columns, $values] = [ | |
implode(', ', array_keys($data)), | |
implode(', ', array_map(fn($column) => ":$column", array_keys($data))) | |
]; | |
$statement = $this->pdo->prepare("INSERT INTO {$this->table} ({$columns}) VALUES ({$values})"); | |
$statement->execute($data); | |
return $this->pdo->lastInsertId(); | |
} | |
private function getImpl(): \PDOStatement | |
{ | |
$query = "SELECT {$this->select} FROM {$this->table}"; | |
if (!empty($this->join)) { | |
$query .= ' ' . implode(' ', $this->join); | |
} | |
if (!empty($this->where)) { | |
$query .= " WHERE " . implode(' AND ', $this->where); | |
} | |
if (!empty($this->orderBy)) { | |
$query .= " ORDER BY " . implode(', ', $this->orderBy); | |
} | |
if (!empty($this->limit)) { | |
$query .= " LIMIT " . $this->limit; | |
} | |
$statement = $this->pdo->prepare($query); | |
if (!empty($this->where)) { | |
$statement->execute($this->params); // Bind and execute the prepared statement | |
} else { | |
$statement->execute(); | |
} | |
return $statement; | |
} | |
public function get(): array | |
{ | |
return $this->getImpl()->fetchAll(PDO::FETCH_ASSOC|PDO::FETCH_PROPS_LATE); | |
} | |
public function getObjects(string $class): array | |
{ | |
return $this->getImpl()->fetchAll(PDO::FETCH_CLASS, $class); | |
} | |
public function getAsObjectOf(?string $class) | |
{ | |
return $class ? $this->getObjects($class) : $this->get(); | |
} | |
public function delete(): void | |
{ | |
$query = "DELETE FROM {$this->table}"; | |
if (!empty($this->where)) { | |
$query .= " WHERE " . implode(' AND ', $this->where); | |
} | |
$statement = $this->pdo->prepare($query); | |
$statement->execute($this->params); | |
} | |
public function update(array $data): void | |
{ | |
$columns = array_map(fn($column) => "$column = ?", array_keys($data)); | |
$query = "UPDATE {$this->table} SET " . implode(', ', $columns); | |
if (!empty($this->where)) { | |
$query .= " WHERE " . implode(' AND ', $this->where); | |
} | |
$statement = $this->pdo->prepare($query); | |
$statement->execute(array_merge(array_values($data), $this->params)); | |
} | |
public function hasOne(string $table, string $foreignKey, string $localKey = 'id') | |
{ | |
$this->relationships[] = new Relationship(Relationship::ONE_TO_ONE, $table, $foreignKey, $localKey); | |
return $this; | |
} | |
public function hasMany(string $table, string $foreignKey, string $localKey = 'id') | |
{ | |
$this->relationships[] = new Relationship(Relationship::ONE_TO_MANY, $table, $foreignKey, $localKey); | |
return $this; | |
} | |
public function belongsToMany(string $table, string $pivotTable, string $relatedForeignKey, string $foreignKey, string $localKey = 'id') | |
{ | |
$this->relationships[] = new Relationship( | |
Relationship::MANY_TO_MANY, | |
$table, | |
$foreignKey, | |
$localKey, | |
new RelationshipInverse($pivotTable, $relatedForeignKey, $localKey, Relationship::MANY_TO_MANY) | |
); | |
return $this; | |
} | |
public function getWithRelations(?string $asObjectsOf = null) | |
{ | |
$results = $this->getAsObjectOf($asObjectsOf); | |
foreach ($results as &$result) { | |
foreach ($this->relationships as $relation) { | |
$result[$relation->table] = $this->getRelatedResults($relation, $result, $relation->type === Relationship::MANY_TO_MANY); | |
if ($relation->inverse) | |
$result[$relation->inverse->pivotTable] = $this->getRelatedResults($relation->inverse->toRelationship(), $result); | |
} | |
} | |
return $results; | |
} | |
protected function getRelatedResults(Relationship $relation, array $result, bool $isManyToMany = false, ?string $asObjectsOf = null) | |
{ | |
$queryBuilder = new self($this->pdo, $relation->table); | |
if ($isManyToMany) { | |
$queryBuilder | |
->join($relation->inverse->pivotTable, $relation->localKey, '=', $relation->foreignKey) | |
->join($relation->table, $relation->inverse->relatedForeignKey, '=', $relation->localKey); | |
} else { | |
$queryBuilder->where($relation->foreignKey, $result[$relation->localKey]); | |
} | |
return $queryBuilder->get(); | |
} | |
} | |
class PDOConfigurator | |
{ | |
public static function mysql(string $host, string $db, string $user, string $password): PDO | |
{ | |
return new PDO("mysql:host=$host;dbname=$db", $user, $password); | |
} | |
public static function sqlite(string $db): PDO | |
{ | |
return new PDO("sqlite:$db"); | |
} | |
public static function sqliteMemory(): PDO | |
{ | |
return new PDO('sqlite::memory:'); | |
} | |
public static function pgsql(string $host, string $db, string $user, string $password): PDO | |
{ | |
return new PDO("pgsql:host=$host;dbname=$db", $user, $password); | |
} | |
public static function sqlsrv(string $host, string $db, string $user, string $password): PDO | |
{ | |
return new PDO("sqlsrv:Server=$host;Database=$db", $user, $password); | |
} | |
public static function oci(string $host, string $db, string $user, string $password): PDO | |
{ | |
return new PDO("oci:dbname=//$host/$db", $user, $password); | |
} | |
public static function firebird(string $host, string $db, string $user, string $password): PDO | |
{ | |
return new PDO("firebird:dbname=$host:$db", $user, $password); | |
} | |
public static function informix(string $host, string $db, string $user, string $password): PDO | |
{ | |
return new PDO("informix:host=$host;database=$db", $user, $password); | |
} | |
public static function ibm(string $host, string $db, string $user, string $password, string $port = '50000'): PDO | |
{ | |
return new PDO("ibm:DRIVER={IBM DB2 ODBC DRIVER};DATABASE=$db;HOSTNAME=$host;PORT=$port;PROTOCOL=TCPIP;", $user, $password); | |
} | |
public static function odbc(string $dsn, string $user, string $password): PDO | |
{ | |
return new PDO("odbc:$dsn", $user, $password); | |
} | |
public static function mssql(string $host, string $db, string $user, string $password): PDO | |
{ | |
return new PDO("mssql:host=$host;dbname=$db", $user, $password); | |
} | |
public static function dblib(string $host, string $db, string $user, string $password): PDO | |
{ | |
return new PDO("dblib:host=$host;dbname=$db", $user, $password); | |
} | |
public static function sybase(string $host, string $db, string $user, string $password): PDO | |
{ | |
return new PDO("sybase:host=$host;dbname=$db", $user, $password); | |
} | |
public static function cubrid(string $host, string $db, string $user, string $password): PDO | |
{ | |
return new PDO("cubrid:host=$host;dbname=$db", $user, $password); | |
} | |
public static function dsn(string $dsn, string $user, string $password): PDO | |
{ | |
return new PDO($dsn, $user, $password); | |
} | |
public static function sqlanywhere(string $host, string $db, string $user, string $password): PDO | |
{ | |
return new PDO("sqlanywhere:host=$host;dbname=$db", $user, $password); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment