SQL Rendering for the global search

@return string SQL Part Query
494438
public function globalFilter()
    {
        $globalSearch = [];
        if (isset($this->request['search']) && !empty($this->request['search']['value'])) {
            for ($i = 0, $ien = count($this->request['columns']); $i<$ien; $i++) {
                if (self::isSearchable($this->columns[$i])) {
                    $where_condition = $this->toSQLColumn($this->columns[$i], 2).' LIKE '.$this->pdoLink->quote('%'.$this->request['search']['value'].'%');
                    if (!$this->setHaving($where_condition, $this->columns[$i])) {
                        $globalSearch[] = $where_condition;
                    }
                }
            }
        }

        return empty($globalSearch) ? '' : '('.implode(' OR ', $globalSearch).')';
    }
1284363
public function get_action_sql() {
        global $DB;

        // In new logs we have a field to pick, and in legacy try get this from action.
        if ($this->filterparams->logreader instanceof logstore_legacy\log\store) {
            $action = $this->get_legacy_crud_action($this->filterparams->action);
            $firstletter = substr($action, 0, 1);
            if ($firstletter == '-') {
                $sql = $DB->sql_like('action', ':action', false, true, true);
                $params['action'] = '%'.substr($action, 1).'%';
            } else {
                $sql = $DB->sql_like('action', ':action', false);
                $params['action'] = '%'.$action.'%';
            }
        } else if (!empty($this->filterparams->action)) {
             list($sql, $params) = $DB->get_in_or_equal(str_split($this->filterparams->action),
                    SQL_PARAMS_NAMED, 'crud');
            $sql = "crud " . $sql;
        } else {
            // Add condition for all possible values of crud (to use db index).
            list($sql, $params) = $DB->get_in_or_equal(array('c', 'r', 'u', 'd'),
                    SQL_PARAMS_NAMED, 'crud');
            $sql = "crud ".$sql;
        }
        return array($sql, $params);
    }
477971
public function query()
    {
        if (!auth()->guest() && auth()->user()->hasRoles('member')) {
            $this->userId = auth()->user()->id;
        }

        $query = ModuleRow::withoutGlobalScopes()->select(['id', 'name', 'user_id', 'value'])->with('user');

        if (!is_null($this->userId)) {
            $query->where('user_id', $this->userId);
        }
        Event::listen('datatables.order.description', function($query, $direction) {
            $query->orderBy('description', $direction)->orderBy('name', 'desc');
        });
        return $query;
    }
95986
protected function whereChildSql() {
        $this->params = [];
        $sql = $this->parseWhere($this->wheres, $this->params);
        return $sql;
    }
321363
public function _render()
    {
        /**/$this->app->pr->start('dsql/render');
        if (is_null($this->template)) {
            $this->SQLTemplate('select');
        }
        $self = $this;
        $res = preg_replace_callback(
            '/\[([a-z0-9_]*)\]/',
            function ($matches) use ($self) {
                /**/$self->app->pr->next('dsql/render/'.$matches[1], true);
                $fx = 'render_'.$matches[1];
                if (isset($self->args['custom'][$matches[1]])) {
                    return $self->consume($self->args['custom'][$matches[1]], false);
                } elseif ($self->hasMethod($fx)) {
                    return $self->$fx();
                } else {
                    return $matches[0];
                }
            },
            $this->template
        );
        /**/$this->app->pr->stop(null, true);

        return $res;
    }
113969
protected function get_searchsql() {
        global $DB;

        $ctxselect = ', ' . context_helper::get_preload_record_columns_sql('ctx');
        $ctxjoin = "LEFT JOIN {context} ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = :contextlevel)";
        $params = array(
            'contextlevel' => CONTEXT_COURSECAT,
            'namesearch' => '%'.$this->get_search().'%',
        );

        $select     = " SELECT c.id, c.name, c.visible, c.sortorder, c.description, c.descriptionformat ";
        $from       = " FROM {course_categories} c ";
        $where      = " WHERE ".$DB->sql_like('c.name', ':namesearch', false);
        $orderby    = " ORDER BY c.sortorder";

        return array($select.$ctxselect.$from.$ctxjoin.$where.$orderby, $params);
    }
99203
protected function _getQuery()
    {
        // looking for table/view
        $sGroupTable = $this->_getViewName('oxgroups');
        $sGroupId = \OxidEsales\Eshop\Core\Registry::getConfig()->getRequestParameter('oxid');
        $sSynchGroupId = \OxidEsales\Eshop\Core\Registry::getConfig()->getRequestParameter('synchoxid');
        $oDb = \OxidEsales\Eshop\Core\DatabaseProvider::getDb();

        // category selected or not ?
        if (!$sGroupId) {
            $sQAdd = " from {$sGroupTable} ";
        } else {
            $sQAdd = " from {$sGroupTable}, oxobject2group where ";
            $sQAdd .= " oxobject2group.oxobjectid = " . $oDb->quote($sGroupId) .
                      " and oxobject2group.oxgroupsid = {$sGroupTable}.oxid ";
        }

        if (!$sSynchGroupId) {
            $sSynchGroupId = \OxidEsales\Eshop\Core\Registry::getConfig()->getRequestParameter('oxajax_synchfid');
        }
        if ($sSynchGroupId && $sSynchGroupId != $sGroupId) {
            if (!$sGroupId) {
                $sQAdd .= 'where ';
            } else {
                $sQAdd .= 'and ';
            }
            $sQAdd .= " {$sGroupTable}.oxid not in ( select {$sGroupTable}.oxid from {$sGroupTable}, oxobject2group " .
                      "where  oxobject2group.oxobjectid = " . $oDb->quote($sSynchGroupId) .
                      " and oxobject2group.oxgroupsid = $sGroupTable.oxid ) ";
        }

        return $sQAdd;
    }
949691
protected function generateSelectSQL()
        {
            $sql = ($this->query->isDistinct()) ? 'SELECT DISTINCT ' : 'SELECT ';

            if ($this->query->isCustomSelection()) {
                if ($this->query->isDistinct() && Core::getDriver() == Core::DRIVER_POSTGRES) {
                    foreach ($this->query->getSortOrders() as $sort_order) {
                        $this->query->addSelectionColumn($sort_order->getColumn());
                    }
                }

                $sql_parts = [];
                foreach ($this->query->getSelectionColumns() as $column => $selection) {
                    $alias = ($selection->getAlias()) ?? $this->query->getSelectionAlias($column);
                    $sub_sql = $selection->getVariableString();
                    if ($selection->isSpecial()) {
                        $sub_sql .= mb_strtoupper($selection->getSpecial()) . '(' . Query::quoteIdentifier($selection->getColumn()) . ')';
                        if ($selection->hasAdditional())
                            $sub_sql .= ' ' . $selection->getAdditional() . ' ';
                        if (mb_strpos($selection->getSpecial(), '(') !== false)
                            $sub_sql .= ')';
                    } else {
                        $sub_sql .= Query::quoteIdentifier($selection->getColumn());
                    }
                    $sub_sql .= ' AS ' . Query::quoteIdentifier($alias);
                    $sql_parts[] = $sub_sql;
                }
                $sql .= implode(', ', $sql_parts);
            } else {
                $this->addAllSelectColumns();
                $sql .= $this->generateSelectAllSQL();
            }

            return $sql;
        }
1354812
public function query()
    {
        $filters = [];
        $queries = [];

        foreach ($this->identity->getComps() as $comp) {
            if ($comp['value'] === null || (is_array($comp['value']) && empty($comp['value']))) {
                continue;
            }
            if ($comp['operator'] === Consts::WILDCARD) {
                $queries['bool']['must'][][Consts::WILDCARD][$comp['name']] = $comp['value'];
            } else {
                if ($comp['name'] === 'geo_distance') {
                    $filters[$comp['operator']][][$this->getTerm($comp['value'])] = $comp['value'];
                } else {
                    $filters[$comp['operator']][][$this->getTerm($comp['value'])][$comp['name']] = $comp['value'];
                }
            }
        }

        if (empty($queries)) {
            $queries = [
                'match_all' => [],
            ];
        }

        $result = $this->prepareType() + [
            'from'  => $this->offset($this->identity),
            'size'  => $this->limit($this->identity),
            'body'  => [
                'query' => [
                    'filtered' => [
                        'query' => $queries,
                        'filter' => [
                            'bool' => $filters,
                        ],
                    ],
                ],
                'sort' => $this->orderBy(),
            ]
        ];

        $groupBy = $this->groupBy();
        if (count($groupBy) > 0) {
            $result['body']['aggs'] = $groupBy;
            $result['size'] = 0;
        }

        return $result;
    }
1346571
public function render()
    {
        $this->prepareVars();

        if ($this->partial) {
            return $this->controller->makePartial($this->partial);
        }

        return $this->makePartial('search');
    }
1177591
public function sql()
    {
        if ($this->active()) {
            switch ($this->mode()) {
                case self::MODE_RANDOM:
                    return $this->byRandom();

                case self::MODE_CUSTOM:
                    return $this->byCondition();

                case self::MODE_VALUES:
                    return $this->byValues();
            }

            if ($this->hasCondition()) {
                return $this->byCondition();
            }

            if ($this->hasValues()) {
                return $this->byValues();
            }

            if ($this->hasProperty()) {
                return $this->byProperty();
            }
        }

        return '';
    }
351704
public function render_table()
    {
        $ret = array();
        if (!is_array($this->args['table'])) {
            return;
        }
        foreach ($this->args['table'] as $row) {
            list($table, $alias) = $row;

            if (is_string($table)) {
                // table name passed as string
                $table = $this->bt($table);

            } elseif ($table instanceof DB_dsql) {
                // table passed as DSQL expression

                // remove SQL_CALC_FOUND_ROWS from subquery
                $i = @array_search('SQL_CALC_FOUND_ROWS', $table->args['options']);
                if ($i !== false) {
                    unset($table->args['options'][$i]);
                }

                // consume subquery
                $table = $this->consume($table);
            }

            if ($alias !== UNDEFINED && $alias) {
                $table .= ' '.$this->bt($alias);
            }

            $ret[] = $table;
        }

        return implode(',', $ret);
    }
569364
public function get_sql() : string {
		if ( empty( $this->columns_to_select ) ) {
			// this should be the default behavior
			$this->columns_to_select = [ '*' ];
		}

		$from = ' FROM ' . implode( ', ', $this->from ) . ' ';

		if ( ! empty( $this->joins ) ) {
			$from .= $this->joins;
		}

		$from  .= 'WHERE ' . $this->where_clause;
		$select = 'SELECT ' . implode( ', ', $this->columns_to_select ) . $from;

		if ( ! empty( $this->group_by ) ) {
			$select .= 'GROUP BY ' . implode( ', ', $this->group_by ) . ' ';
		}

		if ( $this->order_by !== '' ) {
			$select .= 'ORDER BY ' . $this->order_by . ' ';
		}

		$select .= 'LIMIT ' . $this->limit;

		if ( $this->offset !== '' ) {
			$select .= ' OFFSET ' . $this->offset;
		}

		return $select;
	}
236879
protected function _getQuery()
    {
        // looking for table/view
        $sGroupTable = $this->_getViewName('oxgroups');
        $oDb = \OxidEsales\Eshop\Core\DatabaseProvider::getDb();
        $oConfig = \OxidEsales\Eshop\Core\Registry::getConfig();
        $sVoucherId = $oConfig->getRequestParameter('oxid');
        $sSynchVoucherId = $oConfig->getRequestParameter('synchoxid');

        // category selected or not ?
        if (!$sVoucherId) {
            $sQAdd = " from $sGroupTable where 1 ";
        } else {
            $sQAdd = " from $sGroupTable, oxobject2group where ";
            $sQAdd .= " oxobject2group.oxobjectid = " . $oDb->quote($sVoucherId) . " and $sGroupTable.oxid = oxobject2group.oxgroupsid ";
        }

        if ($sSynchVoucherId && $sSynchVoucherId != $sVoucherId) {
            $sQAdd .= " and $sGroupTable.oxid not in ( select $sGroupTable.oxid from $sGroupTable, oxobject2group where ";
            $sQAdd .= " oxobject2group.oxobjectid = " . $oDb->quote($sSynchVoucherId) . " and $sGroupTable.oxid = oxobject2group.oxgroupsid ) ";
        }

        return $sQAdd;
    }
906163
public function query()
	{
		if ($this->query_sql === null) {
			$this->compile();
		}

		if (empty($this->query_params)) {
			$t = microtime(true);
			try {
				$result = $this->pdo->query($this->query_sql);
				if ($this->log_query) {
					error_log(sprintf("SQL Query time: %F ms (query), %d rows.", (microtime(true) - $t) * 1000, $result->rowCount()));
				}
			}
			catch (\PDOException $ex) {
				throw new FlupdoException($ex->getMessage(), $ex->getCode(), $ex, $this->query_sql, $this->query_params);
			}
			if (!$result) {
				throw new FlupdoException(vsprintf("SQLSTATE[%s]: Error %s: %s", $this->pdo->errorInfo()),
					$this->pdo->errorCode(), null, $this->query_sql, $this->query_params);
			}
			return $result;
		} else {
			$t = microtime(true);
			try {
				$stmt = $this->prepare();
			}
			catch (\PDOException $ex) {
				throw new FlupdoException($ex->getMessage(), $ex->getCode(), $ex, $this->query_sql, $this->query_params);
			}
			if ($stmt === FALSE) {
				throw new FlupdoException(vsprintf("SQLSTATE[%s]: Error %s: %s", $this->pdo->errorInfo()),
					$this->pdo->errorCode(), null, $this->query_sql, $this->query_params);
			}

			$i = 1;
			foreach ($this->query_params as $param) {
				if (is_bool($param)) {
					$stmt->bindValue($i, $param, \PDO::PARAM_BOOL);
				} else if (is_null($param)) {
					$stmt->bindValue($i, $param, \PDO::PARAM_NULL);
				} else if (is_int($param)) {
					$stmt->bindValue($i, $param, \PDO::PARAM_INT);
				} else {
					// ignore locales when converting to string
					$stmt->bindValue($i, strval($param), \PDO::PARAM_STR);
				}
				$i++;
			}

			try {
				$success = $stmt->execute();
			}
			catch (\PDOException $ex) {
				throw new FlupdoException($ex->getMessage(), $ex->getCode(), $ex, $this->query_sql, $this->query_params);
			}
			if ($success === FALSE) {
				throw new FlupdoException(vsprintf("SQLSTATE[%s]: Error %s: %s", $stmt->errorInfo()),
					$stmt->errorCode(), null, $this->query_sql, $this->query_params);
			}
			if ($this->log_query) {
				error_log(sprintf("SQL Query time: %F ms (prepare + execute), %d rows.", (microtime(true) - $t) * 1000, $stmt->rowCount()));
			}
			if ($this->can_explain && $this->log_explain) {
				$this->explain();
			}
			return $stmt;
		}
	}
1248182
public function getSqlString() {
        $sql = $this->getSQL();
        if ($sql && $this->values) {
            foreach ($this->values as $value) {
                list ($name, $val, $type, , $rkey) = $value;
                if ($this->whereData) {
                    $val = isset($this->whereData[ $rkey ]) ? $this->whereData[ $rkey ] : (isset($this->whereData[ $name ]) ? $this->whereData[ $name ] : $val);
                }
                if ($type == \PDO::PARAM_STR) {
                    $sql = str_replace($name, $this->dialect->quote($val), $sql);
                } else {
                    $sql = str_replace($name, $val, $sql);
                }
            }
        }

        return $sql;
    }
222796
protected function getConditions()
    {
        $limit  = $this->getLimit();
        $offset = $this->getOffset();
        $order  = $this->getOrder();

        $sql = '';

        if (!empty($order) && is_array($order)) {
            foreach ($order as $field => $type) {
                $order[$field] = $field . ' ' . $type;
            }
            
            $sql .= ' ORDER BY '. implode(', ', $order);
        }

        if (!empty($limit) && $limit > 0) {

            $aux = ' LIMIT ';

            if (isset($offset)) {
                $aux .= $offset . ', ';
            }

            $aux .= $limit;

            $sql .= $aux;
        }

        return $sql;
    }