<?php
namespace Illuminate\Database\Query\Grammars;
use Illuminate\Support\Arr;
use Illuminate\Support\Str;
use Illuminate\Database\Query\Builder;
class PostgresGrammar extends Grammar
{
/**
* All of the available clause operators.
*
* @var array
*/
protected $operators = [
'=', '<', '>', '<=', '>=', '<>', '!=',
'like', 'not like', 'between', 'ilike', 'not ilike',
'~', '&', '|', '#', '<<', '>>', '<<=', '>>=',
'&&', '@>', '<@', '?', '?|', '?&', '||', '-', '-', '#-',
'is distinct from', 'is not distinct from',
];
/**
* 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 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;
}
/**
* {@inheritdoc}
*/
public function compileInsert(Builder $query, array $values)
{
$table = $this->wrapTable($query->from);
return empty($values)
? "insert into {$table} DEFAULT VALUES"
: parent::compileInsert($query, $values);
}
/**
* 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)
{
if (is_null($sequence)) {
$sequence = 'id';
}
return $this->compileInsert($query, $values).' returning '.$this->wrap($sequence);
}
/**
* Compile an update statement into SQL.
*
* @param \Illuminate\Database\Query\Builder $query
* @param array $values
* @return string
*/
public function compileUpdate(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($values);
$from = $this->compileUpdateFrom($query);
$where = $this->compileUpdateWheres($query);
return trim("update {$table} set {$columns}{$from} {$where}");
}
/**
* Compile the columns for the update statement.
*
* @param array $values
* @return string
*/
protected function compileUpdateColumns($values)
{
// When gathering the columns for an update statement, we'll wrap each of the
// columns and convert it to a parameter value. Then we will concatenate a
// list of the columns that can be added into this update query clauses.
return collect($values)->map(function ($value, $key) {
return $this->wrap($key).' = '.$this->parameter($value);
})->implode(', ');
}
/**
* Compile the "from" clause for an update with a join.
*
* @param \Illuminate\Database\Query\Builder $query
* @return string|null
*/
protected function compileUpdateFrom(Builder $query)
{
if (! isset($query->joins)) {
return '';
}
// 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) {
return ' from '.implode(', ', $froms);
}
}
/**
* 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 prepareBindingsForUpdate(array $bindings, array $values)
{
// Update statements with "joins" in Postgres use an interesting syntax. We need to
// take all of the bindings and put them on the end of this array since they are
// added to the end of the "where" clause statements as typical where clauses.
$bindingsWithoutJoin = Arr::except($bindings, 'join');
return array_values(
array_merge($values, $bindings['join'], Arr::flatten($bindingsWithoutJoin))
);
}
/**
* Compile a delete statement into SQL.
*
* @param \Illuminate\Database\Query\Builder $query
* @return string
*/
public function compileDelete(Builder $query)
{
$table = $this->wrapTable($query->from);
return isset($query->joins)
? $this->compileDeleteWithJoins($query, $table)
: parent::compileDelete($query);
}
/**
* Compile a delete query that uses joins.
*
* @param \Illuminate\Database\Query\Builder $query
* @param string $table
* @return string
*/
protected function compileDeleteWithJoins($query, $table)
{
$using = ' USING '.collect($query->joins)->map(function ($join) {
return $this->wrapTable($join->table);
})->implode(', ');
$where = count($query->wheres) > 0 ? ' '.$this->compileUpdateWheres($query) : '';
return trim("delete from {$table}{$using}{$where}");
}
/**
* 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' => []];
}
/**
* Wrap a single string in keyword identifiers.
*
* @param string $value
* @return string
*/
protected function wrapValue($value)
{
if ($value === '*') {
return $value;
}
// If the given value is a JSON selector we will wrap it differently than a
// traditional value. We will need to split this path and wrap each part
// wrapped, etc. Otherwise, we will simply wrap the value as a string.
if (Str::contains($value, '->')) {
return $this->wrapJsonSelector($value);
}
return '"'.str_replace('"', '""', $value).'"';
}
/**
* Wrap the given JSON selector.
*
* @param string $value
* @return string
*/
protected function wrapJsonSelector($value)
{
$path = explode('->', $value);
$field = $this->wrapValue(array_shift($path));
$wrappedPath = $this->wrapJsonPathAttributes($path);
$attribute = array_pop($wrappedPath);
if (! empty($wrappedPath)) {
return $field.'->'.implode('->', $wrappedPath).'->>'.$attribute;
}
return $field.'->>'.$attribute;
}
/**
* Wrap the attributes of the give JSON path.
*
* @param array $path
* @return array
*/
protected function wrapJsonPathAttributes($path)
{
return array_map(function ($attribute) {
return "'$attribute'";
}, $path);
}
}