Creating data query

@param array $select
@return mixed
320753
protected function createQuery(array $select = null)
    {
        HCMenu::$customAppends = ['item_label', 'item_url'];
        HCMenuTypes::$customAppends = ['label'];

        $with = ['language'    => function ($query) {
            $query->select('id', 'iso_639_1');
        }, 'parent'            => function ($query) {
            $query->select(HCMenu::getFillableFields());
        }, 'page.translations' => function ($query) {
            $query->select('id', 'record_id', 'title', 'slug');
        }, 'menu_type'         => function ($query) {
            $query->select('id');
        }];

        if( $select == null )
            $select = HCMenu::getFillableFields();

        $list = HCMenu::with($with)->select($select)
            // add filters
            ->where(function ($query) use ($select) {
                $query = $this->getRequestParameters($query, $select);
            });

        // enabling check for deleted
        $list = $this->checkForDeleted($list);

        // add search items
        $list = $this->search($list);

        // ordering data
        $list = $this->orderData($list, $select);

        return $list;
    }
554275
function select( $table, $options = array() ) {

        $options = array_merge( array(

            'expr' => null,
            'where' => array(),
            'orderBy' => array(),
            'limitCount' => null,
            'limitOffset' => null,
            'params' => array()

        ), $options );

        $query = "SELECT ";

        if ( empty( $options[ 'expr' ] ) ) {

            $query .= "*";

        } else if ( is_array( $options[ 'expr' ] ) ) {

            $query .= implode( ", ", $options[ 'expr' ] );

        } else {

            $query .= $options[ 'expr' ];

        }

        $table = $this->rewriteTable( $table );
        $query .= " FROM " . $this->quoteIdentifier( $table );

        $query .= $this->getSuffix( $options[ 'where' ], $options[ 'orderBy' ], $options[ 'limitCount' ], $options[ 'limitOffset' ] );

        $this->onQuery( $query, $options[ 'params' ] );

        $statement = $this->prepare( $query );
        $statement->setFetchMode( \PDO::FETCH_ASSOC );
        $statement->execute( $options[ 'params' ] );

        return $statement;

    }
534735
protected function createSelectQuery()
    {
        $query = $this->handler->createSelectQuery();
        $query->select(
            $this->handler->quoteColumn(self::COLUMN_ID),
            $this->handler->quoteColumn(self::COLUMN_URL),
            $this->handler->quoteColumn(self::COLUMN_ORIGINAL_URL_MD5),
            $this->handler->quoteColumn(self::COLUMN_IS_VALID),
            $this->handler->quoteColumn(self::COLUMN_LAST_CHECKED),
            $this->handler->quoteColumn(self::COLUMN_CREATED),
            $this->handler->quoteColumn(self::COLUMN_MODIFIED)
        )->from(
            $this->handler->quoteTable(self::URL_TABLE)
        );

        return $query;
    }
982260
public function getCustom($id, array $select = array())
    {
        $supported = $this->isSupported('get');

        return (true === $supported) ? $this->makeRequest("{$this->endpoint}/$id", array('select' => $select)) : $supported;
    }
587242
protected function createSubQueryBuilder($select = null)
    {
        if (!$select) {
            $select = $this->getSelectAs($this->idField);
        }

        // get all filter-fields
        $filterFields = $this->getAllFields(true);

        // get entity names
        $entityNames = $this->getEntityNamesOfFieldDescriptors($filterFields);

        // get necessary joins to achieve filtering
        $addJoins = $this->getNecessaryJoins($entityNames);

        // create querybuilder and add select
        $queryBuilder = $this->createQueryBuilder($addJoins)->select($select);

        if ($this->user && $this->permission && array_key_exists($this->permission, $this->permissions)) {
            $this->addAccessControl(
                $queryBuilder,
                $this->user,
                $this->permissions[$this->permission],
                $this->securedEntityName,
                $this->encodeAlias($this->securedEntityName)
            );
        }

        return $queryBuilder;
    }
33567
public function get()
    {
        $eventResult = $this->fireEvents('before-select');
        if (!is_null($eventResult)) {
            return $eventResult;
        };

        $executionTime = 0;
        if (is_null($this->pdoStatement)) {
            $queryObject = $this->getQuery('select');
            list($this->pdoStatement, $executionTime) = $this->statement(
                $queryObject->getSql(),
                $queryObject->getBindings()
            );
        }

        $start = microtime(true);
        $result = call_user_func_array(array($this->pdoStatement, 'fetchAll'), $this->fetchParameters);
        $executionTime += microtime(true) - $start;
        $this->pdoStatement = null;
        $this->fireEvents('after-select', $result, $executionTime);
        return $result;
    }
1042447
public function select(SQLStatement $select): string
    {
        $limit = $select->getLimit();

        if ($limit <= 0) {
            return parent::select($select);
        }

        $sql = $select->getDistinct() ? 'SELECT DISTINCT ' : 'SELECT ';
        $sql .= $this->handleColumns($select->getColumns());
        $sql .= ' FROM ';
        $sql .= $this->handleTables($select->getTables());
        $sql .= $this->handleJoins($select->getJoins());
        $sql .= $this->handleWheres($select->getWheres());
        $sql .= $this->handleGroupings($select->getGroupBy());
        $sql .= $this->handleOrderings($select->getOrder());
        $sql .= $this->handleHavings($select->getHaving());

        $offset = $select->getOffset();

        if ($offset < 0) {
            return 'SELECT * FROM (' . $sql . ') M1 WHERE ROWNUM <= ' . $limit;
        }

        $limit += $offset;
        $offset++;

        return 'SELECT * FROM (SELECT M1.*, ROWNUM AS OPIS_ROWNUM FROM (' . $sql . ') M1 WHERE ROWNUM <= ' . $limit . ') WHERE OPIS_ROWNUM >= ' . $offset;
    }
468000
protected function fetchCollection(Select $select)
    {
        $collection = [];

        /** @var EntityInterface $entity */
        foreach ($this->selectWith($select) as $entity) {
            $collection[$entity->getIdentifier()] = $entity;
        }

        return $collection;
    }
636928
public function preSelect(Select $select)
	{
        $sname = 'dms';
        $tableGateway = $this->tableGateway;
        $tableName    = $tableGateway->getTable();
        $tablePrimKey = $this->getPrimaryKey($tableGateway);

        $rawState  = $select->getRawState();
        $columns   = $rawState['columns'];
        if (!in_array('*', $columns) && !in_array($tablePrimKey, $columns))
            // We need primary key for postSelect
            array_unshift($columns, $tablePrimKey);

        if (!$this->getDmsColumns()) {
            // we have not any predefined dms columns
            // - look over dms table that which data is provided for this entity of table
            //   as extra data on dmsTable
            // - the query return left joined results of table rows included dms data as
            //   field and content
            // - later on postSelect we will filter data

            // NOTE: not defined dms columns bring down the performance and resources

            if (!in_array('*', $columns)) {
                $dmsColumns = array_diff($columns, $tableGateway->getColumns());
                if ($dmsColumns) {
                    // we`ve not need all dms columns from table, request specific columns
                    // that are not default table columns and assumed as dms
                    // select user_id, field_on_dms, ....
                    $this->setDmsColumns($dmsColumns);
                    return $this->preSelect($select);
                }
            }

            /*
            SELECT mtp . * , dms.field as dms__field, dms.content as dms__content
            FROM  `typopages_page` AS mtp
            LEFT JOIN  `yimabase_dms` AS dms ON dms.foreign_key = mtp.page_id
            */
            $joinTable = $this->getDmsTable()->table;// get name of dms table

            $expression = new Expression(
                "$sname.foreign_key = ?.?
                AND $sname.model = ?
                "
                , [
                    $tableName, $tablePrimKey,
                    get_class($tableGateway),
                ]
                , [
                    Expression::TYPE_IDENTIFIER,
                    Expression::TYPE_IDENTIFIER
                ]
            );

            $select->join(
                [$sname => $joinTable]//join table name
                ,$expression //conditions
                ,[
                    'dms__content' => 'content',
                    'dms__field'   => 'field',
                ]
                ,'left'
            );

            // used in post select
            $this->setStoredValues(['pk' => $tablePrimKey, 'columns' => $rawState['columns']]);

            $select->columns($columns);

            return; // ===================================================================================
        }

		/*
		 SELECT `sampletable`.*, `dms__image`.`content` AS `image`
 		 FROM `sampletable`
		 LEFT JOIN `yimabase_dms` AS `dms__image`
 		  ON dms__image.foreign_key = `sampletable`.`sampletable_id`
      		 AND dms__image.model   = 'application\\Model\\TableGateway\\Sample'
     		 AND dms__image.field   = 'image'
		*/
		
		// Looking at SELECT requested COLUMNS for any DMS Columns ... {
        #  SELECT [column] FROM ...
		$vColumns  = array_values($columns);
		$dmsFields = $this->getDmsColumns();
		
		if (! array_intersect($vColumns, $dmsFields) && !in_array('*', $vColumns) )
            // We don't have any DMS columns on SELECT expression
			return;
        // ... }

		$tableClass   = get_class($tableGateway);
		$tablePrimKey = $this->getPrimaryKey($tableGateway);

		foreach ($this->getDmsColumns() as $dc) {
			if (($key = array_search($dc, $columns)) !== false ) {
				// we have dms column in select and must remove from SELECT 'dms_column'
				unset($columns[$key]);
			} elseif (!in_array('*', $vColumns)) {
				// we don't need any other dms fields, cause not in SELECT
				continue;
			}
			
			$name = 'Dms__'.$dc;

			$joinTable = $this->getDmsTable()->table;// get name of translation table
			$expression = new Expression("
				$name.foreign_key = ?.?
				AND $name.model = ?
				AND $name.field = ?
				"
				,array(
					$tableName, $tablePrimKey,
					$tableClass,
					$dc
				)
				,array(
					Expression::TYPE_IDENTIFIER,
					Expression::TYPE_IDENTIFIER
				)
			);
			
			$select->join(
				array($name => $joinTable)//join table name
				,$expression //conditions
				,array($dc => 'content') // this way we dont need postSelect replacement
				,'left'
			);
		}
		
		$select->columns($columns);

        // Filter dms columns on where clause:
        // SELECT `user`.`_user_id` AS `_user_id`, `Dms__location`.`content` AS `location`
        // FROM `user` LEFT JOIN `user_dms` AS `Dms__location` ON ...
        // WHERE `nation_code` = '0322358078' AND location = '1' <====== sql can't realize this
        // We use this instead:
        // Dms__location.content = '1'

        /** @var \Zend\Db\Sql\Where $where */
        $where      = $rawState['where'];
        foreach ($where->getPredicates() as $pr)
        {
            /** @var \Zend\Db\Sql\Predicate\Operator $predicate */
            $predicate = $pr[1];
            if (method_exists($predicate, 'getLeft')) {
                $operand = $predicate->getLeft();
                if (in_array($operand, $this->getDmsColumns()))
                    $predicate->setLeft('Dms__'.$operand.'.content');
            } elseif (method_exists($predicate, 'getIdentifier')) {
                $operand = $predicate->getIdentifier();
                if (in_array($operand, $this->getDmsColumns()))
                    $predicate->setIdentifier('Dms__'.$operand.'.content');
            }
        }
	}
1302369
public function fetchAssoc(Select $select, AdapterInterface $specialAdapter = null)
    {
        $this->_logger->debug(sprintf('Executing query: %s', $select->assemble()));

        $result = ! is_null($specialAdapter) ? $specialAdapter->fetchAssoc($select) :
            $this->getDefaultConnection()->fetchAssoc($select);

        $this->_logger->debug(sprintf('Query result: %s', trim(print_r($result, true))));

        return $result;
    }
1139747
public function fetchResults(Zend_Db_Select $select, Garp_Browsebox $browsebox) {
        if (!empty($this->_params)) {
            $model = new $this->_config['model']();
            $bindingOptions = $this->_config['bindingOptions'];
            $bindingOptions['modelClass'] = get_class($browsebox->getModel());
            $bindingOptions['conditions'] = $select;

            $model->bindModel('__related__', $bindingOptions);
            $record = $model->find($this->_params)->current();
            $model->unbindModel('__related__');

            return $record->__related__;
        } else {
            throw new Garp_Browsebox_Filter_Exception_NotApplicable();
        }
    }
251498
public function select($columns = "*") : QueryBuilder
    {
        $this->_query[] = "SELECT";

        //Parse column(s)
        if(is_array($columns)) {
            $select_columns = "";

            $size = sizeof($columns);
            for($i = 0; $i < $size; $i++) {
                if($i > 0) {
                    $select_columns .= ', ';
                }

                $select_columns .= '`'. $this->sanitize((string)$columns[$i]) .'`';
            }

            $this->_query[] = $select_columns;

            return $this;
        }

        $select_columns = "*";
        if(
            !empty($columns) &&
            $columns !== "*"
        ) {
            $select_columns = '`'. $this->sanitize((string)$columns) .'`';
        }

        $this->_query[] = $select_columns;

        return $this;
    }
347056
protected function _order(Zend_Db_Table_Select $select, $order)
    {
        if (!is_array($order)) {
            $order = array($order);
        }

        foreach ($order as $val) {
            $select->order($val);
        }

        return $select;
    }
1192471
public function select($select = '*')
    {
        //Argument 1 must be a string or array
        Argument::i()->test(1, 'string', 'array');
        
        //if select is an array
        if (is_array($select)) {
            //transform into a string
            $select = implode(', ', $select);
        }
        
        $this->select = $select;
        
        return $this;
    }
846593
final protected function createSelectQuery(Query $query)
    {
        $select = $this->getDatabase()->select('node', 'n')->fields('n', ['nid'])->addTag('node_access');

        if ($this->addGroupby()) {
            $select->groupBy('n.nid');
        }

        return $select;
    }
249564
protected function processSelect($select, $context)
    {
        if ($select instanceof Aggregate) {
            $column = $this->processColumnOrSubQuery($select->column, $context);
            if ($column === $select->column) {
                return $select;
            } else {
                return new Aggregate($select->function, $column);
            }
        }

        return $this->processColumnOrSubQuery($select, $context);
    }
619174
public static function find($id, $select = ['*'])
    {
        if (! is_array($id)) {
            $id = [$id];
        }

        $static = new static();

        $static->select($select);

        $static->whereIn($static->primary_key, $id);

        return count($id) == 1 ? $static->first() : $static->get();
    }