/** * Returns an array of objects queried from the given t41_Object_Collection instance parameters * * The given collection is populated if it comes empty of members. * * In any other case, this method doesn't directly populate the collection. This action is under the responsability of * the caller. For example, the t41_Object_Collection::find() method takes care of it. * * @param t41\ObjectModel\Collection $collection * @param boolean|array $returnCount true = counting, array = stats on listed properties * @param string $subOp complex operation like SUM or AVG * @return array */ public function find(ObjectModel\Collection $collection, $returnCount = false, $subOp = null) { $this->_class = $class = $collection->getDataObject()->getClass(); $table = $this->_getTableFromClass($class); if (!$table) { throw new Exception('MISSING_DBTABLE_PARAM'); } // primary key is either part of the mapper configuration or 'id' $pkey = $this->_mapper ? $this->_mapper->getPrimaryKey($class) : \t41\Backend::DEFAULT_PKEY; if (is_array($pkey)) { $composite = array(); /* @var $obj t41\Backend\Key */ foreach ($pkey as $obj) { $composite[] = sprintf('TRIM(%s)', $table . '.' . $obj->getName()); $composite[] = Backend\Mapper::VALUES_SEPARATOR; } $pkey = sprintf("CONCAT(%s) AS %s", implode(',', $composite), Backend::DEFAULT_PKEY); } else { $pkey = $table . '.' . $pkey; } $this->_connect(); /* @var $select \Zend_Db_Select */ $this->_select = $this->_ressource->select(); // detect if query is of stat-kind if ($returnCount) { switch ($subOp) { case ObjectModel::CALC_SUM: $expressions = array(); foreach ($returnCount as $propKey => $property) { $prop = $this->_mapper ? $this->_mapper->propertyToDatastoreName($class, $propKey) : $propKey; $expressions[] = sprintf('SUM(%s.%s)', $table, $prop); } $subOpExpr = implode('+', $expressions); break; case ObjectModel::CALC_AVG: $subOpExpr = sprintf('AVG(%s)', $returnCount); break; default: $subOpExpr = 'COUNT(*)'; break; } $this->_select->from($table, new \Zend_Db_Expr($subOpExpr . " AS " . \t41\Backend::MAX_ROWS_IDENTIFIER)); } else { $this->_select->distinct(); $this->_select->from($table, $pkey); } $this->_alreadyJoined = array(); /* @var $condition t41\Backend\Condition */ foreach ($collection->getConditions() as $conditionArray) { // combo conditions if ($conditionArray[0] instanceof Condition\Combo) { $statement = array(); foreach ($conditionArray[0]->getConditions() as $condition) { $statement[] = $this->_parseCondition($condition[0], $this->_select, $table); } $statement = implode(' OR ', $statement); switch ($conditionArray[1]) { case Condition::MODE_OR: $this->_select->orWhere($statement); break; case Condition::MODE_AND: default: $this->_select->where($statement); break; } continue; } // optional table where the column may be $jtable = ''; // condition object is in the first key $condition = $conditionArray[0]; /* does condition contain another condition object ? */ if ($condition->isRecursive()) { while ($condition->isRecursive()) { $property = $condition->getProperty(); $parent = $property->getParent() ? $property->getParent()->getId() : $table; $condition = $condition->getCondition(); if ($jtable) { $parentTable = $jtable; } else { if ($parent) { $parentTable = $this->_mapper ? $this->_mapper->getDatastore($parent) : $parent; } else { $parentTable = $table; } } $jtable = $this->_mapper ? $this->_mapper->getDatastore($property->getParameter('instanceof')) : $this->_getTableFromClass($property->getParameter('instanceof')); /* column name in left table */ $jlkey = $this->_mapper ? $this->_mapper->propertyToDatastoreName($class, $property->getId()) : $property->getId(); $uniqext = $jtable . '__joined_for__' . $jlkey; if (in_array($uniqext, $this->_alreadyJoined)) { $class = $property->getParameter('instanceof'); $jtable = $uniqext; continue; } /* pkey name in joined table */ $jpkey = $this->_mapper ? $this->_mapper->getPrimaryKey($property->getParameter('instanceof')) : Backend::DEFAULT_PKEY; $join = sprintf("%s.%s = %s.%s", $parentTable, $jlkey, $uniqext, $jpkey); $this->_select->joinLeft($jtable . " AS {$uniqext}", $join, array()); $this->_alreadyJoined[$jtable] = $uniqext; //$jtable; $jtable = $uniqext; $class = $property->getParameter('instanceof'); } } $property = $condition->getProperty(); if ($property instanceof Property\ObjectProperty) { // no join if object is stored in a different backend ! // @todo improve this part if (ObjectModel::getObjectBackend($property->getParameter('instanceof'))->getAlias() != $this->_uri->getAlias()) { $clauses = $condition->getClauses(); if ($clauses[0]['value'] != Condition::NO_VALUE) { $clauses[0]['operator'] = Condition::OPERATOR_ENDSWITH | Condition::OPERATOR_EQUAL; $condition->setClauses($clauses); } $field = $this->_mapper ? $this->_mapper->propertyToDatastoreName($this->_class, $property->getId()) : $property->getId(); } else { // which table to join with ? (in case of condition is last element of a recursion) $jtable2 = $jtable ? $jtable : $table; $jtable = $this->_mapper ? $this->_mapper->getDatastore($property->getParameter('instanceof')) : $this->_getTableFromClass($property->getParameter('instanceof')); $leftkey = $this->_mapper ? $this->_mapper->propertyToDatastoreName($class, $property->getId()) : $property->getId(); $field = $rightkey = $this->_mapper ? $this->_mapper->getPrimaryKey($property->getParameter('instanceof')) : Backend::DEFAULT_PKEY; $uniqext = $jtable . '__joined_for__' . $leftkey; if (!in_array($uniqext, $this->_alreadyJoined)) { $join = sprintf("%s.%s = %s.%s", $jtable2, $leftkey, $uniqext, is_array($rightkey) ? $rightkey[0] : $rightkey); $this->_select->joinLeft($jtable . " AS {$uniqext}", $join, array()); $this->_alreadyJoined[$jtable] = $uniqext; } $jtable = $uniqext; } } else { if ($property instanceof Property\CollectionProperty) { // handling of conditions based on collection limited to withMembers() and withoutMembers() $leftkey = $property->getParameter('keyprop'); $field = $property->getId(); $subSelect = $this->_ressource->select(); $subseltbl = $this->_mapper ? $this->_mapper->getDatastore($property->getParameter('instanceof')) : $this->_getTableFromClass($property->getParameter('instanceof')); $subSelect->from($subseltbl, new \Zend_Db_Expr(sprintf("COUNT(%s)", $leftkey))); $join = sprintf("%s.%s = %s", $subseltbl, $leftkey, $pkey); $subSelect->where($join); $statement = $this->_buildConditionStatement(new \Zend_Db_Expr(sprintf("(%s)", $subSelect)), $condition->getClauses(), $conditionArray[1]); $this->_select->where($statement); continue; } else { $field = $property->getId(); if ($this->_mapper) { $field = $this->_mapper->propertyToDatastoreName($class, $field); } } } /* convert identifier tag to the valid primary key */ if ($field == ObjectUri::IDENTIFIER) { // @todo handle multiple keys from mapper $field = $table . '.'; $key = $this->_mapper ? $this->_mapper->getPrimaryKey($class) : Backend::DEFAULT_PKEY; $field .= is_array($key) ? $key[0] : $key; } else { if ($jtable) { if (array_key_exists($jtable, $this->_alreadyJoined)) { $field = $this->_alreadyJoined[$jtable] . '.' . $field; } else { $tmp = $jtable . '.'; $tmp .= is_array($field) ? $field[0] : $field; $field = $tmp; } } else { if (array_key_exists($table, $this->_alreadyJoined)) { $field = $this->_alreadyJoined[$table] . '.' . $field; } else { $field = $table . '.' . $field; } } } if ($field instanceof Key) { $field = $table . '.' . $field->getName(); } // protect DateProperty() with setted timepart parameter from misuse if ($property instanceof DateProperty && $property->getParameter('timepart') == true) { $field = "DATE({$field})"; } $statement = $this->_buildConditionStatement($field, $condition->getClauses(), $conditionArray[1]); switch ($conditionArray[1]) { case Condition::MODE_OR: $this->_select->orWhere($statement); break; case Condition::MODE_AND: default: $this->_select->where($statement); break; } } // Adjust query based on returnCount if ($returnCount) { if (is_array($returnCount)) { if ($subOp) { } else { // return count on grouped columns foreach ($returnCount as $key => $property) { $fieldmodifier = null; if ($this->_mapper) { $class = $property->getParent() ? $property->getParent()->getId() : $collection->getDataObject()->getClass(); $field = $this->_mapper->propertyToDatastoreName($class, $property->getId()); } else { $field = $property->getId(); } if ($property instanceof ObjectProperty) { // join with $key if necessary if (strstr($key, '.') !== false) { $leftPart = substr($key, 0, strpos($key, '.')); $intermediateProp = $collection->getDataObject()->getProperty($leftPart); $fieldmodifier = $this->_join($intermediateProp, $table) . '.' . $field; } } // limit date grouping to date part, omitting possible hour part if ($property instanceof DateProperty) { $fieldmodifier = "DATE({$field})"; } $this->_select->group($fieldmodifier ? $fieldmodifier : $field); $this->_select->columns(array($field => $fieldmodifier ? $fieldmodifier : $field)); } } } else { $this->_select->reset('group'); } } else { $this->_select->limit($collection->getBoundaryBatch() != -1 ? $collection->getBoundaryBatch() : null, $collection->getBoundaryOffset()); /** * Sorting part */ foreach ($collection->getSortings() as $sorting) { $slUniqext = $slTable = null; // Specific cases first // @todo find a better way to sort on meta properties if ($sorting[0]->getId() == ObjectUri::IDENTIFIER || $sorting[0] instanceof MetaProperty) { $id = Backend::DEFAULT_PKEY; $this->_select->order(new \Zend_Db_Expr($table . '.' . $id . ' ' . $sorting[1])); continue; } else { if ($sorting[0] instanceof Property\CollectionProperty) { // handling of conditions based on collection limited to withMembers() and withoutMembers() $leftkey = $sorting[0]->getParameter('keyprop'); //$field = $property->getId(); $subSelect = $this->_ressource->select(); $subseltbl = $this->_mapper ? $this->_mapper->getDatastore($sorting[0]->getParameter('instanceof')) : $this->_getTableFromClass($sorting[0]->getParameter('instanceof')); $subSelect->from($subseltbl, new \Zend_Db_Expr(sprintf("COUNT(%s)", $leftkey))); $join = sprintf("%s.%s = %s", $subseltbl, $leftkey, $pkey); $subSelect->where($join); // $statement = $this->_buildConditionStatement(new \Zend_Db_Expr(sprintf("(%s)", $subSelect)), $condition->getClauses(), $conditionArray[1]); $this->_select->order(new \Zend_Db_Expr('(' . $subSelect->__toString() . ') ' . $sorting[1])); continue; } else { if ($sorting[0] instanceof Property\ObjectProperty) { // find which property to sort by if ($sorting[0]->getParameter('sorting')) { $sprops = array_keys($sorting[0]->getParameter('sorting')); } else { // try to sort with properties used to display value if (substr($sorting[0]->getParameter('display'), 0, 1) == '[') { // @todo extract elements of pattern to order from them ? $sprops = array('id'); } else { $sprops = explode(',', $sorting[0]->getParameter('display')); } } // sorting property belongs to a second-level join if ($sorting[0]->getParent()->getClass() != $collection->getClass()) { $leftkey = 'commande'; //$this->_mapper ? $this->_mapper->propertyToDatastoreName($collection->getDataObject()->getClass(), $sorting[0]->getParent()getId()) : $sorting[0]->getId(); $class = $sorting[0]->getParent()->getClass(); $stable = $this->_getTableFromClass($class); $sbackend = ObjectModel::getObjectBackend($class); // Property to sort from is in a different backend from current one if ($sbackend->getAlias() != $this->getAlias()) { // We presume that the current backend is allowed to connect to the remote one // Should we raise an exception instead ? $stable = $sbackend->getUri()->getDatabase() . '.' . $stable; } $field = $sorting[0]->getId(); $rightkey = $this->_mapper ? $this->_mapper->getPrimaryKey($class) : Backend::DEFAULT_PKEY; $uniqext = $stable . '__joined_for__' . $leftkey; if (!in_array($uniqext, $this->_alreadyJoined)) { if (is_array($rightkey)) { foreach ($rightkey as $rightkeyObj) { $join = sprintf("%s.%s = %s.%s", $table, $leftkey, $uniqext, $rightkeyObj->getName()); } } else { $join = sprintf("%s.%s = %s.%s", $table, $leftkey, $uniqext, $rightkey); } $this->_select->joinLeft("{$stable} AS {$uniqext}", $join, array()); $this->_alreadyJoined[$stable] = $uniqext; } $slTable = $this->_getTableFromClass($sorting[0]->getParameter('instanceof')); $slUniqext = $uniqext; } $leftkey = $this->_mapper ? $this->_mapper->propertyToDatastoreName($collection->getDataObject()->getClass(), $sorting[0]->getId()) : $sorting[0]->getId(); $class = $sorting[0]->getParameter('instanceof'); $stable = isset($slTable) ? $slTable : $this->_getTableFromClass($class); $sbackend = ObjectModel::getObjectBackend($class); // Property to sort from is in a different backend from current one if ($sbackend->getAlias() != $this->getAlias()) { // We presume that the current backend is allowed to connect to the remote one // Should we raise an exception instead ? $stable = $sbackend->getUri()->getDatabase() . '.' . $stable; } $field = $sorting[0]->getId(); $rightkey = $this->_mapper ? $this->_mapper->getPrimaryKey($class) : Backend::DEFAULT_PKEY; $uniqext = $stable . '__joined_for__' . $leftkey; if (!in_array($uniqext, $this->_alreadyJoined)) { if (is_array($rightkey)) { foreach ($rightkey as $rightkeyObj) { $join = sprintf("%s.%s = %s.%s", $table, $leftkey, $uniqext, $rightkeyObj->getName()); } } else { $join = sprintf("%s.%s = %s.%s", isset($slUniqext) ? $slUniqext : $table, $leftkey, $uniqext, $rightkey); } $this->_select->joinLeft("{$stable} AS {$uniqext}", $join, array()); $this->_alreadyJoined[$stable] = $uniqext; } foreach ($sprops as $sprop) { if ($this->_mapper) { $sfield = $this->_mapper->propertyToDatastoreName($class, $sprop); } else { $sfield = $sprop; } $sortingExpr = $this->_alreadyJoined[$stable] . '.' . $sfield; if (isset($sorting[2]) && !empty($sorting[2])) { $sortingExpr = sprintf('%s(%s)', $sorting[2], $sortingExpr); } $this->_select->order(new \Zend_Db_Expr($sortingExpr . ' ' . $sorting[1])); } continue; } } } // default sorting on a different table $class = $sorting[0]->getParent() ? $sorting[0]->getParent()->getClass() : $collection->getDataObject()->getClass(); $stable = $this->_getTableFromClass($class); if ($this->_mapper) { $sfield = $this->_mapper->propertyToDatastoreName($class, $sorting[0]->getId()); } else { $field = $sorting[0]; $sfield = $field->getId(); } // add a left join if the sorting field belongs to a table not yet part of the query if ($stable != $table) { // get the property id from the class name $tfield = isset($sorting[3]) ? $sorting[3] : $collection->getDataObject()->getObjectPropertyId($class); $leftkey = $this->_mapper ? $this->_mapper->propertyToDatastoreName($class, $tfield) : $tfield; $rightkey = $this->_mapper ? $this->_mapper->getPrimaryKey($field->getParameter('instanceof')) : Backend::DEFAULT_PKEY; $uniqext = $stable . '__joined_for__' . $leftkey; if (!in_array($uniqext, $this->_alreadyJoined)) { $join = sprintf("%s.%s = %s.%s", $table, $leftkey, $uniqext, $rightkey); $this->_select->joinLeft("{$stable} AS {$uniqext}", $join, array()); $this->_alreadyJoined[$stable] = $uniqext; } $sortingExpr = $this->_alreadyJoined[$stable] . '.' . $sfield; } else { $sortingExpr = $stable . '.' . $sfield; } if (isset($sorting[2]) && !empty($sorting[2])) { $sortingExpr = sprintf('%s(%s)', $sorting[2], $sortingExpr); } $this->_select->order(new \Zend_Db_Expr('TRIM(' . $sortingExpr . ') ' . $sorting[1])); } } $result = array(); $context = array('table' => $table); try { if (true && $returnCount == false) { $this->_select->columns($this->_getColumns($collection->getDataObject())); } $result = $this->_ressource->fetchAll($this->_select); } catch (\Zend_Db_Exception $e) { $context['error'] = $e->getMessage(); $this->_setLastQuery($this->_select->__toString(), $this->_select->getPart('where'), $context); return false; } $this->_setLastQuery($this->_select->__toString(), $this->_select->getPart('where'), $context); if ($returnCount !== false) { return is_array($returnCount) ? $result : $result[0][Backend::MAX_ROWS_IDENTIFIER]; } // convert array of primary keys to strings foreach ($result as $key => $val) { // $result[$key] = implode(Backend\Mapper::VALUES_SEPARATOR, $val); } /* prepare base of object uri */ $uri = new ObjectModel\ObjectUri(); $uri->setBackendUri($this->_uri); $uri->setClass($collection->getDataObject()->getClass()); $uri->setUrl($this->_database . '/' . $table . '/'); return $collection->populate($result, $uri); //return $this->_populateCollection($result, $collection, $uri); }