PostgresGrammar.php
TLDR
This file, PostgresGrammar.php, is a class that extends the Grammar class and provides specific functionality for working with PostgreSQL databases. It contains methods for compiling various types of clauses in SQL queries, such as basic where clauses, bitwise operator where clauses, date clauses, time clauses, fulltext clauses, select clauses, JSON clauses, having clauses, lock clauses, insert clauses, update clauses, delete clauses, truncate clauses, and more.
Methods
whereBasic
Compiles a basic where clause for a query.
whereBitwise
Compiles a bitwise operator where clause for a query.
whereDate
Compiles a "where date" clause for a query.
whereTime
Compiles a "where time" clause for a query.
dateBasedWhere
Compiles a date based where clause for a query.
whereFullText
Compiles a "where fulltext" clause for a query.
validFullTextLanguages
Returns an array of valid full text languages for PostgreSQL.
compileColumns
Compiles the "select *" portion of the query.
compileJsonContains
Compiles a "JSON contains" statement into SQL.
compileJsonContainsKey
Compiles a "JSON contains key" statement into SQL.
compileJsonLength
Compiles a "JSON length" statement into SQL.
compileHaving
Compiles a single having clause.
compileHavingBitwise
Compiles a having clause involving a bitwise operator.
compileLock
Compiles the lock into SQL.
compileInsertOrIgnore
Compiles an insert ignore statement into SQL.
compileInsertGetId
Compiles an insert and get ID statement into SQL.
compileUpdate
Compiles an update statement into SQL.
compileUpdateColumns
Compiles the columns for an update statement.
compileUpsert
Compiles an "upsert" statement into SQL.
prepareBindingsForUpdateFrom
Prepares the bindings for an update from statement.
compileUpdateWithJoinsOrLimit
Compiles an update statement with joins or limit into SQL.
prepareBindingsForUpdate
Prepares the bindings for an update statement.
compileDelete
Compiles a delete statement into SQL.
compileDeleteWithJoinsOrLimit
Compiles a delete statement with joins or limit into SQL.
compileTruncate
Compiles a truncate table statement into SQL.
wrapJsonSelector
Wraps the given JSON selector.
wrapJsonBooleanSelector
Wraps the given JSON selector for boolean values.
wrapJsonBooleanValue
Wraps the given JSON boolean value.
wrapJsonPathAttributes
Wraps the attributes of the given JSON path.
parseJsonPathArrayKeys
Parses the given JSON path attribute for array keys.
substituteBindingsIntoRawSql
Substitutes the given bindings into the given raw SQL query.
Classes
None
<?php
namespace Illuminate\Database\Query\Grammars;
use Illuminate\Database\Query\Builder;
use Illuminate\Support\Arr;
use Illuminate\Support\Str;
class PostgresGrammar extends Grammar
{
/**
* All of the available clause operators.
*
* @var string[]
*/
protected $operators = [
'=', '<', '>', '<=', '>=', '<>', '!=',
'like', 'not like', 'between', 'ilike', 'not ilike',
'~', '&', '|', '#', '<<', '>>', '<<=', '>>=',
'&&', '@>', '<@', '?', '?|', '?&', '||', '-', '@?', '@@', '#-',
'is distinct from', 'is not distinct from',
];
/**
* The grammar specific bitwise operators.
*
* @var array
*/
protected $bitwiseOperators = [
'~', '&', '|', '#', '<<', '>>', '<<=', '>>=',
];
/**
* Compile a basic where clause.
*
* @param \Illuminate\Database\Query\Builder $query
* @param array $where
* @return string
*/
protected function whereBasic(Builder $query, $where)
{
if (str_contains(strtolower($where['operator']), 'like')) {
return sprintf(
'%s::text %s %s',
$this->wrap($where['column']),
$where['operator'],
$this->parameter($where['value'])
);
}
return parent::whereBasic($query, $where);
}
/**
* Compile a bitwise operator where clause.
*
* @param \Illuminate\Database\Query\Builder $query
* @param array $where
* @return string
*/
protected function whereBitwise(Builder $query, $where)
{
$value = $this->parameter($where['value']);
$operator = str_replace('?', '??', $where['operator']);
return '('.$this->wrap($where['column']).' '.$operator.' '.$value.')::bool';
}
/**
* Compile a "where date" clause.
*
* @param \Illuminate\Database\Query\Builder $query
* @param array $where
* @return string
*/
protected function whereDate(Builder $query, $where)
{
$value = $this->parameter($where['value']);
return $this->wrap($where['column']).'::date '.$where['operator'].' '.$value;
}
/**
* Compile a "where time" clause.
*
* @param \Illuminate\Database\Query\Builder $query
* @param array $where
* @return string
*/
protected function whereTime(Builder $query, $where)
{
$value = $this->parameter($where['value']);
return $this->wrap($where['column']).'::time '.$where['operator'].' '.$value;
}
/**
* Compile a date based where clause.
*
* @param string $type
* @param \Illuminate\Database\Query\Builder $query
* @param array $where
* @return string
*/
protected function dateBasedWhere($type, Builder $query, $where)
{
$value = $this->parameter($where['value']);
return 'extract('.$type.' from '.$this->wrap($where['column']).') '.$where['operator'].' '.$value;
}
/**
* Compile a "where fulltext" clause.
*
* @param \Illuminate\Database\Query\Builder $query
* @param array $where
* @return string
*/
public function whereFullText(Builder $query, $where)
{
$language = $where['options']['language'] ?? 'english';
if (! in_array($language, $this->validFullTextLanguages())) {
$language = 'english';
}
$columns = collect($where['columns'])->map(function ($column) use ($language) {
return "to_tsvector('{$language}', {$this->wrap($column)})";
})->implode(' || ');
$mode = 'plainto_tsquery';
if (($where['options']['mode'] ?? []) === 'phrase') {
$mode = 'phraseto_tsquery';
}
if (($where['options']['mode'] ?? []) === 'websearch') {
$mode = 'websearch_to_tsquery';
}
return "({$columns}) @@ {$mode}('{$language}', {$this->parameter($where['value'])})";
}
/**
* Get an array of valid full text languages.
*
* @return array
*/
protected function validFullTextLanguages()
{
return [
'simple',
'arabic',
'danish',
'dutch',
'english',
'finnish',
'french',
'german',
'hungarian',
'indonesian',
'irish',
'italian',
'lithuanian',
'nepali',
'norwegian',
'portuguese',
'romanian',
'russian',
'spanish',
'swedish',
'tamil',
'turkish',
];
}
/**
* Compile the "select *" portion of the query.
*
* @param \Illuminate\Database\Query\Builder $query
* @param array $columns
* @return string|null
*/
protected function compileColumns(Builder $query, $columns)
{
// If the query is actually performing an aggregating select, we will let that
// compiler handle the building of the select clauses, as it will need some
// more syntax that is best handled by that function to keep things neat.
if (! is_null($query->aggregate)) {
return;
}
if (is_array($query->distinct)) {
$select = 'select distinct on ('.$this->columnize($query->distinct).') ';
} elseif ($query->distinct) {
$select = 'select distinct ';
} else {
$select = 'select ';
}
return $select.$this->columnize($columns);
}
/**
* Compile a "JSON contains" statement into SQL.
*
* @param string $column
* @param string $value
* @return string
*/
protected function compileJsonContains($column, $value)
{
$column = str_replace('->>', '->', $this->wrap($column));
return '('.$column.')::jsonb @> '.$value;
}
/**
* Compile a "JSON contains key" statement into SQL.
*
* @param string $column
* @return string
*/
protected function compileJsonContainsKey($column)
{
$segments = explode('->', $column);
$lastSegment = array_pop($segments);
if (filter_var($lastSegment, FILTER_VALIDATE_INT) !== false) {
$i = $lastSegment;
} elseif (preg_match('/\[(-?[0-9]+)\]$/', $lastSegment, $matches)) {
$segments[] = Str::beforeLast($lastSegment, $matches[0]);
$i = $matches[1];
}
$column = str_replace('->>', '->', $this->wrap(implode('->', $segments)));
if (isset($i)) {
return vsprintf('case when %s then %s else false end', [
'jsonb_typeof(('.$column.")::jsonb) = 'array'",
'jsonb_array_length(('.$column.')::jsonb) >= '.($i < 0 ? abs($i) : $i + 1),
]);
}
$key = "'".str_replace("'", "''", $lastSegment)."'";
return 'coalesce(('.$column.')::jsonb ?? '.$key.', false)';
}
/**
* Compile a "JSON length" statement into SQL.
*
* @param string $column
* @param string $operator
* @param string $value
* @return string
*/
protected function compileJsonLength($column, $operator, $value)
{
$column = str_replace('->>', '->', $this->wrap($column));
return 'jsonb_array_length(('.$column.')::jsonb) '.$operator.' '.$value;
}
/**
* Compile a single having clause.
*
* @param array $having
* @return string
*/
protected function compileHaving(array $having)
{
if ($having['type'] === 'Bitwise') {
return $this->compileHavingBitwise($having);
}
return parent::compileHaving($having);
}
/**
* Compile a having clause involving a bitwise operator.
*
* @param array $having
* @return string
*/
protected function compileHavingBitwise($having)
{
$column = $this->wrap($having['column']);
$parameter = $this->parameter($having['value']);
return '('.$column.' '.$having['operator'].' '.$parameter.')::bool';
}
/**
* Compile the lock into SQL.
*
* @param \Illuminate\Database\Query\Builder $query
* @param bool|string $value
* @return string
*/
protected function compileLock(Builder $query, $value)
{
if (! is_string($value)) {
return $value ? 'for update' : 'for share';
}
return $value;
}
/**
* Compile an insert ignore statement into SQL.
*
* @param \Illuminate\Database\Query\Builder $query
* @param array $values
* @return string
*/
public function compileInsertOrIgnore(Builder $query, array $values)
{
return $this->compileInsert($query, $values).' on conflict do nothing';
}
/**
* Compile an insert and get ID statement into SQL.
*
* @param \Illuminate\Database\Query\Builder $query
* @param array $values
* @param string $sequence
* @return string
*/
public function compileInsertGetId(Builder $query, $values, $sequence)
{
return $this->compileInsert($query, $values).' returning '.$this->wrap($sequence ?: 'id');
}
/**
* Compile an update statement into SQL.
*
* @param \Illuminate\Database\Query\Builder $query
* @param array $values
* @return string
*/
public function compileUpdate(Builder $query, array $values)
{
if (isset($query->joins) || isset($query->limit)) {
return $this->compileUpdateWithJoinsOrLimit($query, $values);
}
return parent::compileUpdate($query, $values);
}
/**
* Compile the columns for an update statement.
*
* @param \Illuminate\Database\Query\Builder $query
* @param array $values
* @return string
*/
protected function compileUpdateColumns(Builder $query, array $values)
{
return collect($values)->map(function ($value, $key) {
$column = last(explode('.', $key));
if ($this->isJsonSelector($key)) {
return $this->compileJsonUpdateColumn($column, $value);
}
return $this->wrap($column).' = '.$this->parameter($value);
})->implode(', ');
}
/**
* Compile an "upsert" statement into SQL.
*
* @param \Illuminate\Database\Query\Builder $query
* @param array $values
* @param array $uniqueBy
* @param array $update
* @return string
*/
public function compileUpsert(Builder $query, array $values, array $uniqueBy, array $update)
{
$sql = $this->compileInsert($query, $values);
$sql .= ' on conflict ('.$this->columnize($uniqueBy).') do update set ';
$columns = collect($update)->map(function ($value, $key) {
return is_numeric($key)
? $this->wrap($value).' = '.$this->wrapValue('excluded').'.'.$this->wrap($value)
: $this->wrap($key).' = '.$this->parameter($value);
})->implode(', ');
return $sql.$columns;
}
/**
* Prepares a JSON column being updated using the JSONB_SET function.
*
* @param string $key
* @param mixed $value
* @return string
*/
protected function compileJsonUpdateColumn($key, $value)
{
$segments = explode('->', $key);
$field = $this->wrap(array_shift($segments));
$path = "'{".implode(',', $this->wrapJsonPathAttributes($segments, '"'))."}'";
return "{$field} = jsonb_set({$field}::jsonb, {$path}, {$this->parameter($value)})";
}
/**
* Compile an update from statement into SQL.
*
* @param \Illuminate\Database\Query\Builder $query
* @param array $values
* @return string
*/
public function compileUpdateFrom(Builder $query, $values)
{
$table = $this->wrapTable($query->from);
// Each one of the columns in the update statements needs to be wrapped in the
// keyword identifiers, also a place-holder needs to be created for each of
// the values in the list of bindings so we can make the sets statements.
$columns = $this->compileUpdateColumns($query, $values);
$from = '';
if (isset($query->joins)) {
// When using Postgres, updates with joins list the joined tables in the from
// clause, which is different than other systems like MySQL. Here, we will
// compile out the tables that are joined and add them to a from clause.
$froms = collect($query->joins)->map(function ($join) {
return $this->wrapTable($join->table);
})->all();
if (count($froms) > 0) {
$from = ' from '.implode(', ', $froms);
}
}
$where = $this->compileUpdateWheres($query);
return trim("update {$table} set {$columns}{$from} {$where}");
}
/**
* Compile the additional where clauses for updates with joins.
*
* @param \Illuminate\Database\Query\Builder $query
* @return string
*/
protected function compileUpdateWheres(Builder $query)
{
$baseWheres = $this->compileWheres($query);
if (! isset($query->joins)) {
return $baseWheres;
}
// Once we compile the join constraints, we will either use them as the where
// clause or append them to the existing base where clauses. If we need to
// strip the leading boolean we will do so when using as the only where.
$joinWheres = $this->compileUpdateJoinWheres($query);
if (trim($baseWheres) == '') {
return 'where '.$this->removeLeadingBoolean($joinWheres);
}
return $baseWheres.' '.$joinWheres;
}
/**
* Compile the "join" clause where clauses for an update.
*
* @param \Illuminate\Database\Query\Builder $query
* @return string
*/
protected function compileUpdateJoinWheres(Builder $query)
{
$joinWheres = [];
// Here we will just loop through all of the join constraints and compile them
// all out then implode them. This should give us "where" like syntax after
// everything has been built and then we will join it to the real wheres.
foreach ($query->joins as $join) {
foreach ($join->wheres as $where) {
$method = "where{$where['type']}";
$joinWheres[] = $where['boolean'].' '.$this->$method($query, $where);
}
}
return implode(' ', $joinWheres);
}
/**
* Prepare the bindings for an update statement.
*
* @param array $bindings
* @param array $values
* @return array
*/
public function prepareBindingsForUpdateFrom(array $bindings, array $values)
{
$values = collect($values)->map(function ($value, $column) {
return is_array($value) || ($this->isJsonSelector($column) && ! $this->isExpression($value))
? json_encode($value)
: $value;
})->all();
$bindingsWithoutWhere = Arr::except($bindings, ['select', 'where']);
return array_values(
array_merge($values, $bindings['where'], Arr::flatten($bindingsWithoutWhere))
);
}
/**
* Compile an update statement with joins or limit into SQL.
*
* @param \Illuminate\Database\Query\Builder $query
* @param array $values
* @return string
*/
protected function compileUpdateWithJoinsOrLimit(Builder $query, array $values)
{
$table = $this->wrapTable($query->from);
$columns = $this->compileUpdateColumns($query, $values);
$alias = last(preg_split('/\s+as\s+/i', $query->from));
$selectSql = $this->compileSelect($query->select($alias.'.ctid'));
return "update {$table} set {$columns} where {$this->wrap('ctid')} in ({$selectSql})";
}
/**
* Prepare the bindings for an update statement.
*
* @param array $bindings
* @param array $values
* @return array
*/
public function prepareBindingsForUpdate(array $bindings, array $values)
{
$values = collect($values)->map(function ($value, $column) {
return is_array($value) || ($this->isJsonSelector($column) && ! $this->isExpression($value))
? json_encode($value)
: $value;
})->all();
$cleanBindings = Arr::except($bindings, 'select');
return array_values(
array_merge($values, Arr::flatten($cleanBindings))
);
}
/**
* Compile a delete statement into SQL.
*
* @param \Illuminate\Database\Query\Builder $query
* @return string
*/
public function compileDelete(Builder $query)
{
if (isset($query->joins) || isset($query->limit)) {
return $this->compileDeleteWithJoinsOrLimit($query);
}
return parent::compileDelete($query);
}
/**
* Compile a delete statement with joins or limit into SQL.
*
* @param \Illuminate\Database\Query\Builder $query
* @return string
*/
protected function compileDeleteWithJoinsOrLimit(Builder $query)
{
$table = $this->wrapTable($query->from);
$alias = last(preg_split('/\s+as\s+/i', $query->from));
$selectSql = $this->compileSelect($query->select($alias.'.ctid'));
return "delete from {$table} where {$this->wrap('ctid')} in ({$selectSql})";
}
/**
* Compile a truncate table statement into SQL.
*
* @param \Illuminate\Database\Query\Builder $query
* @return array
*/
public function compileTruncate(Builder $query)
{
return ['truncate '.$this->wrapTable($query->from).' restart identity cascade' => []];
}
/**
* Wrap the given JSON selector.
*
* @param string $value
* @return string
*/
protected function wrapJsonSelector($value)
{
$path = explode('->', $value);
$field = $this->wrapSegments(explode('.', array_shift($path)));
$wrappedPath = $this->wrapJsonPathAttributes($path);
$attribute = array_pop($wrappedPath);
if (! empty($wrappedPath)) {
return $field.'->'.implode('->', $wrappedPath).'->>'.$attribute;
}
return $field.'->>'.$attribute;
}
/**
* Wrap the given JSON selector for boolean values.
*
* @param string $value
* @return string
*/
protected function wrapJsonBooleanSelector($value)
{
$selector = str_replace(
'->>', '->',
$this->wrapJsonSelector($value)
);
return '('.$selector.')::jsonb';
}
/**
* Wrap the given JSON boolean value.
*
* @param string $value
* @return string
*/
protected function wrapJsonBooleanValue($value)
{
return "'".$value."'::jsonb";
}
/**
* Wrap the attributes of the given JSON path.
*
* @param array $path
* @return array
*/
protected function wrapJsonPathAttributes($path)
{
$quote = func_num_args() === 2 ? func_get_arg(1) : "'";
return collect($path)->map(function ($attribute) {
return $this->parseJsonPathArrayKeys($attribute);
})->collapse()->map(function ($attribute) use ($quote) {
return filter_var($attribute, FILTER_VALIDATE_INT) !== false
? $attribute
: $quote.$attribute.$quote;
})->all();
}
/**
* Parse the given JSON path attribute for array keys.
*
* @param string $attribute
* @return array
*/
protected function parseJsonPathArrayKeys($attribute)
{
if (preg_match('/(\[[^\]]+\])+$/', $attribute, $parts)) {
$key = Str::beforeLast($attribute, $parts[0]);
preg_match_all('/\[([^\]]+)\]/', $parts[0], $keys);
return collect([$key])
->merge($keys[1])
->diff('')
->values()
->all();
}
return [$attribute];
}
/**
* Substitute the given bindings into the given raw SQL query.
*
* @param string $sql
* @param array $bindings
* @return string
*/
public function substituteBindingsIntoRawSql($sql, $bindings)
{
$query = parent::substituteBindingsIntoRawSql($sql, $bindings);
foreach ($this->operators as $operator) {
if (! str_contains($operator, '?')) {
continue;
}
$query = str_replace(str_replace('?', '??', $operator), $operator, $query);
}
return $query;
}
}