builds the sql query for the GetQueryRestrictionForActiveFilter method
we only want to show results that are values of the selected shop attribute in the filter item.

@return string
542597
protected function GetSQLQueryForQueryRestrictionForActiveFilter()
    {
        $aValues = TTools::MysqlRealEscapeArray($this->aActiveFilterData);
        $sEscapedTargetTable = MySqlLegacySupport::getInstance()->real_escape_string($this->sItemTableName);
        $sEscapedTargetMLTTable = MySqlLegacySupport::getInstance()->real_escape_string('shop_article_'.$this->sItemTableName.'_mlt');

        $sItemListQuery = "SELECT `{$sEscapedTargetMLTTable}`.*
                           FROM `{$sEscapedTargetTable}`
                     INNER JOIN `{$sEscapedTargetMLTTable}` ON `{$sEscapedTargetTable}`.`id` = `{$sEscapedTargetMLTTable}`.`target_id`
                          WHERE ".$this->GetTargetTableNameField()." IN ('".implode("','", $aValues)."')";

        return $sItemListQuery;
    }
799554
public function search()
    {
        $this->load($this->params, '');
        $query = $this->getBasicQuery();
        $languageId = Language::getCurrent()->id;
        $category = Category::findOne($this->categoryId);

        if(!empty($this->filterModel) && $this->filterModel instanceof FilterModel) {
            if(!empty($this->filterModel->pfrom) && $this->filterModel->pfrom != $this->filterModel->minPrice) {
                $query->andWhere(['>=', $this->discountPriceQuery, $this->convertPrice($this->filterModel->pfrom)]);
            }
            if(!empty($this->filterModel->pto) && $this->filterModel->pto != $this->filterModel->maxPrice) {
                $query->andWhere(['<=', $this->discountPriceQuery, $this->convertPrice($this->filterModel->pto)]);
            }
            if(!empty($this->filterModel->vendors)) {
                $query->andWhere(['in', 'shop_product.vendor_id', $this->filterModel->vendors]);
            }
            if(!empty($this->filterModel->availabilities)) {
                $query->andWhere(['in', 'shop_product.availability', $this->filterModel->availabilities]);
            }

            if(!empty($this->filterModel->params)) {
                foreach ($category->filter->params as $filterParam) {
                    if(array_key_exists($filterParam->key, $this->filterModel->params)) {
                        $paramValues = $this->filterModel->params[$filterParam->key];
                        if(!empty($paramValues)) {
                            $key = $filterParam->key;
                            $paramName = $filterParam->translation->param_name;
                            $query->leftJoin("shop_param param_$key", "shop_product.id = param_$key.product_id");
                            $query->leftJoin("shop_param_translation paramt_$key", "param_$key.id = paramt_$key.param_id AND paramt_$key.language_id = $languageId AND paramt_$key.name = '$paramName'");
                            $whereQuery = ['or'];
                            for ($i = 0; $i < count($paramValues); $i++) {
                                $paramValue = $paramValues[$i];
                                $whereQuery[] = ['or',
                                    ["paramt_$key.value" => $paramValue],
                                    new Expression("FIND_IN_SET(:param$i" . "Value1_$key, paramt_$key.value)", [":param$i" . "Value1_$key" => $paramValue]),
                                    new Expression("FIND_IN_SET(:param$i" . "Value2_$key, paramt_$key.value)", [":param$i" . "Value2_$key" => " " . $paramValue])
                                ];
                            }
                            $query->andWhere($whereQuery);
                        }
                    }
                }
            }
        }


        switch (ArrayHelper::getValue($this->params, 'sort')) {
            case self::SORT_CHEAP:
                $query->select([
                    '`shop_product`.*',
                    $this->discountPriceQuery . ' AS discount_price'
                ]);

                $query->orderBy(['discount_price' => SORT_ASC]);
                break;

            case self::SORT_EXPENSIVE:
                $query->select([
                    '`shop_product`.*',
                    $this->discountPriceQuery . ' AS discount_price'
                ]);

                $query->orderBy(['discount_price' => SORT_DESC]);
                break;

            case self::SORT_OLD:
                $query->orderBy(['creation_time' => SORT_ASC]);
                break;

            case self::SORT_NEW:
                $query->orderBy(['creation_time' => SORT_DESC]);
                break;

            default:
                $query->orderBy(['category_id' => SORT_ASC, 'position' => SORT_ASC]);
        }

        $query->groupBy(['id']);

        $dataProvider = new ActiveDataProvider([
            'query' => $query
        ]);

        if ($this->validate()) {
            return $dataProvider;
        }
        else throw new Exception();
    }
1300702
public function filtering()
    {
        if ($this->request->hasHeader('search-protection')) {
            $this->request->setSearchableColumnIndex($this->columns, $this->columnDef['filter']);
        }
        $columns          = $this->request['columns'];
        $this->collection = $this->collection->filter(function ($row) use ($columns) {
            $data                  = $this->serialize($row);
            $this->isFilterApplied = true;
            $found                 = [];

            $keyword = $this->request->keyword();
            foreach ($this->request->searchableColumnIndex() as $index) {
                $column = $this->getColumnName($index);
                if (!$value  = Arr::get($data, $column)) {
                    continue;
                }

                if ($this->isCaseInsensitive()) {
                    $found[] = Str::contains(Str::lower($value), Str::lower($keyword));
                } else {
                    $found[] = Str::contains($value, $keyword);
                }
            }

            return in_array(true, $found);
        }
        );
    }
883228
public function findAllClosedQuery()
    {
        $qb = $this->createQueryBuilder('p');

        return $qb
            // Add conditions.
            ->andWhere($qb->expr()->orX(
                $qb->expr()->eq('p.active', ':active'),
                $qb->expr()->lte('p.expiry', ':now')
            ))

            // Add parameters.
            ->setParameter('active', false)
            ->setParameter('now', new \DateTime())

            // Order by and get results.
            ->orderBy('p.name', 'ASC')
            ->getQuery();
    }
151383
public function GetQueryRestrictionForActiveFilter()
    {
        $sQuery = $this->GetFromInternalCache('sQueryRestrictionForActiveFilter');
        if (is_null($sQuery)) {
            $aValues = $this->aActiveFilterData;
            if (count($aValues) > 0) {
                $sItemListQuery = $this->GetSQLQueryForQueryRestrictionForActiveFilter();
                $aIdList = array();
                if (!empty($sItemListQuery)) {
                    $tRes = MySqlLegacySupport::getInstance()->query($sItemListQuery);
                    $aIdList = array();
                    while ($aItemRow = MySqlLegacySupport::getInstance()->fetch_assoc($tRes)) {
                        $aIdList[] = MySqlLegacySupport::getInstance()->real_escape_string($aItemRow['source_id']);
                    }
                }

                if (count($aIdList) > 0) {
                    $sQuery = "`shop_article`.`id` IN ('".implode("','", $aIdList)."')";
                }
            }
            $this->SetInternalCache('sQueryRestrictionForActiveFilter', $sQuery);
        }

        return $sQuery;
    }
955046
protected function _getQuery()
    {
        $myConfig = \OxidEsales\Eshop\Core\Registry::getConfig();
        $oDb = \OxidEsales\Eshop\Core\DatabaseProvider::getDb();
        $sId = $myConfig->getRequestParameter('oxid');
        $sSynchId = $myConfig->getRequestParameter('synchoxid');

        $sUserTable = $this->_getViewName('oxuser');

        // category selected or not ?
        if (!$sId) {
            $sQAdd = " from $sUserTable where 1 ";
            if (!$myConfig->getConfigParam('blMallUsers')) {
                $sQAdd .= "and $sUserTable.oxshopid = '" . $myConfig->getShopId() . "' ";
            }
        } elseif ($sSynchId && $sSynchId != $sId) {
            // selected group ?
            $sQAdd = " from oxobject2group left join $sUserTable on $sUserTable.oxid = oxobject2group.oxobjectid ";
            $sQAdd .= " where oxobject2group.oxgroupsid = " . $oDb->quote($sId);
            if (!$myConfig->getConfigParam('blMallUsers')) {
                $sQAdd .= "and $sUserTable.oxshopid = '" . $myConfig->getShopId() . "' ";
            }

            // resetting
            $sId = null;
        } else {
            $sQAdd = " from oxobject2delivery, $sUserTable where oxobject2delivery.oxdeliveryid = " . $oDb->quote($sId);
            $sQAdd .= "and oxobject2delivery.oxobjectid = $sUserTable.oxid and oxobject2delivery.oxtype = 'oxdelsetu' ";
        }

        if ($sSynchId && $sSynchId != $sId) {
            $sQAdd .= "and $sUserTable.oxid not in ( select $sUserTable.oxid from oxobject2delivery, $sUserTable where oxobject2delivery.oxdeliveryid = " . $oDb->quote($sSynchId);
            $sQAdd .= "and oxobject2delivery.oxobjectid = $sUserTable.oxid and oxobject2delivery.oxtype = 'oxdelsetu' ) ";
        }

        return $sQAdd;
    }
1046495
public static function &GetInstanceForCurrentFilter()
    {
        static $oInstance = false;
        if (false === $oInstance) {
            $oInstance = null;
            $aFilter = TdbShop::GetActiveFilter();
            if (array_key_exists('shop_manufacturer_id', $aFilter) && !empty($aFilter['shop_manufacturer_id'])) {
                $oInstance = TdbShopManufacturer::GetNewInstance();
                /** @var $oInstance TdbShopManufacturer */
                if (!$oInstance->Load($aFilter['shop_manufacturer_id'])) {
                    $oInstance = null;
                }
            }
        }

        return $oInstance;
    }
550209
public function GetQueryRestrictionForActiveFilter()
    {
        $sQuery = $this->GetFromInternalCache('sQueryRestrictionForActiveFilter');
        if (is_null($sQuery)) {
            $aValues = $this->aActiveFilterData;
            if (is_array($aValues) && count($aValues) > 0) {
                $aValues = TTools::MysqlRealEscapeArray($aValues);
                $sItemListQuery = 'SELECT * FROM `'.MySqlLegacySupport::getInstance()->real_escape_string($this->sItemTableName).'` WHERE '.$this->GetTargetTableNameField()." IN ('".implode("','", $aValues)."')";
                $tRes = MySqlLegacySupport::getInstance()->query($sItemListQuery);
                $aIdList = array();
                while ($aItemRow = MySqlLegacySupport::getInstance()->fetch_assoc($tRes)) {
                    $aIdList[] = MySqlLegacySupport::getInstance()->real_escape_string($aItemRow['id']);
                }

                if (count($aIdList) > 0) {
                    $sQuery = '`shop_article`.`'.MySqlLegacySupport::getInstance()->real_escape_string($this->sItemFieldName)."` IN ('".implode("','", $aIdList)."')";
                }
            }
            $this->SetInternalCache('sQueryRestrictionForActiveFilter', $sQuery);
        }

        return $sQuery;
    }
280742
private function buildQuery()
    {
        $this->validateQuery();

        $statement = "";
        if ($this->select !== null) {
            $statement .= sprintf("%s %s ", self::SELECT, $this->select);
        }
        if ($this->from !== null) {
            $statement .= sprintf("%s %s ", self::FROM, $this->from);
        }
        if ($this->where !== null) {
            $statement .= sprintf("%s %s ", self::WHERE, $this->where);
        }
        if ($this->orderBy !== null) {
            $statement .= sprintf("%s %s ", self::ORDER_BY, $this->orderBy);
        }
        if ($this->limit !== null) {
            $statement .= sprintf("%s %s ", self::LIMIT, $this->limit);
        }
        if ($this->offset !== null) {
            $statement .= sprintf("%s %s ", self::OFFSET, $this->offset);
        }

        return trim($statement);
    }
1036356
public function buildSelectQuery()
	{
		if ($aggregate = $this->getAggregate()) {
			return [$this->buildAggreregateQuery(), [], []];
		}

		$options = [];

		if ($limit = $this->getLimit()) {
			$options[IQuery::LIMIT] = $limit;
		}

		if ($offset = $this->getOffset()) {
			$options[IQuery::OFFSET] = $offset;
		}

		if (($sort = $this->getOrder()) && !empty($sort)) {
			$options[IQuery::ORDER] = $sort;
		}

		return [$this->getSelect(), $this->getWhere(), $options];
	}
378494
protected function _getQuery()
    {
        // active AJAX component
        $sGroupTable = $this->_getViewName('oxgroups');
        $oDb = \OxidEsales\Eshop\Core\DatabaseProvider::getDb();
        $sDiscountId = \OxidEsales\Eshop\Core\Registry::getConfig()->getRequestParameter('oxid');
        $sSynchDiscountId = \OxidEsales\Eshop\Core\Registry::getConfig()->getRequestParameter('synchoxid');

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

        if ($sSynchDiscountId && $sSynchDiscountId != $sDiscountId) {
            $sQAdd .= ' and ' . $sGroupTable . '.oxid not in ( select ' . $sGroupTable . '.oxid from oxobject2group left join ' . $sGroupTable . ' on oxobject2group.oxgroupsid=' . $sGroupTable . '.oxid ';
            $sQAdd .= " where oxobject2group.oxobjectid = " . $oDb->quote($sSynchDiscountId) . " ) ";
        }

        return $sQAdd;
    }
654066
public function GetQueryRestrictionForActiveFilter()
    {
        $sQuery = '';
        $dStartValue = $this->GetActiveStartValue();
        $dEndValue = $this->GetActiveEndValue();
        if ($dStartValue > 0 && $dEndValue > 0) {
            $sQuery = "`shop_article`.`{$this->sItemFieldName}` >= '".MySqlLegacySupport::getInstance()->real_escape_string(
                $dStartValue
            )."' AND `shop_article`.`{$this->sItemFieldName}` <= '".MySqlLegacySupport::getInstance()->real_escape_string($dEndValue)."'";
        } elseif ($dStartValue > 0) {
            $sQuery = "`shop_article`.`{$this->sItemFieldName}` >= '".MySqlLegacySupport::getInstance()->real_escape_string($dStartValue)."'";
        } elseif ($dEndValue > 0) {
            $sQuery = "`shop_article`.`{$this->sItemFieldName}` <= '".MySqlLegacySupport::getInstance()->real_escape_string($dEndValue)."'";
        }

        return $sQuery;
    }
1106068
public function getFilterPreselect()
    {
        // get definitions from plugin settings
        // and proceed only when preselectedFilter was not set
        // this reduces the amount of sql queries, too
        if ($this->conf['preselected_filters'] && count($this->preselectedFilter) == 0) {
            $preselectedArray = GeneralUtility::trimExplode(',', $this->conf['preselected_filters'], true);
            foreach ($preselectedArray as $option) {
                $option = intval($option);
                $queryBuilder = Db::getQueryBuilder('tx_kesearch_filters');
                $pageRepository = GeneralUtility::makeInstance(PageRepository::class);
                $filterRows = $queryBuilder
                    ->add(
                        'select',
                        '`tx_kesearch_filters`.`uid` AS filteruid, `tx_kesearch_filteroptions`.`uid` AS optionuid, `tx_kesearch_filteroptions`.`tag`'
                    )
                    ->from('tx_kesearch_filters')
                    ->from('tx_kesearch_filteroptions')
                    ->add(
                        'where',
                        'FIND_IN_SET("' . $option . '",tx_kesearch_filters.options)'
                        . ' AND `tx_kesearch_filteroptions`.`uid` = ' . $option
                        . $pageRepository->enableFields('tx_kesearch_filters')
                        . $pageRepository->enableFields('tx_kesearch_filteroptions')
                    )
                    ->execute()
                    ->fetchAll();

                foreach ($filterRows as $row) {
                    $this->preselectedFilter[$row['filteruid']][$row['optionuid']] = $row['tag'];
                }
            }
        }
    }
572924
public function query()
    {
        $query = app()->make(RulesRepositoryContract::class)->datatable([
            'tbl_ban_management_rules.id',
            'tbl_ban_management_rules.user_id',
            'tbl_ban_management_rules.internal_reason',
            'tbl_ban_management_rules.value',
            'tbl_ban_management_rules.enabled',
            'tbl_ban_management_rules.trusted',
            'tbl_ban_management_rules.created_at',
            'tbl_ban_management_rules.expired_at'
        ]);

        if (request()->ajax()) {
            $columns = request()->get('columns', []);
            $all     = array_where($columns, function($item, $index) {
                return array_get($item, 'data') === 'status' AND array_get($item, 'search.value') === 'all';
            });

            if (!empty($all)) {
                $query->whereIn('tbl_ban_management_rules.status', [0, 1]);
            }
        }

        if (($where = $this->getDefaultWhere($query)) !== false) {
            $query->where($where);
        }

        return $query;
    }
531894
private function getSelectQuery()
    {
        return 'SELECT '.(empty($this->parts['select']) ? '*' : implode(', ', $this->parts['select'])).
               ' FROM '.$this->getFromClause().
               (($this->parts['where'] !== null) ? ' WHERE '.$this->parts['where'] : null).
               (!empty($this->parts['group_by']) ? ' GROUP BY '.implode(', ', $this->parts['group_by']) : null).
               (($this->parts['having'] !== null) ? ' HAVING '.$this->parts['having'] : null).
               (!empty($this->parts['order_by']) ? ' ORDER BY '.implode(', ', $this->parts['order_by']) : null).
               (($this->parts['limit'] !== null) ? ' LIMIT '.$this->parts['limit'] : null).
               (($this->parts['offset'] !== null) ? ' OFFSET '.$this->parts['offset'] : null);
    }
1097567
public function getQuery() {
        $query = 'SELECT '.implode(', ', $this->fields)
                .' FROM '.implode(', ', $this->from);
        if (count($this->conditions) > 0) {
            $query = $query.' WHERE '.implode(' AND ', $this->conditions);
        }
        $query = rtrim($query).';';

        return $query;
    }
1258025
protected function getQueryResult()
    {
        if ($this->queryResult) {
            return $this->queryResult;
        }

        return $this->queryResult = $this->getSelector()
            ->getSelectBuilder()
            ->execute();
    }