Source of file Select.php
Size: 11,773 Bytes - Last Modified: 2017-11-08T13:54:24+00:00
/home/travis/build/bluzphp/framework/src/Db/Query/Select.php
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430 | <?php /** * Bluz Framework Component * * @copyright Bluz PHP Team * @link https://github.com/bluzphp/framework */ declare(strict_types=1); namespace Bluz\Db\Query; use Bluz\Db\Exception\DbException; use Bluz\Proxy\Db; /** * Builder of SELECT queries * * @package Bluz\Db\Query */ class Select extends AbstractBuilder { use Traits\From; use Traits\Where; use Traits\Order; use Traits\Limit; /** * @var mixed PDO fetch types or object class */ protected $fetchType = \PDO::FETCH_ASSOC; /** * {@inheritdoc} * * @param integer|string|object $fetchType * * @return integer|string|array */ public function execute($fetchType = null) { if (!$fetchType) { $fetchType = $this->fetchType; } switch ($fetchType) { case (!is_int($fetchType)): return Db::fetchObjects($this->getSql(), $this->params, $fetchType); case \PDO::FETCH_CLASS: return Db::fetchObjects($this->getSql(), $this->params); case \PDO::FETCH_ASSOC: default: return Db::fetchAll($this->getSql(), $this->params); } } /** * Setup fetch type, any of PDO, or any Class * * @param string $fetchType * * @return Select instance */ public function setFetchType($fetchType) { $this->fetchType = $fetchType; return $this; } /** * {@inheritdoc} * * @return string */ public function getSql() : string { $query = "SELECT " . implode(', ', $this->sqlParts['select']) . " FROM "; $fromClauses = []; // Loop through all FROM clauses foreach ($this->sqlParts['from'] as $from) { $fromClause = $from['table'] . ' ' . $from['alias'] . $this->getSQLForJoins($from['alias']); $fromClauses[$from['alias']] = $fromClause; } $query .= implode(', ', $fromClauses) . ($this->sqlParts['where'] !== null ? " WHERE " . ((string)$this->sqlParts['where']) : "") . ($this->sqlParts['groupBy'] ? " GROUP BY " . implode(", ", $this->sqlParts['groupBy']) : "") . ($this->sqlParts['having'] !== null ? " HAVING " . ((string)$this->sqlParts['having']) : "") . ($this->sqlParts['orderBy'] ? " ORDER BY " . implode(", ", $this->sqlParts['orderBy']) : "") . ($this->limit ? " LIMIT " . $this->limit . " OFFSET " . $this->offset : ""); return $query; } /** * Specifies an item that is to be returned in the query result * Replaces any previously specified selections, if any * * Example * <code> * $sb = new Select(); * $sb * ->select('u.id', 'p.id') * ->from('users', 'u') * ->leftJoin('u', 'phone', 'p', 'u.id = p.user_id'); * </code> * * @param string[] $select the selection expressions * * @return Select instance */ public function select(...$select) { return $this->addQueryPart('select', $select, false); } /** * Adds an item that is to be returned in the query result. * * Example * <code> * $sb = new Select(); * $sb * ->select('u.id') * ->addSelect('p.id') * ->from('users', 'u') * ->leftJoin('u', 'phone', 'u.id = p.user_id'); * </code> * * @param string $select the selection expression * * @return Select instance */ public function addSelect($select) { return $this->addQueryPart('select', $select, true); } /** * Creates and adds a join to the query * * Example * <code> * $sb = new Select(); * $sb * ->select('u.name') * ->from('users', 'u') * ->join('u', 'phone', 'p', 'p.is_primary = 1'); * </code> * * @param string $fromAlias the alias that points to a from clause * @param string $join the table name to join * @param string $alias the alias of the join table * @param string $condition the condition for the join * * @return Select instance */ public function join($fromAlias, $join, $alias, $condition = null) { return $this->innerJoin($fromAlias, $join, $alias, $condition); } /** * Creates and adds a join to the query * * Example * <code> * $sb = new Select(); * $sb * ->select('u.name') * ->from('users', 'u') * ->innerJoin('u', 'phone', 'p', 'p.is_primary = 1'); * </code> * * @param string $fromAlias the alias that points to a from clause * @param string $join the table name to join * @param string $alias the alias of the join table * @param string $condition the condition for the join * * @return Select instance */ public function innerJoin($fromAlias, $join, $alias, $condition = null) { $this->aliases[] = $alias; return $this->addQueryPart( 'join', [ $fromAlias => [ 'joinType' => 'inner', 'joinTable' => $join, 'joinAlias' => $alias, 'joinCondition' => $condition ] ], true ); } /** * Creates and adds a left join to the query. * * Example * <code> * $sb = new Select(); * $sb * ->select('u.name') * ->from('users', 'u') * ->leftJoin('u', 'phone', 'p', 'p.is_primary = 1'); * </code> * * @param string $fromAlias the alias that points to a from clause * @param string $join the table name to join * @param string $alias the alias of the join table * @param string $condition the condition for the join * * @return Select instance */ public function leftJoin($fromAlias, $join, $alias, $condition = null) { $this->aliases[] = $alias; return $this->addQueryPart( 'join', [ $fromAlias => [ 'joinType' => 'left', 'joinTable' => $join, 'joinAlias' => $alias, 'joinCondition' => $condition ] ], true ); } /** * Creates and adds a right join to the query. * * Example * <code> * $sb = new Select(); * $sb * ->select('u.name') * ->from('users', 'u') * ->rightJoin('u', 'phone', 'p', 'p.is_primary = 1'); * </code> * * @param string $fromAlias the alias that points to a from clause * @param string $join the table name to join * @param string $alias the alias of the join table * @param string $condition the condition for the join * * @return Select instance */ public function rightJoin($fromAlias, $join, $alias, $condition = null) { $this->aliases[] = $alias; return $this->addQueryPart( 'join', [ $fromAlias => [ 'joinType' => 'right', 'joinTable' => $join, 'joinAlias' => $alias, 'joinCondition' => $condition ] ], true ); } /** * Specifies a grouping over the results of the query. * Replaces any previously specified groupings, if any. * * Example * <code> * $sb = new Select(); * $sb * ->select('u.name') * ->from('users', 'u') * ->groupBy('u.id'); * </code> * * @param string[] $groupBy the grouping expression * * @return Select instance */ public function groupBy(...$groupBy) { if (empty($groupBy)) { return $this; } return $this->addQueryPart('groupBy', $groupBy, false); } /** * Adds a grouping expression to the query. * * Example * <code> * $sb = new Select(); * $sb * ->select('u.name') * ->from('users', 'u') * ->groupBy('u.lastLogin'); * ->addGroupBy('u.createdAt') * </code> * * @param string[] $groupBy the grouping expression * * @return Select instance */ public function addGroupBy(...$groupBy) { if (empty($groupBy)) { return $this; } return $this->addQueryPart('groupBy', $groupBy, true); } /** * Specifies a restriction over the groups of the query. * Replaces any previous having restrictions, if any * * @param string[] $condition the query restriction predicates * * @return Select */ public function having(...$condition) { $condition = $this->prepareCondition($condition); return $this->addQueryPart('having', $condition, false); } /** * Adds a restriction over the groups of the query, forming a logical * conjunction with any existing having restrictions * * @param string[] $condition the query restriction predicates * * @return Select */ public function andHaving(...$condition) { $condition = $this->prepareCondition($condition); $having = $this->getQueryPart('having'); if ($having instanceof CompositeBuilder && $having->getType() == 'AND') { $having->add($condition); } else { $having = new CompositeBuilder([$having, $condition]); } return $this->addQueryPart('having', $having, false); } /** * Adds a restriction over the groups of the query, forming a logical * disjunction with any existing having restrictions * * @param string[] $condition the query restriction predicates * * @return Select */ public function orHaving(...$condition) { $condition = $this->prepareCondition($condition); $having = $this->getQueryPart('having'); if ($having instanceof CompositeBuilder && $having->getType() == 'OR') { $having->add($condition); } else { $having = new CompositeBuilder([$having, $condition], 'OR'); } return $this->addQueryPart('having', $having, false); } /** * Setup offset like a page number, start from 1 * * @param integer $page * * @return Select * @throws DbException */ public function setPage($page = 1) { if (!$this->limit) { throw new DbException("Please setup limit for use method `setPage`"); } $this->offset = $this->limit * ($page - 1); return $this; } /** * Generate SQL string for JOINs * * @internal * * @param string $fromAlias alias of table * * @return string */ protected function getSQLForJoins($fromAlias) { $sql = ''; if (isset($this->sqlParts['join'][$fromAlias])) { foreach ($this->sqlParts['join'][$fromAlias] as $join) { $sql .= ' ' . strtoupper($join['joinType']) . " JOIN " . $join['joinTable'] . ' ' . $join['joinAlias'] . " ON " . ((string)$join['joinCondition']); $sql .= $this->getSQLForJoins($join['joinAlias']); } } return $sql; } } |