A real hacky solution to enable admins to search for translated content in the CMS

@param Garp_Model_Db $model
@param Zend_Db_Select $select
@param string $likeValue
@return string A search clause
17525
protected function _joinCmsSearchQuery(
        Garp_Model_Db $model, Zend_Db_Select &$select, $likeValue
    ) {
        $languages = Garp_I18n::getLocales();
        $default_language = array(Garp_I18n::getDefaultLocale());
        $langColumn = self::LANG_COLUMN;
        // Exclude default language, since that's already joined in the joint view
        $languages = array_diff($languages, $default_language);
        $adapter = $model->getAdapter();
        $where = array();
        foreach ($languages as $language) {
            $i18nModel = $this->getI18nModel($model);
            $i18nAlias = $model->getName() . '_i18n_' . $language;
            $onClause = $i18nModel->refMapToOnClause(
                get_class($model), $i18nAlias,
                $model->getJointView()
            );

            // join i18n model
            $select->joinLeft(
                array($i18nAlias => $i18nModel->getName()),
                "$onClause AND {$i18nAlias}.{$langColumn} = '{$language}'",
                array()
            );

            // add WHERE clauses that search in the i18n model
            $translatedFields = $this->_translatableFields;
            foreach ($translatedFields as $i18nField) {
                $where[] = "{$i18nAlias}.{$i18nField} LIKE " . $adapter->quote($likeValue);
            }

        }
        return implode(' OR ', $where);
    }
59218
private function parseSelectQuery()
    {
        // Parse select ?
        $selectSql = Column::generateQueryString($this->query->select);

        // Parse table
        $fromSql = Table::generateQueryString($this->query->table);

        $whereSql = null;
        $whereBind = array();


        $sql = [
            'select' => $selectSql,
            'from' => $fromSql,
            'where' => $whereSql
            // TODO: Add other parts.
        ];

        $this->bind = array_merge(
            $this->bind,
            $whereBind
        );

        $this->sql = $this->combineSelectQuery($sql);
    }
157168
public function search(array $search, $sort, $select = null)
    {
        $select = ($select) ?: $this->getSelect();

        foreach ($search as $key => $value) {
            if (!$value['searchString'] == '') {
                if (substr($value['searchString'], 0, 1) == '=' && $key == 0) {
                    $id = (int)substr($value['searchString'], 1);
                    $select->where->equalTo($this->getPrimaryKey(), $id);
                } else {
                    $where = $select->where->nest();
                    $c = 0;

                    foreach ($value['columns'] as $column) {
                        if ($c > 0) $where->or;
                        $where->like($column, '%' . $value['searchString'] . '%');
                        $c++;
                    }

                    $where->unnest();
                }
            }
        }

        $select = $this->setSortOrder($select, $sort);

        return $this->fetchResult($select);
    }
812414
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_COURSE,
            'fullnamesearch' => '%'.$this->get_search().'%',
            'shortnamesearch' => '%'.$this->get_search().'%'
        );

        $select     = " SELECT c.id, c.fullname, c.shortname, c.visible, c.sortorder ";
        $from       = " FROM {course} c ";
        $where      = " WHERE (".$DB->sql_like('c.fullname', ':fullnamesearch', false)." OR ".
            $DB->sql_like('c.shortname', ':shortnamesearch', false).")";
        $orderby    = " ORDER BY c.sortorder";

        if ($this->currentcourseid !== null && !$this->includecurrentcourse) {
            $where .= " AND c.id <> :currentcourseid";
            $params['currentcourseid'] = $this->currentcourseid;
        }

        return array($select.$ctxselect.$from.$ctxjoin.$where.$orderby, $params);
    }
953861
public function fetchLikesCounters($likeableType, $likeType)
    {
        /** @var \Illuminate\Database\Eloquent\Builder $likesCount */
        $likesCount = app(LikeContract::class)
            ->select([
                DB::raw('COUNT(*) AS count'),
                'likeable_type',
                'likeable_id',
                'type_id',
            ])
            ->where('likeable_type', $likeableType);

        if (!is_null($likeType)) {
            $likesCount->where('type_id', $this->getLikeTypeId($likeType));
        }

        $likesCount->groupBy('likeable_id', 'type_id');

        return $likesCount->get()->toArray();
    }
34856
public function getDatabases(string $like = null): array
    {
        $sql = 'SHOW DATABASES;';
        if ($like !== null) {
            $sql = sprintf('SHOW DATABASES WHERE `database` LIKE %s;', $this->quoter->quoteValue($like));
        }

        return $this->db->queryValues($sql, 'Database');
    }
1028193
public function getQueryReference($key = '', $foreign = '', $foreignLabel = '', $foreignKey = '', &$i)
    {
        $model      = Norm::factory($foreign);
        $refSchemes = $model->schema();
        $foreignKey = $foreignKey ?: 'id';

        if ($foreignKey == '$id') {
            $foreignKey = 'id';
        }

        $query = $key . ' IN (SELECT '.$foreignKey.' FROM '.strtolower($foreign).' WHERE '.$foreignLabel.' LIKE :f'.$i.') ';
        $i++;

        return $query;
    }
562885
public static function _select(array $select=[]) :Query {

		// returns a Query object, to execute, or to complement with some more parameters
		return Database::query()
			->select($select)
			->from(self::tableName());

	}
1100049
final public function fetchByColumn($column, $value, $select = '*')
    {
        $this->validateShortcutData();

        return $this->db->select($select)
                        ->from(static::getTableName())
                        ->whereEquals($column, $value)
                        ->query();
    }
775981
public function compileSelect()
    {

        // Compile "Select From"
        if (sizeof($this->query->getQuerySelect()) == 0) {
            $this->queryString = 'SELECT * ';

            return;
        }

        foreach ($this->query->getQuerySelect() as $key => $value) {
            if ($value instanceof Raw) {
                $value = $value->getString();
            }

            if ($key == 0) {
                $selectString = "$value";
            } else {
                $selectString .= ",$value";
            }
        }

        $this->queryString = "SELECT $selectString";
    }
406995
protected function _setWhereClause(Zend_Db_Select &$select, $slugField, $slug, Garp_Model_Db $model, $lang = null) {
        $slugField = $model->getAdapter()->quoteIdentifier($slugField);
        $select->reset(Zend_Db_Select::WHERE)
            ->where($slugField . ' = ?', $slug);
        if ($lang) {
            $select->where(Garp_Model_Behavior_Translatable::LANG_COLUMN . ' = ?', $lang);
        }
    }
451779
public function fetch(array $options = null) {
        try {
            $this->_checkAcl('fetch');
        } catch (Garp_Auth_Exception $e) {
            $this->_checkAcl('fetch_own');
        }

        if ($this->_model instanceof Garp_Model_Db) {
            $options = $options instanceof Garp_Util_Configuration
                ? $options
                : new Garp_Util_Configuration($options);
            $options
                ->setDefault('sort', array())
                ->setDefault('start', null)
                ->setDefault('limit', null)
                ->setDefault('fields', null)
                ->setDefault('query', false)
                ->setDefault('group', array())
                ->setDefault('rule', null)
                ->setDefault('bindingModel', null)
                ->setDefault('bidirectional', false)
                ->setDefault('filterForeignKeys', false);
            $options['sort']   = (array)$options['sort'];
            $options['fields'] = (array)$options['fields'];
            $tableName         = $this->_getTableName($this->_model);
            $options           = (array)$options;
            $modelInfo         = $this->_model->info();
            $referenceMap      = $modelInfo['referenceMap'];
            // SELECT
            // ============================================================
            $select = $this->_model->select();
            $select->setIntegrityCheck(false);

            // FILTER WHERES AND JOINS
            // ============================================================
            $related = array();
            if ($options['query'] && !empty($options['query'])) {
                /**
                 * Check for other model names in the conditions.
                 * These are indicated by a dot (".") in the name.
                 * If available, add these models as joins to the Select object.
                 * The format is <related-model-name>.<primary-key> => <value>.
                 */
                foreach ($options['query'] as $column => $value) {
                    if (strpos($column, '.') !== false) {
                        $related[$column] = $value;
                        unset($options['query'][$column]);
                    }
                }
            }

            // FROM
            // ============================================================
            if ($options['fields']) {
                $fields = $options['fields'];
            } elseif (count($related)) {
                // When using a join filter (used for the relationpanel), it's more performant to
                // specify only a model's list fields, otherwise the query can get pretty heavy for
                // tables with 100.000+ records.
                $primary = array_values($this->_model->info(Zend_Db_Table_Abstract::PRIMARY));
                $fields = array_merge($this->_model->getListFields(), $primary);
            } else {
                $fields = Zend_Db_Table_Select::SQL_WILDCARD;
            }
            // If filterForeignKeys is true, filter out the foreign keys
            if ($options['filterForeignKeys']) {
                $fields = $this->_filterForeignKeyColumns($fields, $referenceMap);
            }
            $select->from($tableName, $fields);

            // JOIN
            // ============================================================
            if (count($related)) {
                $this->_addJoinClause(
                    $select,
                    $related,
                    $options['rule'],
                    $options['bindingModel'],
                    $options['bidirectional']
                );
            }

            if (array_get($options, 'joinMultilingualModel')) {
                /**
                 * Note that this join is meant just to provide a table with its extended columns to
                 * allow, for instance, sorting.
                 * If you want to sort by `name`, which is often a multilingual field,
                 * you're gonna need this. MySQL would otherwise complain about unknown columns.
                 * Note that this replicates the behavior of the MySQL joint views.
                 */
                $i18nModel = $this->_model->getObserver('Translatable')
                    ->getI18nModel($this->_model);
                $lang = Garp_I18n::getDefaultLocale();
                $select->join(
                    $i18nModel->getName(),
                    $i18nModel->refmapToOnClause(get_class($this->_model)) . " AND lang = '$lang'",
                    array_map(
                        array_get('name'),
                        array_filter(
                            $this->_model->getFieldConfiguration(), array_get('multilingual')
                        )
                    )
                );
            }

            // WHERE
            // Add WHERE clause if there still remains something after
            // filtering.
            // ============================================================
            if ($options['query']) {
                $select->where($this->_createWhereClause($options['query']));
            }

            // GROUP
            // ============================================================
            $select->group($options['group']);

            // ORDER
            // ============================================================
            // Prefix native columns with the table name (e.g. "id" becomes
            // "Thing.id")
            // Note that we create a mock table object based on the joint view
            // to collect column info.
            // This should be more accurate than reading that info from the table.
            $mockTable = new Zend_Db_Table(
                array(
                Zend_Db_Table_Abstract::NAME => $tableName,
                Zend_Db_Table_Abstract::PRIMARY => $this->_model->info(
                    Zend_Db_Table_Abstract::PRIMARY
                ))
            );
            $nativeColumns = $mockTable->info(Zend_Db_Table_Abstract::COLS);

            $select->order(
                array_map(
                    function ($s) use ($tableName, $nativeColumns) {
                        $nativeColTest = preg_replace('/(ASC|DESC)$/', '', $s);
                        $nativeColTest = trim($nativeColTest);

                        if (in_array($nativeColTest, $nativeColumns) && strpos($s, '.') === false) {
                            $s = $tableName . '.' . $s;
                        }
                        return $s;
                    }, $options['sort']
                )
            );

            // LIMIT
            // ============================================================
            // Do not limit when a COUNT(*) is performed, this skews results.
            $isCountQuery = count($fields) == 1
                && !empty($fields[0])
                && strtolower($fields[0]) == 'count(*)';
            if (!$isCountQuery) {
                $select->limit($options['limit'], $options['start']);
            }
            $results = $this->_model->fetchAll($select)->toArray();
        } else {
            $results = $this->_model->fetchAll();
        }

        foreach ($results as &$result) {
            foreach ($result as $column => $value) {
                if (strpos($column, '.') !== false) {
                    $keyParts = explode('.', $column, 2);
                    $newKey = $keyParts[1];
                    $relModelKey = Garp_Util_String::strReplaceOnce(
                        $this->_model->getNameWithoutNamespace(), '', $keyParts[0]
                    );
                    $result['relationMetadata'][$relModelKey][$newKey] = $value;
                    unset($result[$column]);
                }
            }
        }
        return $results;
    }
866115
public function selectOneModel(Select $select, HydratorInterface $hydrator = null, AbstractModel $model = null)
    {
        $resultSet = new ResultSet\HydratingResultSet($this->getHydrator(), $this->getModel());
        return $this->selectOne($select, $resultSet);
    }
506832
public function select(\FreeFW\Core\StorageModel &$p_model, array $p_conditions = [])
    {
        $source     = $p_model::getSource();
        $properties = $p_model::getProperties();
        $values     = [];
        $result     = \FreeFW\DI\DI::get('FreeFW::Model::ResultSet');
        /**
         * @var \FreeFW\Model\Condition $oneCondition
         */
        $where = '';
        foreach ($p_conditions as $idx => $oneCondition) {
            $part = $this->renderCondition($oneCondition);
            if ($where != '') {
                $where = $where . ' AND ';
            }
            $where  = $where . $part['sql'];
            $values = array_merge($values, $part['values']);
        }
        // Build query
        $sql = 'SELECT * FROM ' . $source . ' WHERE ' . $where;
        $this->logger->debug('PDOStorage.select : ' . $sql);
        // I got all, run query...
        try {
            // Get PDO and execute
            $query = $this->provider->prepare($sql, array(\PDO::ATTR_CURSOR => \PDO::CURSOR_FWDONLY));
            if ($query->execute($values)) {
                while ($row = $query->fetch(\PDO::FETCH_OBJ)) {
                    $model = clone($p_model);
                    $model
                        ->init()
                        ->setFromArray($row)
                    ;
                    $result[] = $model;
                }
            } else {
                $this->logger->debug('PDOStorage.select.error : ' . print_r($query->errorInfo(), true));
                $localErr = $query->errorInfo();
                $code     = 0;
                $message  = 'PDOStorage.select.error : ' . print_r($query->errorInfo(), true);
                die($message);
                if (is_array($localErr) && count($localErr) > 1) {
                    $code    = intval($localErr[0]);
                    $message = $localErr[2];
                }
                $p_model->addError($code, $message);
            }
        } catch (\Exception $ex) {
            var_dump($ex);
        }
        return $result;
    }
122636
private function getZoneSelectQuery(): QueryBuilder
    {
        $query = $this->connection->createQueryBuilder();
        $query->select('DISTINCT ngbm_zone.*')
            ->from('ngbm_zone');

        return $query;
    }
1176344
public function like($like_field, $like_value)
    {
        $this->like_field[trim($like_field)] = trim($like_value);
        $this->total = $this->page = $this->total_page = null;

        return $this;
    }
427888
protected function _fetchTotal($modelName, $conditions) {
        if (!array_key_exists('query', $conditions)) {
            $conditions['query'] = array();
        }
        $man = new Garp_Content_Manager($modelName);
        return $man->count($conditions);
    }