lib/Query/SelectBase.php
<?php declare(strict_types=1);
/**
* ___ _ _
* | _ \__ _| |_ __ _| |__ __ _ ___ ___
* | _/ _` | _/ _` | '_ \/ _` (_-</ -_)
* |_| \__,_|\__\__,_|_.__/\__,_/__/\___|
*
* This file is part of Kristuff\Patabase.
* (c) Kristuff <kristuff@kristuff.fr>
*
* For the full copyright and license information, please view the LICENSE
* file that was distributed with this source code.
*
* @version 1.0.1
* @copyright 2017-2022 Christophe Buliard
*/
namespace Kristuff\Patabase\Query;
use Kristuff\Patabase\Driver\DatabaseDriver;
use Kristuff\Patabase\Query;
use Kristuff\Patabase\Query\QueryBuilder;
/**
* Class SelectBase
*
* Abstract base class for Select
*/
abstract class SelectBase extends QueryBuilder
{
/**
* Use DISTINCT or not?
*
* @access protected
* @var boolean
*/
protected $distinct = false;
/**
* Columns list for SELECT query
*
* @access protected
* @var array
*/
protected $columns = array();
/**
* Table source for SELECT query
*
* @access protected
* @var string
*/
protected $fromTable = '';
/**
* SQL JOINS internal list
*
* @access protected
* @var array
*/
protected $joins = array();
/**
* SQL GROUP BY internal list
*
* @access protected
* @var array
*/
protected $groupBy = array();
/**
* SQL ORDER BY internal list
*
* @access protected
* @var array
*/
protected $orderBy = array();
/**
* Limit for the SELECT query
*
* @access protected
* @var int
*/
protected $limit = 0;
/**
* Offset for the SELECT query
*
* @access protected
* @var int
*/
protected $offset = 0;
/**
* The top QueryBuilder instance, in case of subquery
*
* @access protected
* @var QueryBuilder
*/
protected $topQuery = null;
/**
* Constructor
*
* @access public
* @param DatabaseDriver $driver The driver instance
* @param Query $query The top query parent in case of subquery. Default is NULL
* @param array $args Columns arguments. Default is empty array
*/
public function __construct(DatabaseDriver $driver, $query = null, $args = array())
{
parent::__construct($driver);
$this->topQuery = $query;
// columns arguments
if (! empty($args)) {
$this->parseColumnsArguments($args);
}
}
/**
* Parse the columns arguments for the select query
*
* @access protected
* @param mixed $args The output columns argument
*
* @return void
*/
protected function parseColumnsArguments(array $args)
{
// args could be list of name, or one argument indexed array name => alias
$cols = (count($args) === 1 && is_array($args[0])) ? $args[0] : $args;
// parse column
foreach ($cols as $key => $value){
// Each arg could be a non indexed array of name, or
// an indexed array name => alias
$column = !is_int($key) ? $key : $value;
$columnValue = !is_int($key) ? $value : null;
$this->column($column, $columnValue);
}
}
/**
* Define the used of DISTINCT keyword
*
* @access public
* @return $this
*/
public function distinct()
{
$this->distinct = true;
return $this;
}
/**
* Add an output column for the select
*
* @access public
* @param string $column The column name, could be Table.ColumnName format
* @param string $alias The alias for this column
*
* @return $this
*/
public function column(string $column, ?string $alias = null)
{
$this->columns[] = array(
'type' => 'column',
'name' => $column,
'alias' => $alias);
return $this;
}
/**
* Define the outputs columns for the select
*
* @access public
* @return $this
*/
public function columns()
{
// args could be list of name, of one argument indexed array name => alias
$args = func_get_args();
$this->parseColumnsArguments($args);
return $this;
}
/**
* Add a COUNT(*) column for the select
*
* @access public
* @param string $alias The alias for this column
*
* @return $this
*/
public function count(string $alias)
{
$this->columns[] = array(
'type' => 'count',
'alias' => $alias
);
return $this;
}
/**
* Add a SUM(column) for the select
*
* @access public
* @param string $column The column to sum
* @param string $alias The alias for this column
* @return $this
*/
public function sum(string $column, string $alias)
{
$this->columns[] = array(
'type' => 'sum',
'name' => $column,
'alias' => $alias
);
return $this;
}
/**
* Add a MIN(column) for the select
*
* @access public
* @param string $column The column to sum
* @param string $alias The alias for this column
* @return $this
*/
public function min(string $column, string $alias)
{
$this->columns[] = array(
'type' => 'min',
'name' => $column,
'alias' => $alias
);
return $this;
}
/**
* Add a MAX(column) for the select
*
* @access public
* @param string $column The column to sum
* @param string $alias The alias for this column
* @return $this
*/
public function max(string $column, string $alias)
{
$this->columns[] = array(
'type' => 'max',
'name' => $column,
'alias' => $alias
);
return $this;
}
/**
* Create and returns a new sub Select instance
*
* @access public
* @param string $alias The alias for this query
*
* @return Query\Select
*/
public function select(string $alias)
{
$query = new Select($this->driver, $this);
$this->columns[] = array(
'type' => 'sub_query',
'query' => $query,
'alias' => $alias,
);
return $query;
}
/**
* Define the FROM tableName
*
* @access public
* @param string $tableName The table name
*
* @return $this
*/
public function from(string $tableName)
{
$this->fromTable = $tableName;
return $this;
}
/**
* Left join
*
* @access public
* @param string $externalTable Join table
* @param string $externalColumn Foreign key on the join table
* @param string $localTable Local table
* @param string $localColumn Local column
*
* @return $this
*/
public function leftJoin(string $externalTable, string $externalColumn, string $localTable, string $localColumn)
{
$this->joins[] = sprintf(
'LEFT OUTER JOIN %s ON %s=%s',
$this->driver->escape($externalTable),
$this->driver->escape($localTable).'.'.$this->driver->escape($localColumn),
$this->driver->escape($externalTable).'.'.$this->driver->escape($externalColumn)
);
return $this;
}
/**
* Right join
*
* @access public
* @param string $externalTable Join table
* @param string $externalColumn Foreign key on the join table
* @param string $localTable Local table
* @param string $localColumn Local column
*
* @return $this
*/
public function rightJoin(string $externalTable, string $externalColumn, string $localTable, string $localColumn)
{
$this->joins[] = sprintf(
'RIGHT OUTER JOIN %s ON %s=%s',
$this->driver->escape($externalTable),
$this->driver->escape($localTable).'.'.$this->driver->escape($localColumn),
$this->driver->escape($externalTable).'.'.$this->driver->escape($externalColumn)
);
return $this;
}
/**
* Full join
*
* @access public
* @param string $externalTable Join table
* @param string $externalColumn Foreign key on the join table
* @param string $localTable Local table
* @param string $localColumn Local column
*
* @return $this
*/
public function fullJoin(string $externalTable, string $externalColumn, string $localTable, string $localColumn)
{
$this->joins[] = sprintf(
'FULL OUTER JOIN %s ON %s=%s',
$this->driver->escape($externalTable),
$this->driver->escape($localTable).'.'.$this->driver->escape($localColumn),
$this->driver->escape($externalTable).'.'.$this->driver->escape($externalColumn)
);
return $this;
}
/**
* Inner join
*
* @access public
* @param string $externalTable Join table
* @param string $externalColumn Foreign key on the join table
* @param string $localTable Local table
* @param string $localColumn Local column
*
* @return $this
*/
public function innerJoin(string $externalTable, string $externalColumn, string $localTable, string $localColumn)
{
$this->joins[] = sprintf(
'INNER JOIN %s ON %s=%s',
$this->driver->escape($externalTable),
$this->driver->escape($localTable).'.'.$this->driver->escape($localColumn),
$this->driver->escape($externalTable).'.'.$this->driver->escape($externalColumn)
);
return $this;
}
/**
* join (alias for innerJoin)
*
* @access public
* @param string $externalTable Join table
* @param string $externalColumn Foreign key on the join table
* @param string $localTable Local table
* @param string $localColumn Local column
*
* @return $this
*/
public function join(string $externalTable, string $externalColumn, string $localTable, string $localColumn)
{
return $this->innerJoin($externalTable, $externalColumn, $localTable, $localColumn);
}
/**
* Get a WHERE statement object
*
* @access public
* @return Where
*/
public function where(): Where
{
if (!isset($this->where)){
$this->where = new Query\Where($this, $this->driver, $this->topQuery);
}
return $this->where;
}
/**
* Add a WHERE column = value condition
* It's an alias for ->where()->equal($column, $value)
*
* @access public
* @param string $column The column name
* @param mixed $value The condition value
*
* @return $this
*/
public function whereEqual(string $column, $value)
{
$this->where()->equal($column, $value);
return $this;
}
/**
* Get an HAVING statement object
*
* @access public
* @return Having
*/
public function having(): Having
{
if (!isset($this->having)){
$this->having = new Query\Having($this, $this->driver, $this->topQuery);
}
return $this->having;
}
/**
* Define the GROUP BY
*
* @access public
* @param mixed
* @return $this
*/
public function groupBy()
{
$this->groupBy = func_get_args();
return $this;
}
/**
* Add an ORDER BY statement
*
* @access public
* @param string $column Column name
* @param string $order Direction ASC or DESC or custom function
*
* @return $this
*/
public function orderBy($column, $order = self::SORT_ASC)
{
$this->orderBy[] = array(
'column' => $column,
'order' => $order
);
return $this;
}
/**
* Add an ORDER BY [X] ASC statement
*
* @access public
* @param string $column The column name
* @return $this
*/
public function orderAsc($column)
{
$this->orderBy($column, self::SORT_ASC);
return $this;
}
/**
* Add an ORDER BY [X] DESC statement
*
* @access public
* @param string $column The column name
* @return $this
*/
public function orderDesc($column)
{
$this->orderBy($column, self::SORT_DESC);
return $this;
}
/**
* Add an ORDER BY *random function* statement
*
* @access public
* @param int $seed (optional) The random seed.
*
* @return $this
*/
public function orderRand($seed = null)
{
$this->orderBy(NULL, $this->driver->sqlRandom($seed));
return $this;
}
/**
* Define the query LIMIT
*
* @access public
* @param int $value The limit value
*
* @return $this
*/
public function limit($value)
{
if (! is_null($value)) {
$this->limit = (int) $value;
}
return $this;
}
/**
* Define the query OFFSET
*
* @access public
* @param int $value The offset value
*
* @return $this
*/
public function offset($value)
{
if (! is_null($value)) {
$this->offset = (int) $value;
}
return $this;
}
}