Magento 2 Documentation  2.3
Documentation for Magento 2 CMS v2.3 (December 2018)
Public Member Functions | Data Fields | Protected Member Functions
Select Class Reference
Inheritance diagram for Select:
Zend_Db_Select

Public Member Functions

 __construct (\Magento\Framework\DB\Adapter\Pdo\Mysql $adapter, \Magento\Framework\DB\Select\SelectRenderer $selectRenderer, $parts=[])
 
 where ($cond, $value=null, $type=null)
 
 resetJoinLeft ()
 
 limit ($count=null, $offset=null)
 
 crossUpdateFromSelect ($table)
 
 insertFromSelect ($tableName, $fields=[], $onDuplicate=true)
 
 insertIgnoreFromSelect ($tableName, $fields=[])
 
 deleteFromSelect ($table)
 
 setPart ($part, $value)
 
 useStraightJoin ($flag=true)
 
 orderRand ($field=null)
 
 exists ($select, $joinCondition, $isExists=true)
 
 getConnection ()
 
 assemble ()
 
 __sleep ()
 
 __wakeup ()
 
- Public Member Functions inherited from Zend_Db_Select
 __construct (Zend_Db_Adapter_Abstract $adapter)
 
 getBind ()
 
 bind ($bind)
 
 distinct ($flag=true)
 
 from ($name, $cols=' *', $schema=null)
 
 columns ($cols=' *', $correlationName=null)
 
 union ($select=array(), $type=self::SQL_UNION)
 
 join ($name, $cond, $cols=self::SQL_WILDCARD, $schema=null)
 
 joinInner ($name, $cond, $cols=self::SQL_WILDCARD, $schema=null)
 
 joinLeft ($name, $cond, $cols=self::SQL_WILDCARD, $schema=null)
 
 joinRight ($name, $cond, $cols=self::SQL_WILDCARD, $schema=null)
 
 joinFull ($name, $cond, $cols=self::SQL_WILDCARD, $schema=null)
 
 joinCross ($name, $cols=self::SQL_WILDCARD, $schema=null)
 
 joinNatural ($name, $cols=self::SQL_WILDCARD, $schema=null)
 
 where ($cond, $value=null, $type=null)
 
 orWhere ($cond, $value=null, $type=null)
 
 group ($spec)
 
 having ($cond, $value=null, $type=null)
 
 orHaving ($cond, $value=null, $type=null)
 
 order ($spec)
 
 limit ($count=null, $offset=null)
 
 limitPage ($page, $rowCount)
 
 forUpdate ($flag=true)
 
 getPart ($part)
 
 query ($fetchMode=null, $bind=array())
 
 assemble ()
 
 reset ($part=null)
 
 getAdapter ()
 
 _joinUsing ($type, $name, $cond, $cols=' *', $schema=null)
 
 __call ($method, array $args)
 
 __toString ()
 

Data Fields

const TYPE_CONDITION = 'TYPE_CONDITION'
 
const STRAIGHT_JOIN = 'straightjoin'
 
const SQL_STRAIGHT_JOIN = 'STRAIGHT_JOIN'
 
- Data Fields inherited from Zend_Db_Select
const DISTINCT = 'distinct'
 
const COLUMNS = 'columns'
 
const FROM = 'from'
 
const UNION = 'union'
 
const WHERE = 'where'
 
const GROUP = 'group'
 
const HAVING = 'having'
 
const ORDER = 'order'
 
const LIMIT_COUNT = 'limitcount'
 
const LIMIT_OFFSET = 'limitoffset'
 
const FOR_UPDATE = 'forupdate'
 
const INNER_JOIN = 'inner join'
 
const LEFT_JOIN = 'left join'
 
const RIGHT_JOIN = 'right join'
 
const FULL_JOIN = 'full join'
 
const CROSS_JOIN = 'cross join'
 
const NATURAL_JOIN = 'natural join'
 
const SQL_WILDCARD = '*'
 
const SQL_SELECT = 'SELECT'
 
const SQL_UNION = 'UNION'
 
const SQL_UNION_ALL = 'UNION ALL'
 
const SQL_FROM = 'FROM'
 
const SQL_WHERE = 'WHERE'
 
const SQL_DISTINCT = 'DISTINCT'
 
const SQL_GROUP_BY = 'GROUP BY'
 
const SQL_ORDER_BY = 'ORDER BY'
 
const SQL_HAVING = 'HAVING'
 
const SQL_FOR_UPDATE = 'FOR UPDATE'
 
const SQL_AND = 'AND'
 
const SQL_AS = 'AS'
 
const SQL_OR = 'OR'
 
const SQL_ON = 'ON'
 
const SQL_ASC = 'ASC'
 
const SQL_DESC = 'DESC'
 
const REGEX_COLUMN_EXPR = '/^([\w]*\s*\(([^\(\)]|(?1))*\))$/'
 
const REGEX_COLUMN_EXPR_ORDER = '/^([\w]+\s*\(([^\(\)]|(?1))*\))$/'
 
const REGEX_COLUMN_EXPR_GROUP = '/^([\w]+\s*\(([^\(\)]|(?1))*\))$/'
 
const REGEX_SQL_COMMENTS
 

Protected Member Functions

 _resetJoinLeft ()
 
 _findTableInCond ($table, $cond)
 
 _join ($type, $name, $cond, $cols, $schema=null)
 
 _renderStraightjoin ($sql)
 
 _tableCols ($correlationName, $cols, $afterCorrelationName=null)
 
 _renderForupdate ($sql)
 
- Protected Member Functions inherited from Zend_Db_Select
 _join ($type, $name, $cond, $cols, $schema=null)
 
 _tableCols ($correlationName, $cols, $afterCorrelationName=null)
 
 _where ($condition, $value=null, $type=null, $bool=true)
 
 _getDummyTable ()
 
 _getQuotedSchema ($schema=null)
 
 _getQuotedTable ($tableName, $correlationName=null)
 
 _renderDistinct ($sql)
 
 _renderColumns ($sql)
 
 _renderFrom ($sql)
 
 _renderUnion ($sql)
 
 _renderWhere ($sql)
 
 _renderGroup ($sql)
 
 _renderHaving ($sql)
 
 _renderOrder ($sql)
 
 _renderLimitoffset ($sql)
 
 _renderForupdate ($sql)
 

Additional Inherited Members

- Protected Attributes inherited from Zend_Db_Select
 $_bind = array()
 
 $_adapter
 
 $_parts = array()
 
 $_tableCols = array()
 
- Static Protected Attributes inherited from Zend_Db_Select
static $_partsInit
 
static $_joinTypes
 
static $_unionTypes
 

Detailed Description

Class for SQL SELECT generation and results.

@api @method \Magento\Framework\DB\Select from($name, $cols = '*', $schema = null) @method \Magento\Framework\DB\Select join($name, $cond, $cols = '*', $schema = null) @method \Magento\Framework\DB\Select joinInner($name, $cond, $cols = '*', $schema = null) @method \Magento\Framework\DB\Select joinLeft($name, $cond, $cols = '*', $schema = null) @method \Magento\Framework\DB\Select joinNatural($name, $cond, $cols = '*', $schema = null) @method \Magento\Framework\DB\Select joinFull($name, $cond, $cols = '*', $schema = null) @method \Magento\Framework\DB\Select joinRight($name, $cond, $cols = '*', $schema = null) @method \Magento\Framework\DB\Select joinCross($name, $cols = '*', $schema = null) @method \Magento\Framework\DB\Select orWhere($cond, $value = null, $type = null) @method \Magento\Framework\DB\Select group($spec) @method \Magento\Framework\DB\Select order($spec) @method \Magento\Framework\DB\Select limitPage($page, $rowCount) @method \Magento\Framework\DB\Select forUpdate($flag = true) @method \Magento\Framework\DB\Select distinct($flag = true) @method \Magento\Framework\DB\Select reset($part = null) @method \Magento\Framework\DB\Select columns($cols = '*', $correlationName = null)

Since
100.0.2

Definition at line 33 of file Select.php.

Constructor & Destructor Documentation

◆ __construct()

__construct ( \Magento\Framework\DB\Adapter\Pdo\Mysql  $adapter,
\Magento\Framework\DB\Select\SelectRenderer  $selectRenderer,
  $parts = [] 
)

Class constructor Add straight join support

Parameters
Adapter\Pdo\Mysql$adapter
Select\SelectRenderer$selectRenderer
array$parts

Definition at line 63 of file Select.php.

67  {
68  self::$_partsInit = array_merge(self::$_partsInit, $parts);
69  if (!isset(self::$_partsInit[self::STRAIGHT_JOIN])) {
70  self::$_partsInit = [self::STRAIGHT_JOIN => false] + self::$_partsInit;
71  }
72 
73  $this->selectRenderer = $selectRenderer;
74  parent::__construct($adapter);
75  }
static $_partsInit
Definition: Select.php:126
$adapter
Definition: webapi_user.php:16

Member Function Documentation

◆ __sleep()

__sleep ( )
Returns
string[]
Since
100.0.11

Definition at line 516 of file Select.php.

517  {
518  $properties = array_keys(get_object_vars($this));
519  $properties = array_diff(
520  $properties,
521  [
522  '_adapter',
523  'selectRenderer'
524  ]
525  );
526  return $properties;
527  }
$properties
Definition: categories.php:26

◆ __wakeup()

__wakeup ( )

Init not serializable fields

Returns
void
Since
100.0.11

Definition at line 535 of file Select.php.

536  {
538  $this->_adapter = $objectManager->get(ResourceConnection::class)->getConnection();
539  $this->selectRenderer = $objectManager->get(\Magento\Framework\DB\Select\SelectRenderer::class);
540  }
$objectManager
Definition: bootstrap.php:17

◆ _findTableInCond()

_findTableInCond (   $table,
  $cond 
)
protected

Find table name in condition (where, column)

Parameters
string$table
string$cond
Returns
bool

Definition at line 241 of file Select.php.

242  {
243  $quote = $this->_adapter->getQuoteIdentifierSymbol();
244 
245  if (strpos($cond, $quote . $table . $quote . '.') !== false) {
246  return true;
247  }
248 
249  $position = 0;
250  $result = 0;
251  $needle = [];
252  while (is_integer($result)) {
253  $result = strpos($cond, $table . '.', $position);
254 
255  if (is_integer($result)) {
256  $needle[] = $result;
257  $position = $result + strlen($table) + 1;
258  }
259  }
260 
261  if (!$needle) {
262  return false;
263  }
264 
265  foreach ($needle as $position) {
266  if ($position == 0) {
267  return true;
268  }
269  if (!preg_match('#[a-z0-9_]#is', substr($cond, $position - 1, 1))) {
270  return true;
271  }
272  }
273 
274  return false;
275  }
$quote
$table
Definition: trigger.php:14

◆ _join()

_join (   $type,
  $name,
  $cond,
  $cols,
  $schema = null 
)
protected

Populate the $_parts 'join' key

Does the dirty work of populating the join key.

The $name and $cols parameters follow the same logic as described in the from() method.

Parameters
null | string$typeType of join; inner, left, and null are currently supported
array | string | \Zend_Db_Expr$nameTable name
string$condJoin on this condition
array | string$colsThe columns to select from the joined table
string$schemaThe database name to specify, if any.
Returns
\Magento\Framework\DB\Select This \Magento\Framework\DB\Select object
Exceptions

Definition at line 293 of file Select.php.

294  {
295  if ($type == self::INNER_JOIN && empty($cond)) {
297  }
298  return parent::_join($type, $name, $cond, $cols, $schema);
299  }
$type
Definition: item.phtml:13
const CROSS_JOIN
Definition: Select.php:63
if(!isset($_GET['name'])) $name
Definition: log.php:14

◆ _renderForupdate()

_renderForupdate (   $sql)
protected

Render FOR UPDATE clause

Parameters
string$sqlSQL query
Returns
string

Definition at line 459 of file Select.php.

460  {
461  if ($this->_parts[self::FOR_UPDATE]) {
462  $sql = $this->_adapter->forUpdate($sql);
463  }
464 
465  return $sql;
466  }

◆ _renderStraightjoin()

_renderStraightjoin (   $sql)
protected

Render STRAIGHT_JOIN clause

Parameters
string$sqlSQL query
Returns
string

Definition at line 407 of file Select.php.

408  {
409  if ($this->_adapter->supportStraightJoin() && !empty($this->_parts[self::STRAIGHT_JOIN])) {
410  $sql .= ' ' . self::SQL_STRAIGHT_JOIN;
411  }
412 
413  return $sql;
414  }

◆ _resetJoinLeft()

_resetJoinLeft ( )
protected

Validate LEFT joins, and remove it if not exists

Returns
$this

Definition at line 208 of file Select.php.

209  {
210  foreach ($this->_parts[self::FROM] as $tableId => $tableProp) {
211  if ($tableProp['joinType'] == self::LEFT_JOIN) {
212  if ($tableProp['useInCond']) {
213  continue;
214  }
215 
216  $used = false;
217  foreach ($tableProp['joinInTables'] as $table) {
218  if (isset($this->_parts[self::FROM][$table])) {
219  $used = true;
220  break;
221  }
222  }
223 
224  if (!$used) {
225  unset($this->_parts[self::FROM][$tableId]);
226  return $this->_resetJoinLeft();
227  }
228  }
229  }
230 
231  return $this;
232  }
$table
Definition: trigger.php:14

◆ _tableCols()

_tableCols (   $correlationName,
  $cols,
  $afterCorrelationName = null 
)
protected

Adds to the internal table-to-column mapping array.

Parameters
string$correlationNameThe table/join the columns come from.
array | string$colsThe list of columns; preferably as an array, but possibly as a string containing one column.
bool | string$afterCorrelationNameTrue if it should be prepended, a correlation name if it should be inserted
Returns
void

Definition at line 426 of file Select.php.

427  {
428  if (!is_array($cols)) {
429  $cols = [$cols];
430  }
431 
432  foreach ($cols as $k => $v) {
433  if ($v instanceof Select) {
434  $cols[$k] = new \Zend_Db_Expr(sprintf('(%s)', $v->assemble()));
435  }
436  }
437 
438  return parent::_tableCols($correlationName, $cols, $afterCorrelationName);
439  }

◆ assemble()

assemble ( )

Converts this object to an SQL SELECT string.

Returns
string|null This object as a SELECT string. (or null if a string cannot be produced.)
Since
100.1.0

Definition at line 507 of file Select.php.

508  {
509  return $this->selectRenderer->render($this);
510  }

◆ crossUpdateFromSelect()

crossUpdateFromSelect (   $table)

Cross Table Update From Current select

Parameters
string | array$table
Returns
string

Definition at line 329 of file Select.php.

330  {
331  return $this->getConnection()->updateFromSelect($this, $table);
332  }
$table
Definition: trigger.php:14

◆ deleteFromSelect()

deleteFromSelect (   $table)

Retrieve DELETE query from select

Parameters
string$tableThe table name or alias
Returns
string

Definition at line 366 of file Select.php.

367  {
368  return $this->getConnection()->deleteFromSelect($this, $table);
369  }
$table
Definition: trigger.php:14

◆ exists()

exists (   $select,
  $joinCondition,
  $isExists = true 
)

Add EXISTS clause

Parameters
Select$select
string$joinCondition
bool$isExists
Returns
$this

Definition at line 476 of file Select.php.

477  {
478  if ($isExists) {
479  $exists = 'EXISTS (%s)';
480  } else {
481  $exists = 'NOT EXISTS (%s)';
482  }
483  $select->reset(self::COLUMNS)->columns([new \Zend_Db_Expr('1')])->where($joinCondition);
484 
485  $exists = sprintf($exists, $select->assemble());
486 
487  $this->where($exists);
488  return $this;
489  }
where($cond, $value=null, $type=null)
Definition: Select.php:109

◆ getConnection()

getConnection ( )

Get adapter

Returns
\Magento\Framework\DB\Adapter\AdapterInterface

Definition at line 496 of file Select.php.

497  {
498  return $this->_adapter;
499  }

◆ insertFromSelect()

insertFromSelect (   $tableName,
  $fields = [],
  $onDuplicate = true 
)

Insert to table from current select

Parameters
string$tableName
array$fields
bool$onDuplicate
Returns
string

Definition at line 342 of file Select.php.

343  {
344  $mode = $onDuplicate ? AdapterInterface::INSERT_ON_DUPLICATE : false;
345  return $this->getConnection()->insertFromSelect($this, $tableName, $fields, $mode);
346  }
$tableName
Definition: trigger.php:13
$fields
Definition: details.phtml:14
if($exist=($block->getProductCollection() && $block->getProductCollection() ->getSize())) $mode
Definition: grid.phtml:15

◆ insertIgnoreFromSelect()

insertIgnoreFromSelect (   $tableName,
  $fields = [] 
)

Generate INSERT IGNORE query to the table from current select

Parameters
string$tableName
array$fields
Returns
string

Definition at line 355 of file Select.php.

356  {
357  return $this->getConnection()->insertFromSelect($this, $tableName, $fields, AdapterInterface::INSERT_IGNORE);
358  }
$tableName
Definition: trigger.php:13
$fields
Definition: details.phtml:14

◆ limit()

limit (   $count = null,
  $offset = null 
)

Sets a limit count and offset to the query.

Parameters
int$countOPTIONAL The number of rows to return.
int$offsetOPTIONAL Start returning after this many rows.
Returns
$this

Definition at line 308 of file Select.php.

309  {
310  if ($count === null) {
311  $this->reset(self::LIMIT_COUNT);
312  } else {
313  $this->_parts[self::LIMIT_COUNT] = (int)$count;
314  }
315  if ($offset === null) {
316  $this->reset(self::LIMIT_OFFSET);
317  } else {
318  $this->_parts[self::LIMIT_OFFSET] = (int)$offset;
319  }
320  return $this;
321  }
$count
Definition: recent.phtml:13
reset($part=null)
Definition: Select.php:742
const LIMIT_OFFSET
Definition: Select.php:56
const LIMIT_COUNT
Definition: Select.php:55

◆ orderRand()

orderRand (   $field = null)

Adds the random order to query

Parameters
string$fieldinteger field name
Returns
$this

Definition at line 447 of file Select.php.

448  {
449  $this->_adapter->orderRand($this, $field);
450  return $this;
451  }

◆ resetJoinLeft()

resetJoinLeft ( )

Reset unused LEFT JOIN(s)

Returns
$this @SuppressWarnings(PHPMD.CyclomaticComplexity) @SuppressWarnings(PHPMD.NPathComplexity)

Definition at line 130 of file Select.php.

131  {
132  foreach ($this->_parts[self::FROM] as $tableId => $tableProp) {
133  if ($tableProp['joinType'] == self::LEFT_JOIN) {
134  $useJoin = false;
135  foreach ($this->_parts[self::COLUMNS] as $columnEntry) {
136  list($correlationName, $column) = $columnEntry;
137  if ($column instanceof \Zend_Db_Expr) {
138  if ($this->_findTableInCond(
139  $tableId,
140  $column
141  ) || $this->_findTableInCond(
142  $tableProp['tableName'],
143  $column
144  )
145  ) {
146  $useJoin = true;
147  }
148  } else {
149  if ($correlationName == $tableId) {
150  $useJoin = true;
151  }
152  }
153  }
154  foreach ($this->_parts[self::WHERE] as $where) {
155  if ($this->_findTableInCond(
156  $tableId,
157  $where
158  ) || $this->_findTableInCond(
159  $tableProp['tableName'],
160  $where
161  )
162  ) {
163  $useJoin = true;
164  }
165  }
166 
167  $joinUseInCond = $useJoin;
168  $joinInTables = [];
169 
170  foreach ($this->_parts[self::FROM] as $tableCorrelationName => $table) {
171  if ($tableCorrelationName == $tableId) {
172  continue;
173  }
174  if (!empty($table['joinCondition'])) {
175  if ($this->_findTableInCond(
176  $tableId,
177  $table['joinCondition']
178  ) || $this->_findTableInCond(
179  $tableProp['tableName'],
180  $table['joinCondition']
181  )
182  ) {
183  $useJoin = true;
184  $joinInTables[] = $tableCorrelationName;
185  }
186  }
187  }
188 
189  if (!$useJoin) {
190  unset($this->_parts[self::FROM][$tableId]);
191  } else {
192  $this->_parts[self::FROM][$tableId]['useInCond'] = $joinUseInCond;
193  $this->_parts[self::FROM][$tableId]['joinInTables'] = $joinInTables;
194  }
195  }
196  }
197 
198  $this->_resetJoinLeft();
199 
200  return $this;
201  }
const FROM
Definition: Select.php:49
_findTableInCond($table, $cond)
Definition: Select.php:241
$table
Definition: trigger.php:14

◆ setPart()

setPart (   $part,
  $value 
)

Modify (hack) part of the structured information for the current query

Parameters
string$part
mixed$value
Returns
$this
Exceptions

Definition at line 379 of file Select.php.

380  {
381  $part = strtolower($part);
382  if (!array_key_exists($part, $this->_parts)) {
383  throw new \Zend_Db_Select_Exception("Invalid Select part '{$part}'");
384  }
385  $this->_parts[$part] = $value;
386  return $this;
387  }
$value
Definition: gender.phtml:16

◆ useStraightJoin()

useStraightJoin (   $flag = true)

Use a STRAIGHT_JOIN for the SQL Select

Parameters
bool$flagWhether or not the SELECT use STRAIGHT_JOIN (default true).
Returns
$this

Definition at line 395 of file Select.php.

396  {
397  $this->_parts[self::STRAIGHT_JOIN] = (bool)$flag;
398  return $this;
399  }

◆ where()

where (   $cond,
  $value = null,
  $type = null 
)

Adds a WHERE condition to the query by AND.

If a value is passed as the second param, it will be quoted and replaced into the condition wherever a question-mark appears. Array values are quoted and comma-separated.

// simplest but non-secure $select->where("id = $id");

// secure (ID is quoted but matched anyway) $select->where('id = ?', $id);

// alternatively, with named binding $select->where('id = :id');

Note that it is more correct to use named bindings in your queries for values other than strings. When you use named bindings, don't forget to pass the values when actually making a query:

$db->fetchAll($select, array('id' => 5));

Parameters
string$condThe WHERE condition.
string$valueOPTIONAL A single value to quote into the condition.
string | int | null$typeOPTIONAL The type of the given value
Returns
\Magento\Framework\DB\Select

Definition at line 109 of file Select.php.

110  {
111  if ($value === null && $type === null) {
112  $value = '';
113  } elseif ($type == self::TYPE_CONDITION) {
114  $type = null;
115  }
116  if (is_array($value)) {
117  $cond = $this->getConnection()->quoteInto($cond, $value);
118  $value = null;
119  }
120  return parent::where($cond, $value, $type);
121  }
elseif(isset( $params[ 'redirect_parent']))
Definition: iframe.phtml:17
$type
Definition: item.phtml:13
$value
Definition: gender.phtml:16

Field Documentation

◆ SQL_STRAIGHT_JOIN

const SQL_STRAIGHT_JOIN = 'STRAIGHT_JOIN'

Sql straight join

Definition at line 48 of file Select.php.

◆ STRAIGHT_JOIN

const STRAIGHT_JOIN = 'straightjoin'

Straight join key

Definition at line 43 of file Select.php.

◆ TYPE_CONDITION

const TYPE_CONDITION = 'TYPE_CONDITION'

Condition type

Definition at line 38 of file Select.php.


The documentation for this class was generated from the following file: