PHP Query::getSelect方法代码示例

 /**
  * @param \Freetrix\Main\Data\Connection $connection
  * @param Query                        $query
  *
  * @return bool
  */
 public static function checkQuery(\Freetrix\Main\Data\Connection $connection, Query $query)
 {
     // check interface
     if (!$connection instanceof INosqlPrimarySelector) {
         return false;
     }
     // no expressions in select
     foreach ($query->getSelectChains() as $selectChain) {
         if ($selectChain->getLastElement()->getValue() instanceof ExpressionField) {
             return false;
         }
     }
     // skip empty select, just for not handle this useless case in nosql api
     if (!count($query->getSelect())) {
         return false;
     }
     // if empty joinmap, group, order and simple filter
     if (!count($query->getJoinMap()) && !count($query->getGroupChains()) && !count($query->getOrderChains()) && !count($query->getHavingChains())) {
         $entityPrimary = $query->getEntity()->getPrimary();
         // check for primary singularity
         if (!is_array($entityPrimary)) {
             // check if only primary is in filter
             if (count($query->getFilterChains()) == 1 && key($query->getFilterChains()) === $entityPrimary) {
                 $passFilter = true;
                 // check if only equality operations & 1-level filter
                 foreach ($query->getFilter() as $filterElement => $filterValue) {
                     if (is_numeric($filterElement) && is_array($filterValue)) {
                         // filter has subfilters. not ok
                         $passFilter = false;
                         break;
                     }
                     if ($filterElement === 'LOGIC') {
                         continue;
                     }
                     $operation = substr($filterElement, 0, 1);
                     if ($operation !== '=') {
                         // only equal operation allowed. not ok
                         $passFilter = false;
                         break;
                     }
                 }
                 // fine!
                 if ($passFilter) {
                     return true;
                 }
             }
         }
     }
     return false;
 }

 public function evaluateQuery(Query $query)
 {
     $select = $this->evaluateSelect($query->getSelect()) . " ";
     $from = $this->evaluateFrom($query->getFrom()) . " ";
     $where = $this->evaluateWhere($query->getWhere()) . " ";
     $order = $this->evaluateOrder($query->getOrder()) . " ";
     $groupBy = $this->evaluateGroupBy($query->getGroupBy()) . " ";
     $limit = '';
     if ($query->hasLimit()) {
         $limit = 'LIMIT ' . $query->getLimitMax() . ' ' . $query->getLimitOffset() . ' ';
         // http://www.postgresql.org/docs/8.1/static/queries-limit.html
     }
     return $select . $from . $where . $order . $limit . $groupBy;
 }

 public function evaluateQuery(Query $query)
 {
     $select = $this->evaluateSelect($query->getSelect()) . ' ';
     $from = $this->evaluateFrom($query->getFrom()) . ' ';
     $where = $this->evaluateWhere($query->getWhere()) . ' ';
     $order = $this->evaluateOrder($query->getOrder()) . ' ';
     $groupBy = $this->evaluateGroupBy($query->getGroupBy()) . ' ';
     $limit = '';
     if ($query->hasLimit()) {
         $limit = 'LIMIT ' . $query->getLimitOffset() . ', ' . $query->getLimitMax() . ' ';
         // http://www.sqlite.org/lang_select.html
     }
     return $select . $from . $where . $order . $limit . $groupBy;
 }

 public function evaluateQuery(Query $query)
 {
     $select = $this->evaluateSelect($query->getSelect()) . ' ';
     $from = '';
     $order = $this->evaluateOrder($query->getOrder()) . ' ';
     $groupBy = $this->evaluateGroupBy($query->getGroupBy()) . ' ';
     // SQLServer 2008 no tiene LIMIT, hay que hacerlo con una subquery usando ROWNUM
     // Soluciona problema de paginacion en SQLServer, como esta en DAL::listAll()
     //
     if ($query->hasLimit()) {
         // FIXME:
         // El filtro del where que no es el filtro por rowNum
         // debe hacerse en la consulta interna, sino se toman
         // rowNums en la consulta interna que luego no estan
         // en la consulta final, eso afecta al resultado paginado,
         // porque se pagina por rowNum de rows que no matchean el where, ej:
         //  - consulta interna devuelve 1,2,3,4,5,6,7,8
         //  - consulta externa tiene max=3, offset=0
         //  - where matchea solo 2,4,5,7
         //  - el resultado es solo 2, en lugar de 2,4,5 (son los primeros 3 que matchean)
         //
         // problema, el where incluye condiciones que no son sobre la tabla utilizada
         // en la consulta interna, esto afecta?
         $tableName = $query->getLimitTable();
         $offset = $query->getLimitOffset();
         $max = $query->getLimitMax();
         // La consulta interna es para hacer paginacion
         // WHERE: Las condiciones donde dice tableName pone T2 (no puede evaluar condiciones sobre atributos de tablas no mencionados en el FROM)
         //  - http://social.msdn.microsoft.com/Forums/sqlserver/en-US/3b2e0875-e98c-4931-bcb4-e9f449b637d7/the-multipart-identifier-aliasfield-could-not-be-bound
         // Hago el evaluate del from aca porque tengo que cambiar el FROM de tableName por la subconsulta necesaria para el limit
         $queryFrom = $query->getFrom();
         $alias;
         if (count($queryFrom) == 0) {
             // ERROR! es obligatorio por lo menos una!
             throw new Exception("FROM no puede ser vacio");
         } else {
             /*
             $res = "FROM ";
             foreach ($queryFrom as $table)
             {
                if ( $table->name == $tableName )
                {
                   $alias = $table->alias;
                   
                   $res .= '( SELECT ROW_NUMBER() OVER (ORDER BY id) AS rowNum, * '.
                           'FROM '. $tableName .
                           $this->evaluateWhere( $query->getWhere() ) .
                           ' ) '. $alias .', ';
                }
                else
                {
                   $res .= $table->name .' '. $table->alias .', ';
                }
             }
             $from = substr($res, 0, -2) .' ';
             */
             $alias = 'subq';
             // * selecciona multiples columnas con mismo nombre en distintas tablas
             $subquery = '( ' . $select . ', ROW_NUMBER() OVER (ORDER BY ' . $tableName . '.id) AS rowNum ' . $this->evaluateFrom($query->getFrom()) . ' ' . $this->evaluateWhere($query->getWhere()) . ' ' . $order . $groupBy . ' ) ' . $alias . ' ';
             $from = 'FROM ' . $subquery;
             $select = 'SELECT * ';
             // Select para la query ppal. agregaciones y group se harian en la subquery
         }
         $where = 'WHERE ' . $alias . '.rowNum-1 >= ' . $offset . ' AND ' . $alias . '.rowNum-1 < ' . ($offset + $max);
         return $select . $from . $where;
     }
     // hasLimit
     // !hasLimit
     $from = $this->evaluateFrom($query->getFrom()) . ' ';
     $where = $this->evaluateWhere($query->getWhere()) . ' ';
     return $select . $from . $where . $order . $groupBy;
 }

 public static function getInstanceByQuery(Query $query, &$entity_name = null)
 {
     if (empty($entity_name)) {
         $entity_name = 'Tmp' . randString();
     }
     $query_string = '(' . $query->getQuery() . ')';
     $query_chains = $query->getChains();
     $replaced_aliases = array_flip($query->getReplacedAliases());
     // generate fieldsMap
     $fieldsMap = array('TMP_ID' => array('data_type' => 'integer', 'primary' => true));
     foreach ($query->getSelect() as $k => $v) {
         if (is_array($v)) {
             $fieldsMap[$k] = array('data_type' => $v['data_type']);
         } else {
             $fieldsMap[$k] = array('data_type' => $query_chains[$k]->getLastElement()->getValue()->getDataType());
         }
         if (isset($replaced_aliases[$k])) {
             $fieldsMap[$k]['column_name'] = $replaced_aliases[$k];
         }
     }
     // generate class content
     $eval = 'class ' . $entity_name . 'Entity extends ' . __CLASS__ . ' {' . PHP_EOL;
     $eval .= 'protected function __construct(){}' . PHP_EOL;
     $eval .= 'public function initialize() { $this->className = __CLASS__; $this->filePath = __FILE__;' . PHP_EOL;
     $eval .= '$this->dbTableName = ' . var_export($query_string, true) . ';' . PHP_EOL;
     $eval .= '$this->fieldsMap = ' . var_export($fieldsMap, true) . ';' . PHP_EOL;
     $eval .= '}}';
     eval($eval);
     return self::getInstance($entity_name);
 }