PageRenderTime 54ms CodeModel.GetById 14ms RepoModel.GetById 0ms app.codeStats 1ms

/framework/db/ar/CActiveFinder.php

https://bitbucket.org/gencer/yii
PHP | 1661 lines | 1224 code | 139 blank | 298 comment | 180 complexity | b864e2cbbfd793677280d5acc9f2ae5c MD5 | raw file
Possible License(s): BSD-3-Clause

Large files files are truncated, but you can click here to view the full file

  1. <?php
  2. /**
  3. * CActiveFinder class file.
  4. *
  5. * @author Qiang Xue <qiang.xue@gmail.com>
  6. * @link http://www.yiiframework.com/
  7. * @copyright 2008-2013 Yii Software LLC
  8. * @license http://www.yiiframework.com/license/
  9. */
  10. /**
  11. * CActiveFinder implements eager loading and lazy loading of related active records.
  12. *
  13. * When used in eager loading, this class provides the same set of find methods as
  14. * {@link CActiveRecord}.
  15. *
  16. * @author Qiang Xue <qiang.xue@gmail.com>
  17. * @package system.db.ar
  18. * @since 1.0
  19. */
  20. class CActiveFinder extends CComponent
  21. {
  22. /**
  23. * @var boolean join all tables all at once. Defaults to false.
  24. * This property is internally used.
  25. */
  26. public $joinAll=false;
  27. /**
  28. * @var boolean whether the base model has limit or offset.
  29. * This property is internally used.
  30. */
  31. public $baseLimited=false;
  32. private $_joinCount=0;
  33. private $_joinTree;
  34. private $_builder;
  35. /**
  36. * Constructor.
  37. * A join tree is built up based on the declared relationships between active record classes.
  38. * @param CActiveRecord $model the model that initiates the active finding process
  39. * @param mixed $with the relation names to be actively looked for
  40. */
  41. public function __construct($model,$with)
  42. {
  43. $this->_builder=$model->getCommandBuilder();
  44. $this->_joinTree=new CJoinElement($this,$model);
  45. $this->buildJoinTree($this->_joinTree,$with);
  46. }
  47. /**
  48. * Do not call this method. This method is used internally to perform the relational query
  49. * based on the given DB criteria.
  50. * @param CDbCriteria $criteria the DB criteria
  51. * @param boolean $all whether to bring back all records
  52. * @return mixed the query result
  53. */
  54. public function query($criteria,$all=false)
  55. {
  56. $this->joinAll=$criteria->together===true;
  57. if($criteria->alias!='')
  58. {
  59. $this->_joinTree->tableAlias=$criteria->alias;
  60. $this->_joinTree->rawTableAlias=$this->_builder->getSchema()->quoteTableName($criteria->alias);
  61. }
  62. $this->_joinTree->find($criteria);
  63. $this->_joinTree->afterFind();
  64. if($all)
  65. {
  66. $result = array_values($this->_joinTree->records);
  67. if ($criteria->index!==null)
  68. {
  69. $index=$criteria->index;
  70. $array=array();
  71. foreach($result as $object)
  72. $array[$object->$index]=$object;
  73. $result=$array;
  74. }
  75. }
  76. elseif(count($this->_joinTree->records))
  77. $result = reset($this->_joinTree->records);
  78. else
  79. $result = null;
  80. $this->destroyJoinTree();
  81. return $result;
  82. }
  83. /**
  84. * This method is internally called.
  85. * @param string $sql the SQL statement
  86. * @param array $params parameters to be bound to the SQL statement
  87. * @return CActiveRecord
  88. */
  89. public function findBySql($sql,$params=array())
  90. {
  91. Yii::trace(get_class($this->_joinTree->model).'.findBySql() eagerly','system.db.ar.CActiveRecord');
  92. if(($row=$this->_builder->createSqlCommand($sql,$params)->queryRow())!==false)
  93. {
  94. $baseRecord=$this->_joinTree->model->populateRecord($row,false);
  95. $this->_joinTree->findWithBase($baseRecord);
  96. $this->_joinTree->afterFind();
  97. $this->destroyJoinTree();
  98. return $baseRecord;
  99. }
  100. else
  101. $this->destroyJoinTree();
  102. }
  103. /**
  104. * This method is internally called.
  105. * @param string $sql the SQL statement
  106. * @param array $params parameters to be bound to the SQL statement
  107. * @return CActiveRecord[]
  108. */
  109. public function findAllBySql($sql,$params=array())
  110. {
  111. Yii::trace(get_class($this->_joinTree->model).'.findAllBySql() eagerly','system.db.ar.CActiveRecord');
  112. if(($rows=$this->_builder->createSqlCommand($sql,$params)->queryAll())!==array())
  113. {
  114. $baseRecords=$this->_joinTree->model->populateRecords($rows,false);
  115. $this->_joinTree->findWithBase($baseRecords);
  116. $this->_joinTree->afterFind();
  117. $this->destroyJoinTree();
  118. return $baseRecords;
  119. }
  120. else
  121. {
  122. $this->destroyJoinTree();
  123. return array();
  124. }
  125. }
  126. /**
  127. * This method is internally called.
  128. * @param CDbCriteria $criteria the query criteria
  129. * @return string
  130. */
  131. public function count($criteria)
  132. {
  133. Yii::trace(get_class($this->_joinTree->model).'.count() eagerly','system.db.ar.CActiveRecord');
  134. $this->joinAll=$criteria->together!==true;
  135. $alias=$criteria->alias===null ? 't' : $criteria->alias;
  136. $this->_joinTree->tableAlias=$alias;
  137. $this->_joinTree->rawTableAlias=$this->_builder->getSchema()->quoteTableName($alias);
  138. $n=$this->_joinTree->count($criteria);
  139. $this->destroyJoinTree();
  140. return $n;
  141. }
  142. /**
  143. * Finds the related objects for the specified active record.
  144. * This method is internally invoked by {@link CActiveRecord} to support lazy loading.
  145. * @param CActiveRecord $baseRecord the base record whose related objects are to be loaded
  146. */
  147. public function lazyFind($baseRecord)
  148. {
  149. $this->_joinTree->lazyFind($baseRecord);
  150. if(!empty($this->_joinTree->children))
  151. {
  152. foreach($this->_joinTree->children as $child)
  153. $child->afterFind();
  154. }
  155. $this->destroyJoinTree();
  156. }
  157. /**
  158. * Given active record class name returns new model instance.
  159. *
  160. * @param string $className active record class name
  161. * @return CActiveRecord active record model instance
  162. *
  163. * @since 1.1.14
  164. */
  165. public function getModel($className)
  166. {
  167. return CActiveRecord::model($className);
  168. }
  169. private function destroyJoinTree()
  170. {
  171. if($this->_joinTree!==null)
  172. $this->_joinTree->destroy();
  173. $this->_joinTree=null;
  174. }
  175. /**
  176. * Builds up the join tree representing the relationships involved in this query.
  177. * @param CJoinElement $parent the parent tree node
  178. * @param mixed $with the names of the related objects relative to the parent tree node
  179. * @param array $options additional query options to be merged with the relation
  180. * @throws CDbException if given parent tree node is an instance of {@link CStatElement}
  181. * or relation is not defined in the given parent's tree node model class
  182. */
  183. private function buildJoinTree($parent,$with,$options=null)
  184. {
  185. if($parent instanceof CStatElement)
  186. throw new CDbException(Yii::t('yii','The STAT relation "{name}" cannot have child relations.',
  187. array('{name}'=>$parent->relation->name)));
  188. if(is_string($with))
  189. {
  190. if(($pos=strrpos($with,'.'))!==false)
  191. {
  192. $parent=$this->buildJoinTree($parent,substr($with,0,$pos));
  193. $with=substr($with,$pos+1);
  194. }
  195. // named scope
  196. $scopes=array();
  197. if(($pos=strpos($with,':'))!==false)
  198. {
  199. $scopes=explode(':',substr($with,$pos+1));
  200. $with=substr($with,0,$pos);
  201. }
  202. if(isset($parent->children[$with]) && $parent->children[$with]->master===null)
  203. return $parent->children[$with];
  204. if(($relation=$parent->model->getActiveRelation($with))===null)
  205. throw new CDbException(Yii::t('yii','Relation "{name}" is not defined in active record class "{class}".',
  206. array('{class}'=>get_class($parent->model), '{name}'=>$with)));
  207. $relation=clone $relation;
  208. $model=$this->getModel($relation->className);
  209. if($relation instanceof CActiveRelation)
  210. {
  211. $oldAlias=$model->getTableAlias(false,false);
  212. if(isset($options['alias']))
  213. $model->setTableAlias($options['alias']);
  214. elseif($relation->alias===null)
  215. $model->setTableAlias($relation->name);
  216. else
  217. $model->setTableAlias($relation->alias);
  218. }
  219. if(!empty($relation->scopes))
  220. $scopes=array_merge($scopes,(array)$relation->scopes); // no need for complex merging
  221. if(!empty($options['scopes']))
  222. $scopes=array_merge($scopes,(array)$options['scopes']); // no need for complex merging
  223. if(!empty($options['joinOptions']))
  224. $relation->joinOptions=$options['joinOptions'];
  225. $model->resetScope(false);
  226. $criteria=$model->getDbCriteria();
  227. $criteria->scopes=$scopes;
  228. $model->beforeFindInternal();
  229. $model->applyScopes($criteria);
  230. // select has a special meaning in stat relation, so we need to ignore select from scope or model criteria
  231. if($relation instanceof CStatRelation)
  232. $criteria->select='*';
  233. $relation->mergeWith($criteria,true);
  234. // dynamic options
  235. if($options!==null)
  236. $relation->mergeWith($options);
  237. if($relation instanceof CActiveRelation)
  238. $model->setTableAlias($oldAlias);
  239. if($relation instanceof CStatRelation)
  240. return new CStatElement($this,$relation,$parent);
  241. else
  242. {
  243. if(isset($parent->children[$with]))
  244. {
  245. $element=$parent->children[$with];
  246. $element->relation=$relation;
  247. }
  248. else
  249. $element=new CJoinElement($this,$relation,$parent,++$this->_joinCount);
  250. if(!empty($relation->through))
  251. {
  252. $slave=$this->buildJoinTree($parent,$relation->through,array('select'=>''));
  253. $slave->master=$element;
  254. $element->slave=$slave;
  255. }
  256. $parent->children[$with]=$element;
  257. if(!empty($relation->with))
  258. $this->buildJoinTree($element,$relation->with);
  259. return $element;
  260. }
  261. }
  262. // $with is an array, keys are relation name, values are relation spec
  263. foreach($with as $key=>$value)
  264. {
  265. if(is_string($value)) // the value is a relation name
  266. $this->buildJoinTree($parent,$value);
  267. elseif(is_string($key) && is_array($value))
  268. $this->buildJoinTree($parent,$key,$value);
  269. }
  270. }
  271. }
  272. /**
  273. * CJoinElement represents a tree node in the join tree created by {@link CActiveFinder}.
  274. *
  275. * @author Qiang Xue <qiang.xue@gmail.com>
  276. * @package system.db.ar
  277. * @since 1.0
  278. */
  279. class CJoinElement
  280. {
  281. /**
  282. * @var integer the unique ID of this tree node
  283. */
  284. public $id;
  285. /**
  286. * @var CActiveRelation the relation represented by this tree node
  287. */
  288. public $relation;
  289. /**
  290. * @var CActiveRelation the master relation
  291. */
  292. public $master;
  293. /**
  294. * @var CActiveRelation the slave relation
  295. */
  296. public $slave;
  297. /**
  298. * @var CActiveRecord the model associated with this tree node
  299. */
  300. public $model;
  301. /**
  302. * @var array list of active records found by the queries. They are indexed by primary key values.
  303. */
  304. public $records=array();
  305. /**
  306. * @var array list of child join elements
  307. */
  308. public $children=array();
  309. /**
  310. * @var array list of stat elements
  311. */
  312. public $stats=array();
  313. /**
  314. * @var string table alias for this join element
  315. */
  316. public $tableAlias;
  317. /**
  318. * @var string the quoted table alias for this element
  319. */
  320. public $rawTableAlias;
  321. private $_finder;
  322. private $_builder;
  323. private $_parent;
  324. private $_pkAlias; // string or name=>alias
  325. private $_columnAliases=array(); // name=>alias
  326. private $_joined=false;
  327. private $_table;
  328. private $_related=array(); // PK, relation name, related PK => true
  329. /**
  330. * Constructor.
  331. * @param CActiveFinder $finder the finder
  332. * @param mixed $relation the relation (if the third parameter is not null)
  333. * or the model (if the third parameter is null) associated with this tree node.
  334. * @param CJoinElement $parent the parent tree node
  335. * @param integer $id the ID of this tree node that is unique among all the tree nodes
  336. */
  337. public function __construct($finder,$relation,$parent=null,$id=0)
  338. {
  339. $this->_finder=$finder;
  340. $this->id=$id;
  341. if($parent!==null)
  342. {
  343. $this->relation=$relation;
  344. $this->_parent=$parent;
  345. $this->model=$this->_finder->getModel($relation->className);
  346. $this->_builder=$this->model->getCommandBuilder();
  347. $this->tableAlias=$relation->alias===null?$relation->name:$relation->alias;
  348. $this->rawTableAlias=$this->_builder->getSchema()->quoteTableName($this->tableAlias);
  349. $this->_table=$this->model->getTableSchema();
  350. }
  351. else // root element, the first parameter is the model.
  352. {
  353. $this->model=$relation;
  354. $this->_builder=$relation->getCommandBuilder();
  355. $this->_table=$relation->getTableSchema();
  356. $this->tableAlias=$this->model->getTableAlias();
  357. $this->rawTableAlias=$this->_builder->getSchema()->quoteTableName($this->tableAlias);
  358. }
  359. // set up column aliases, such as t1_c2
  360. $table=$this->_table;
  361. if($this->model->getDbConnection()->getDriverName()==='oci') // Issue 482
  362. $prefix='T'.$id.'_C';
  363. else
  364. $prefix='t'.$id.'_c';
  365. foreach($table->getColumnNames() as $key=>$name)
  366. {
  367. $alias=$prefix.$key;
  368. $this->_columnAliases[$name]=$alias;
  369. if($table->primaryKey===$name)
  370. $this->_pkAlias=$alias;
  371. elseif(is_array($table->primaryKey) && in_array($name,$table->primaryKey))
  372. $this->_pkAlias[$name]=$alias;
  373. }
  374. }
  375. /**
  376. * Removes references to child elements and finder to avoid circular references.
  377. * This is internally used.
  378. */
  379. public function destroy()
  380. {
  381. if(!empty($this->children))
  382. {
  383. foreach($this->children as $child)
  384. $child->destroy();
  385. }
  386. unset($this->_finder, $this->_parent, $this->model, $this->relation, $this->master, $this->slave, $this->records, $this->children, $this->stats);
  387. }
  388. /**
  389. * Performs the recursive finding with the criteria.
  390. * @param CDbCriteria $criteria the query criteria
  391. */
  392. public function find($criteria=null)
  393. {
  394. if($this->_parent===null) // root element
  395. {
  396. $query=new CJoinQuery($this,$criteria);
  397. $this->_finder->baseLimited=($criteria->offset>=0 || $criteria->limit>=0);
  398. $this->buildQuery($query);
  399. $this->_finder->baseLimited=false;
  400. $this->runQuery($query);
  401. }
  402. elseif(!$this->_joined && !empty($this->_parent->records)) // not joined before
  403. {
  404. $query=new CJoinQuery($this->_parent);
  405. $this->_joined=true;
  406. $query->join($this);
  407. $this->buildQuery($query);
  408. $this->_parent->runQuery($query);
  409. }
  410. foreach($this->children as $child) // find recursively
  411. $child->find();
  412. foreach($this->stats as $stat)
  413. $stat->query();
  414. }
  415. /**
  416. * Performs lazy find with the specified base record.
  417. * @param CActiveRecord $baseRecord the active record whose related object is to be fetched.
  418. */
  419. public function lazyFind($baseRecord)
  420. {
  421. if(is_string($this->_table->primaryKey))
  422. $this->records[$baseRecord->{$this->_table->primaryKey}]=$baseRecord;
  423. else
  424. {
  425. $pk=array();
  426. foreach($this->_table->primaryKey as $name)
  427. $pk[$name]=$baseRecord->$name;
  428. $this->records[serialize($pk)]=$baseRecord;
  429. }
  430. foreach($this->stats as $stat)
  431. $stat->query();
  432. if(!$this->children)
  433. return;
  434. $params=array();
  435. foreach($this->children as $child)
  436. if(is_array($child->relation->params))
  437. $params=array_merge($params,$child->relation->params);
  438. $query=new CJoinQuery($child);
  439. $query->selects=array($child->getColumnSelect($child->relation->select));
  440. $query->conditions=array(
  441. $child->relation->on,
  442. );
  443. $query->groups[]=$child->relation->group;
  444. $query->joins[]=$child->relation->join;
  445. $query->havings[]=$child->relation->having;
  446. $query->orders[]=$child->relation->order;
  447. $query->params=$params;
  448. $query->elements[$child->id]=true;
  449. if($child->relation instanceof CHasManyRelation)
  450. {
  451. $query->limit=$child->relation->limit;
  452. $query->offset=$child->relation->offset;
  453. }
  454. $child->applyLazyCondition($query,$baseRecord);
  455. $this->_joined=true;
  456. $child->_joined=true;
  457. $this->_finder->baseLimited=false;
  458. $child->buildQuery($query);
  459. $child->runQuery($query);
  460. foreach($child->children as $c)
  461. $c->find();
  462. if(empty($child->records))
  463. return;
  464. if($child->relation instanceof CHasOneRelation || $child->relation instanceof CBelongsToRelation)
  465. $baseRecord->addRelatedRecord($child->relation->name,reset($child->records),false);
  466. else // has_many and many_many
  467. {
  468. foreach($child->records as $record)
  469. {
  470. if($child->relation->index!==null)
  471. $index=$record->{$child->relation->index};
  472. else
  473. $index=true;
  474. $baseRecord->addRelatedRecord($child->relation->name,$record,$index);
  475. }
  476. }
  477. }
  478. /**
  479. * Apply Lazy Condition
  480. * @param CJoinQuery $query represents a JOIN SQL statements
  481. * @param CActiveRecord $record the active record whose related object is to be fetched.
  482. * @throws CDbException if relation in active record class is not specified correctly
  483. */
  484. private function applyLazyCondition($query,$record)
  485. {
  486. $schema=$this->_builder->getSchema();
  487. $parent=$this->_parent;
  488. if($this->relation instanceof CManyManyRelation)
  489. {
  490. $query->conditions=array(
  491. $this->relation->condition,
  492. );
  493. $joinTableName=$this->relation->getJunctionTableName();
  494. if(($joinTable=$schema->getTable($joinTableName))===null)
  495. throw new CDbException(Yii::t('yii','The relation "{relation}" in active record class "{class}" is not specified correctly: the join table "{joinTable}" given in the foreign key cannot be found in the database.',
  496. array('{class}'=>get_class($parent->model), '{relation}'=>$this->relation->name, '{joinTable}'=>$joinTableName)));
  497. $fks=$this->relation->getJunctionForeignKeys();
  498. $joinAlias=$schema->quoteTableName($this->relation->name.'_'.$this->tableAlias);
  499. $parentCondition=array();
  500. $childCondition=array();
  501. $count=0;
  502. $params=array();
  503. $fkDefined=true;
  504. foreach($fks as $i=>$fk)
  505. {
  506. if(isset($joinTable->foreignKeys[$fk])) // FK defined
  507. {
  508. list($tableName,$pk)=$joinTable->foreignKeys[$fk];
  509. if(!isset($parentCondition[$pk]) && $schema->compareTableNames($parent->_table->rawName,$tableName))
  510. {
  511. $parentCondition[$pk]=$joinAlias.'.'.$schema->quoteColumnName($fk).'=:ypl'.$count;
  512. $params[':ypl'.$count]=$record->$pk;
  513. $count++;
  514. }
  515. elseif(!isset($childCondition[$pk]) && $schema->compareTableNames($this->_table->rawName,$tableName))
  516. $childCondition[$pk]=$this->getColumnPrefix().$schema->quoteColumnName($pk).'='.$joinAlias.'.'.$schema->quoteColumnName($fk);
  517. else
  518. {
  519. $fkDefined=false;
  520. break;
  521. }
  522. }
  523. else
  524. {
  525. $fkDefined=false;
  526. break;
  527. }
  528. }
  529. if(!$fkDefined)
  530. {
  531. $parentCondition=array();
  532. $childCondition=array();
  533. $count=0;
  534. $params=array();
  535. foreach($fks as $i=>$fk)
  536. {
  537. if($i<count($parent->_table->primaryKey))
  538. {
  539. $pk=is_array($parent->_table->primaryKey) ? $parent->_table->primaryKey[$i] : $parent->_table->primaryKey;
  540. $parentCondition[$pk]=$joinAlias.'.'.$schema->quoteColumnName($fk).'=:ypl'.$count;
  541. $params[':ypl'.$count]=$record->$pk;
  542. $count++;
  543. }
  544. else
  545. {
  546. $j=$i-count($parent->_table->primaryKey);
  547. $pk=is_array($this->_table->primaryKey) ? $this->_table->primaryKey[$j] : $this->_table->primaryKey;
  548. $childCondition[$pk]=$this->getColumnPrefix().$schema->quoteColumnName($pk).'='.$joinAlias.'.'.$schema->quoteColumnName($fk);
  549. }
  550. }
  551. }
  552. if($parentCondition!==array() && $childCondition!==array())
  553. {
  554. $join='INNER JOIN '.$joinTable->rawName.' '.$joinAlias.' ON ';
  555. $join.='('.implode(') AND (',$parentCondition).') AND ('.implode(') AND (',$childCondition).')';
  556. if(!empty($this->relation->on))
  557. $join.=' AND ('.$this->relation->on.')';
  558. $query->joins[]=$join;
  559. foreach($params as $name=>$value)
  560. $query->params[$name]=$value;
  561. }
  562. else
  563. throw new CDbException(Yii::t('yii','The relation "{relation}" in active record class "{class}" is specified with an incomplete foreign key. The foreign key must consist of columns referencing both joining tables.',
  564. array('{class}'=>get_class($parent->model), '{relation}'=>$this->relation->name)));
  565. }
  566. else
  567. {
  568. $element=$this;
  569. while(true)
  570. {
  571. $condition=$element->relation->condition;
  572. if(!empty($condition))
  573. $query->conditions[]=$condition;
  574. $query->params=array_merge($query->params,$element->relation->params);
  575. if($element->slave!==null)
  576. {
  577. $query->joins[]=$element->slave->joinOneMany($element->slave,$element->relation->foreignKey,$element,$parent);
  578. $element=$element->slave;
  579. }
  580. else
  581. break;
  582. }
  583. $fks=is_array($element->relation->foreignKey) ? $element->relation->foreignKey : preg_split('/\s*,\s*/',$element->relation->foreignKey,-1,PREG_SPLIT_NO_EMPTY);
  584. $prefix=$element->getColumnPrefix();
  585. $params=array();
  586. foreach($fks as $i=>$fk)
  587. {
  588. if(!is_int($i))
  589. {
  590. $pk=$fk;
  591. $fk=$i;
  592. }
  593. if($element->relation instanceof CBelongsToRelation)
  594. {
  595. if(is_int($i))
  596. {
  597. if(isset($parent->_table->foreignKeys[$fk])) // FK defined
  598. $pk=$parent->_table->foreignKeys[$fk][1];
  599. elseif(is_array($element->_table->primaryKey)) // composite PK
  600. $pk=$element->_table->primaryKey[$i];
  601. else
  602. $pk=$element->_table->primaryKey;
  603. }
  604. $params[$pk]=$record->$fk;
  605. }
  606. else
  607. {
  608. if(is_int($i))
  609. {
  610. if(isset($element->_table->foreignKeys[$fk])) // FK defined
  611. $pk=$element->_table->foreignKeys[$fk][1];
  612. elseif(is_array($parent->_table->primaryKey)) // composite PK
  613. $pk=$parent->_table->primaryKey[$i];
  614. else
  615. $pk=$parent->_table->primaryKey;
  616. }
  617. $params[$fk]=$record->$pk;
  618. }
  619. }
  620. $count=0;
  621. foreach($params as $name=>$value)
  622. {
  623. $query->conditions[]=$prefix.$schema->quoteColumnName($name).'=:ypl'.$count;
  624. $query->params[':ypl'.$count]=$value;
  625. $count++;
  626. }
  627. }
  628. }
  629. /**
  630. * Performs the eager loading with the base records ready.
  631. * @param mixed $baseRecords the available base record(s).
  632. */
  633. public function findWithBase($baseRecords)
  634. {
  635. if(!is_array($baseRecords))
  636. $baseRecords=array($baseRecords);
  637. if(is_string($this->_table->primaryKey))
  638. {
  639. foreach($baseRecords as $baseRecord)
  640. $this->records[$baseRecord->{$this->_table->primaryKey}]=$baseRecord;
  641. }
  642. else
  643. {
  644. foreach($baseRecords as $baseRecord)
  645. {
  646. $pk=array();
  647. foreach($this->_table->primaryKey as $name)
  648. $pk[$name]=$baseRecord->$name;
  649. $this->records[serialize($pk)]=$baseRecord;
  650. }
  651. }
  652. $query=new CJoinQuery($this);
  653. $this->buildQuery($query);
  654. if(count($query->joins)>1)
  655. $this->runQuery($query);
  656. foreach($this->children as $child)
  657. $child->find();
  658. foreach($this->stats as $stat)
  659. $stat->query();
  660. }
  661. /**
  662. * Count the number of primary records returned by the join statement.
  663. * @param CDbCriteria $criteria the query criteria
  664. * @return string number of primary records. Note: type is string to keep max. precision.
  665. */
  666. public function count($criteria=null)
  667. {
  668. $query=new CJoinQuery($this,$criteria);
  669. // ensure only one big join statement is used
  670. $this->_finder->baseLimited=false;
  671. $this->_finder->joinAll=true;
  672. $this->buildQuery($query);
  673. $query->limit=$query->offset=-1;
  674. if(!empty($criteria->group) || !empty($criteria->having))
  675. {
  676. $query->orders = array();
  677. $command=$query->createCommand($this->_builder);
  678. $sql=$command->getText();
  679. $sql="SELECT COUNT(*) FROM ({$sql}) sq";
  680. $command->setText($sql);
  681. $command->params=$query->params;
  682. return $command->queryScalar();
  683. }
  684. else
  685. {
  686. $select=is_array($criteria->select) ? implode(',',$criteria->select) : $criteria->select;
  687. if($select!=='*' && preg_match('/^count\s*\(/',trim($select)))
  688. $query->selects=array($select);
  689. elseif(is_string($this->_table->primaryKey))
  690. {
  691. $prefix=$this->getColumnPrefix();
  692. $schema=$this->_builder->getSchema();
  693. $column=$prefix.$schema->quoteColumnName($this->_table->primaryKey);
  694. $query->selects=array("COUNT(DISTINCT $column)");
  695. }
  696. else
  697. $query->selects=array("COUNT(*)");
  698. $query->orders=$query->groups=$query->havings=array();
  699. $command=$query->createCommand($this->_builder);
  700. return $command->queryScalar();
  701. }
  702. }
  703. /**
  704. * Calls {@link CActiveRecord::afterFind} of all the records.
  705. */
  706. public function afterFind()
  707. {
  708. foreach($this->records as $record)
  709. $record->afterFindInternal();
  710. foreach($this->children as $child)
  711. $child->afterFind();
  712. $this->children = null;
  713. }
  714. /**
  715. * Builds the join query with all descendant HAS_ONE and BELONGS_TO nodes.
  716. * @param CJoinQuery $query the query being built up
  717. */
  718. public function buildQuery($query)
  719. {
  720. foreach($this->children as $child)
  721. {
  722. if($child->master!==null)
  723. $child->_joined=true;
  724. elseif($child->relation instanceof CHasOneRelation || $child->relation instanceof CBelongsToRelation
  725. || $this->_finder->joinAll || $child->relation->together || (!$this->_finder->baseLimited && $child->relation->together===null))
  726. {
  727. $child->_joined=true;
  728. $query->join($child);
  729. $child->buildQuery($query);
  730. }
  731. }
  732. }
  733. /**
  734. * Executes the join query and populates the query results.
  735. * @param CJoinQuery $query the query to be executed.
  736. */
  737. public function runQuery($query)
  738. {
  739. $command=$query->createCommand($this->_builder);
  740. foreach($command->queryAll() as $row)
  741. $this->populateRecord($query,$row);
  742. }
  743. /**
  744. * Populates the active records with the query data.
  745. * @param CJoinQuery $query the query executed
  746. * @param array $row a row of data
  747. * @return CActiveRecord the populated record
  748. */
  749. private function populateRecord($query,$row)
  750. {
  751. // determine the primary key value
  752. if(is_string($this->_pkAlias)) // single key
  753. {
  754. if(isset($row[$this->_pkAlias]))
  755. $pk=$row[$this->_pkAlias];
  756. else // no matching related objects
  757. return null;
  758. }
  759. else // is_array, composite key
  760. {
  761. $pk=array();
  762. foreach($this->_pkAlias as $name=>$alias)
  763. {
  764. if(isset($row[$alias]))
  765. $pk[$name]=$row[$alias];
  766. else // no matching related objects
  767. return null;
  768. }
  769. $pk=serialize($pk);
  770. }
  771. // retrieve or populate the record according to the primary key value
  772. if(isset($this->records[$pk]))
  773. $record=$this->records[$pk];
  774. else
  775. {
  776. $attributes=array();
  777. $aliases=array_flip($this->_columnAliases);
  778. foreach($row as $alias=>$value)
  779. {
  780. if(isset($aliases[$alias]))
  781. $attributes[$aliases[$alias]]=$value;
  782. }
  783. $record=$this->model->populateRecord($attributes,false);
  784. foreach($this->children as $child)
  785. {
  786. if(!empty($child->relation->select))
  787. $record->addRelatedRecord($child->relation->name,null,$child->relation instanceof CHasManyRelation);
  788. }
  789. $this->records[$pk]=$record;
  790. }
  791. // populate child records recursively
  792. foreach($this->children as $child)
  793. {
  794. if(!isset($query->elements[$child->id]) || empty($child->relation->select))
  795. continue;
  796. $childRecord=$child->populateRecord($query,$row);
  797. if($child->relation instanceof CHasOneRelation || $child->relation instanceof CBelongsToRelation)
  798. $record->addRelatedRecord($child->relation->name,$childRecord,false);
  799. else // has_many and many_many
  800. {
  801. // need to double check to avoid adding duplicated related objects
  802. if($childRecord instanceof CActiveRecord)
  803. $fpk=serialize($childRecord->getPrimaryKey());
  804. else
  805. $fpk=0;
  806. if(!isset($this->_related[$pk][$child->relation->name][$fpk]))
  807. {
  808. if($childRecord instanceof CActiveRecord && $child->relation->index!==null)
  809. $index=$childRecord->{$child->relation->index};
  810. else
  811. $index=true;
  812. $record->addRelatedRecord($child->relation->name,$childRecord,$index);
  813. $this->_related[$pk][$child->relation->name][$fpk]=true;
  814. }
  815. }
  816. }
  817. return $record;
  818. }
  819. /**
  820. * @return string the table name and the table alias (if any). This can be used directly in SQL query without escaping.
  821. */
  822. public function getTableNameWithAlias()
  823. {
  824. if($this->tableAlias!==null)
  825. return $this->_table->rawName . ' ' . $this->rawTableAlias;
  826. else
  827. return $this->_table->rawName;
  828. }
  829. /**
  830. * Generates the list of columns to be selected.
  831. * Columns will be properly aliased and primary keys will be added to selection if they are not specified.
  832. * @param mixed $select columns to be selected. Defaults to '*', indicating all columns.
  833. * @throws CDbException if active record class is trying to select an invalid column
  834. * @return string the column selection
  835. */
  836. public function getColumnSelect($select='*')
  837. {
  838. $schema=$this->_builder->getSchema();
  839. $prefix=$this->getColumnPrefix();
  840. $columns=array();
  841. if($select==='*')
  842. {
  843. foreach($this->_table->getColumnNames() as $name)
  844. $columns[]=$prefix.$schema->quoteColumnName($name).' AS '.$schema->quoteColumnName($this->_columnAliases[$name]);
  845. }
  846. else
  847. {
  848. if(is_string($select))
  849. $select=explode(',',$select);
  850. $selected=array();
  851. foreach($select as $name)
  852. {
  853. $name=trim($name);
  854. $matches=array();
  855. if(($pos=strrpos($name,'.'))!==false)
  856. $key=substr($name,$pos+1);
  857. else
  858. $key=$name;
  859. $key=trim($key,'\'"`');
  860. if($key==='*')
  861. {
  862. foreach($this->_table->columns as $name=>$column)
  863. {
  864. $alias=$this->_columnAliases[$name];
  865. if(!isset($selected[$alias]))
  866. {
  867. $columns[]=$prefix.$column->rawName.' AS '.$schema->quoteColumnName($alias);
  868. $selected[$alias]=1;
  869. }
  870. }
  871. continue;
  872. }
  873. if(isset($this->_columnAliases[$key])) // simple column names
  874. {
  875. $columns[]=$prefix.$schema->quoteColumnName($key).' AS '.$schema->quoteColumnName($this->_columnAliases[$key]);
  876. $selected[$this->_columnAliases[$key]]=1;
  877. }
  878. elseif(preg_match('/^(.*?)\s+AS\s+(\w+)$/im',$name,$matches)) // if the column is already aliased
  879. {
  880. $alias=$matches[2];
  881. if(!isset($this->_columnAliases[$alias]) || $this->_columnAliases[$alias]!==$alias)
  882. {
  883. $this->_columnAliases[$alias]=$alias;
  884. $columns[]=$name;
  885. $selected[$alias]=1;
  886. }
  887. }
  888. else
  889. throw new CDbException(Yii::t('yii','Active record "{class}" is trying to select an invalid column "{column}". Note, the column must exist in the table or be an expression with alias.',
  890. array('{class}'=>get_class($this->model), '{column}'=>$name)));
  891. }
  892. // add primary key selection if they are not selected
  893. if(is_string($this->_pkAlias) && !isset($selected[$this->_pkAlias]))
  894. $columns[]=$prefix.$schema->quoteColumnName($this->_table->primaryKey).' AS '.$schema->quoteColumnName($this->_pkAlias);
  895. elseif(is_array($this->_pkAlias))
  896. {
  897. foreach($this->_pkAlias as $name=>$alias)
  898. if(!isset($selected[$alias]))
  899. $columns[]=$prefix.$schema->quoteColumnName($name).' AS '.$schema->quoteColumnName($alias);
  900. }
  901. }
  902. return implode(', ',$columns);
  903. }
  904. /**
  905. * @return string the primary key selection
  906. */
  907. public function getPrimaryKeySelect()
  908. {
  909. $schema=$this->_builder->getSchema();
  910. $prefix=$this->getColumnPrefix();
  911. $columns=array();
  912. if(is_string($this->_pkAlias))
  913. $columns[]=$prefix.$schema->quoteColumnName($this->_table->primaryKey).' AS '.$schema->quoteColumnName($this->_pkAlias);
  914. elseif(is_array($this->_pkAlias))
  915. {
  916. foreach($this->_pkAlias as $name=>$alias)
  917. $columns[]=$prefix.$schema->quoteColumnName($name).' AS '.$schema->quoteColumnName($alias);
  918. }
  919. return implode(', ',$columns);
  920. }
  921. /**
  922. * @return string the condition that specifies only the rows with the selected primary key values.
  923. */
  924. public function getPrimaryKeyRange()
  925. {
  926. if(empty($this->records))
  927. return '';
  928. $values=array_keys($this->records);
  929. if(is_array($this->_table->primaryKey))
  930. {
  931. foreach($values as &$value)
  932. $value=unserialize($value);
  933. }
  934. return $this->_builder->createInCondition($this->_table,$this->_table->primaryKey,$values,$this->getColumnPrefix());
  935. }
  936. /**
  937. * @return string the column prefix for column reference disambiguation
  938. */
  939. public function getColumnPrefix()
  940. {
  941. if($this->tableAlias!==null)
  942. return $this->rawTableAlias.'.';
  943. else
  944. return $this->_table->rawName.'.';
  945. }
  946. /**
  947. * @throws CDbException if relation in active record class is not specified correctly
  948. * @return string the join statement (this node joins with its parent)
  949. */
  950. public function getJoinCondition()
  951. {
  952. $parent=$this->_parent;
  953. if($this->relation instanceof CManyManyRelation)
  954. {
  955. $schema=$this->_builder->getSchema();
  956. $joinTableName=$this->relation->getJunctionTableName();
  957. if(($joinTable=$schema->getTable($joinTableName))===null)
  958. throw new CDbException(Yii::t('yii','The relation "{relation}" in active record class "{class}" is not specified correctly: the join table "{joinTable}" given in the foreign key cannot be found in the database.',
  959. array('{class}'=>get_class($parent->model), '{relation}'=>$this->relation->name, '{joinTable}'=>$joinTableName)));
  960. $fks=$this->relation->getJunctionForeignKeys();
  961. return $this->joinManyMany($joinTable,$fks,$parent);
  962. }
  963. else
  964. {
  965. $fks=is_array($this->relation->foreignKey) ? $this->relation->foreignKey : preg_split('/\s*,\s*/',$this->relation->foreignKey,-1,PREG_SPLIT_NO_EMPTY);
  966. if($this->slave!==null)
  967. {
  968. if($this->relation instanceof CBelongsToRelation)
  969. {
  970. $fks=array_flip($fks);
  971. $pke=$this->slave;
  972. $fke=$this;
  973. }
  974. else
  975. {
  976. $pke=$this;
  977. $fke=$this->slave;
  978. }
  979. }
  980. elseif($this->relation instanceof CBelongsToRelation)
  981. {
  982. $pke=$this;
  983. $fke=$parent;
  984. }
  985. else
  986. {
  987. $pke=$parent;
  988. $fke=$this;
  989. }
  990. return $this->joinOneMany($fke,$fks,$pke,$parent);
  991. }
  992. }
  993. /**
  994. * Generates the join statement for one-many relationship.
  995. * This works for HAS_ONE, HAS_MANY and BELONGS_TO.
  996. * @param CJoinElement $fke the join element containing foreign keys
  997. * @param array $fks the foreign keys
  998. * @param CJoinElement $pke the join element contains primary keys
  999. * @param CJoinElement $parent the parent join element
  1000. * @return string the join statement
  1001. * @throws CDbException if a foreign key is invalid
  1002. */
  1003. private function joinOneMany($fke,$fks,$pke,$parent)
  1004. {
  1005. $schema=$this->_builder->getSchema();
  1006. $joins=array();
  1007. if(is_string($fks))
  1008. $fks=preg_split('/\s*,\s*/',$fks,-1,PREG_SPLIT_NO_EMPTY);
  1009. foreach($fks as $i=>$fk)
  1010. {
  1011. if(!is_int($i))
  1012. {
  1013. $pk=$fk;
  1014. $fk=$i;
  1015. }
  1016. if(!isset($fke->_table->columns[$fk]))
  1017. throw new CDbException(Yii::t('yii','The relation "{relation}" in active record class "{class}" is specified with an invalid foreign key "{key}". There is no such column in the table "{table}".',
  1018. array('{class}'=>get_class($parent->model), '{relation}'=>$this->relation->name, '{key}'=>$fk, '{table}'=>$fke->_table->name)));
  1019. if(is_int($i))
  1020. {
  1021. if(isset($fke->_table->foreignKeys[$fk]) && $schema->compareTableNames($pke->_table->rawName, $fke->_table->foreignKeys[$fk][0]))
  1022. $pk=$fke->_table->foreignKeys[$fk][1];
  1023. else // FK constraints undefined
  1024. {
  1025. if(is_array($pke->_table->primaryKey)) // composite PK
  1026. $pk=$pke->_table->primaryKey[$i];
  1027. else
  1028. $pk=$pke->_table->primaryKey;
  1029. }
  1030. }
  1031. $joins[]=$fke->getColumnPrefix().$schema->quoteColumnName($fk) . '=' . $pke->getColumnPrefix().$schema->quoteColumnName($pk);
  1032. }
  1033. if(!empty($this->relation->on))
  1034. $joins[]=$this->relation->on;
  1035. if(!empty($this->relation->joinOptions) && is_string($this->relation->joinOptions))
  1036. return $this->relation->joinType.' '.$this->getTableNameWithAlias().' '.$this->relation->joinOptions.
  1037. ' ON ('.implode(') AND (',$joins).')';
  1038. else
  1039. return $this->relation->joinType.' '.$this->getTableNameWithAlias().' ON ('.implode(') AND (',$joins).')';
  1040. }
  1041. /**
  1042. * Generates the join statement for many-many relationship.
  1043. * @param CDbTableSchema $joinTable the join table
  1044. * @param array $fks the foreign keys
  1045. * @param CJoinElement $parent the parent join element
  1046. * @return string the join statement
  1047. * @throws CDbException if a foreign key is invalid
  1048. */
  1049. private function joinManyMany($joinTable,$fks,$parent)
  1050. {
  1051. $schema=$this->_builder->getSchema();
  1052. $joinAlias=$schema->quoteTableName($this->relation->name.'_'.$this->tableAlias);
  1053. $parentCondition=array();
  1054. $childCondition=array();
  1055. $fkDefined=true;
  1056. foreach($fks as $i=>$fk)
  1057. {
  1058. if(!isset($joinTable->columns[$fk]))
  1059. throw new CDbException(Yii::t('yii','The relation "{relation}" in active record class "{class}" is specified with an invalid foreign key "{key}". There is no such column in the table "{table}".',
  1060. array('{class}'=>get_class($parent->model), '{relation}'=>$this->relation->name, '{key}'=>$fk, '{table}'=>$joinTable->name)));
  1061. if(isset($joinTable->foreignKeys[$fk]))
  1062. {
  1063. list($tableName,$pk)=$joinTable->foreignKeys[$fk];
  1064. if(!isset($parentCondition[$pk]) && $schema->compareTableNames($parent->_table->rawName,$tableName))
  1065. $parentCondition[$pk]=$parent->getColumnPrefix().$schema->quoteColumnName($pk).'='.$joinAlias.'.'.$schema->quoteColumnName($fk);
  1066. elseif(!isset($childCondition[$pk]) && $schema->compareTableNames($this->_table->rawName,$tableName))
  1067. $childCondition[$pk]=$this->getColumnPrefix().$schema->quoteColumnName($pk).'='.$joinAlias.'.'.$schema->quoteColumnName($fk);
  1068. else
  1069. {
  1070. $fkDefined=false;
  1071. break;
  1072. }
  1073. }
  1074. else
  1075. {
  1076. $fkDefined=false;
  1077. break;
  1078. }
  1079. }
  1080. if(!$fkDefined)
  1081. {
  1082. $parentCondition=array();
  1083. $childCondition=array();
  1084. foreach($fks as $i=>$fk)
  1085. {
  1086. if($i<count($parent->_table->primaryKey))
  1087. {
  1088. $pk=is_array($parent->_table->primaryKey) ? $parent->_table->primaryKey[$i] : $parent->_table->primaryKey;
  1089. $parentCondition[$pk]=$parent->getColumnPrefix().$schema->quoteColumnName($pk).'='.$joinAlias.'.'.$schema->quoteColumnName($fk);
  1090. }
  1091. else
  1092. {
  1093. $j=$i-count($parent->_table->primaryKey);
  1094. $pk=is_array($this->_table->primaryKey) ? $this->_table->primaryKey[$j] : $this->_table->primaryKey;
  1095. $childCondition[$pk]=$this->getColumnPrefix().$schema->quoteColumnName($pk).'='.$joinAlias.'.'.$schema->quoteColumnName($fk);
  1096. }
  1097. }
  1098. }
  1099. if($parentCondition!==array() && $childCondition!==array())
  1100. {
  1101. $join=$this->relation->joinType.' '.$joinTable->rawName.' '.$joinAlias;
  1102. if(is_array($this->relation->joinOptions) && isset($this->relation->joinOptions[0]) &&
  1103. is_string($this->relation->joinOptions[0]))
  1104. $join.=' '.$this->relation->joinOptions[0];
  1105. elseif(!empty($this->relation->joinOptions) && is_string($this->relation->joinOptions))
  1106. $join.=' '.$this->relation->joinOptions;
  1107. $join.=' ON ('.implode(') AND (',$parentCondition).')';
  1108. $join.=' '.$this->relation->joinType.' '.$this->getTableNameWithAlias();
  1109. if(is_array($this->relation->joinOptions) && isset($this->relation->joinOptions[1]) &&
  1110. is_string($this->relation->joinOptions[1]))
  1111. $join.=' '.$this->relation->joinOptions[1];
  1112. $join.=' ON ('.implode(') AND (',$childCondition).')';
  1113. if(!empty($this->relation->on))
  1114. $join.=' AND ('.$this->relation->on.')';
  1115. return $join;
  1116. }
  1117. else
  1118. throw new CDbException(Yii::t('yii','The relation "{relation}" in active record class "{class}" is specified with an incomplete foreign key. The foreign key must consist of columns referencing both joining tables.',
  1119. array('{class}'=>get_class($parent->model), '{relation}'=>$this->relation->name)));
  1120. }
  1121. }
  1122. /**
  1123. * CJoinQuery represents a JOIN SQL statement.
  1124. *
  1125. * @author Qiang Xue <qiang.xue@gmail.com>
  1126. * @package system.db.ar
  1127. * @since 1.0
  1128. */
  1129. class CJoinQuery
  1130. {
  1131. /**
  1132. * @var array list of column selections
  1133. */
  1134. public $selects=array();
  1135. /**
  1136. * @var boolean whether to select distinct result set
  1137. */
  1138. public $distinct=false;
  1139. /**
  1140. * @var array list of join statement
  1141. */
  1142. public $joins=array();
  1143. /**
  1144. * @var array list of WHERE clauses
  1145. */
  1146. public $conditions=array();
  1147. /**
  1148. * @var array list of ORDER BY clauses
  1149. */
  1150. public $orders=array();
  1151. /**
  1152. * @var array list of GROUP BY clauses
  1153. */
  1154. public $groups=array();
  1155. /**
  1156. * @var array list of HAVING clauses
  1157. */
  1158. public $havings=array();
  1159. /**
  1160. * @var integer row limit
  1161. */
  1162. public $limit=-1;
  1163. /**
  1164. * @var integer row offset
  1165. */
  1166. public $offset=-1;
  1167. /**
  1168. * @var array list of query parameters
  1169. */
  1170. public $params=array();
  1171. /**
  1172. * @var array list of join element IDs (id=>true)
  1173. */
  1174. public $elements=array();
  1175. /**
  1176. * Constructor.
  1177. * @param CJoinElement $joinElement The root join tree.
  1178. * @param CDbCriteria $criteria the query criteria
  1179. */
  1180. public function __construct($joinElement,$criteria=null)
  1181. {
  1182. if($criteria!==null)
  1183. {
  1184. $this->selects[]=$joinElement->getColumnSelect($criteria->select);
  1185. $this->joins[]=$joinElement->getTableNameWithAlias();
  1186. $this->joins[]=$criteria->join;
  1187. $this->conditions[]=$criteria->condition;
  1188. $this->orders[]=$criteria->order;
  1189. $this->groups[]=$criteria->group;
  1190. $this->havings[]=$criteria->having;
  1191. $this->limit=$criteria->limit;
  1192. $this->offset=$criteria->offset;
  1193. $this->params=$criteria->params;
  1194. if(!$this->distinct && $criteria->distinct)
  1195. $this->distinct=true;
  1196. }
  1197. else
  1198. {
  1199. $this->selects[]=$joinElement->getPrimaryKeySelect();
  1200. $this->joins[]=$joinElement->getTableNameWithAlias();
  1201. $this->conditions[]=$joinElement->getPrimaryKeyRange();
  1202. }
  1203. $this->elements[$joinElement->id]=true;
  1204. }
  1205. /**
  1206. * Joins with another join element
  1207. * @param CJoinElement $element the element to be joined
  1208. */
  1209. public function join($element)
  1210. {
  1211. if($element->slave!==null)
  1212. $this->join($element->slave);
  1213. if(!empty($element->relation->select))
  1214. $this->selects[]=$element->getColumnSelect($element->relation->select);
  1215. $this->conditions[]=$element->relation->condition;
  1216. $this->orders[]=$element->relation->order;
  1217. $this->joins[]=$element->getJoinCondition();
  1218. $this->joins[]=$element->relation->join;
  1219. $this->groups[]=$element->relation->group;
  1220. $this->havings[]=$element->relation->having;
  1221. if(is_array($element->relation->params))
  1222. {
  1223. if(is_array($this->params))
  1224. $this->params=array_merge($this->params,$element->relation->params);
  1225. else
  1226. $this->params=$element->relation->params;
  1227. }
  1228. $this->elements[$element->id]=true;
  1229. }
  1230. /**
  1231. * Creates the SQL statement.
  1232. * @param CDbCommandBuilder $builder the command builder
  1233. * @return CDbCommand DB command instance representing the SQL statement
  1234. */
  1235. public function createCommand($builder)
  1236. {
  1237. $sql=($this->distinct ? 'SELECT DISTINCT ':'SELECT ') . implode(', ',$this->selects);
  1238. $sql.=' FROM ' . implode(' ',array_unique($this->joins));
  1239. $conditions=array();
  1240. foreach($this->conditions as $condition)
  1241. if($condition!=='')
  1242. $conditions[]=$condition;
  1243. if($conditions!==array())
  1244. $sql.=' WHERE (' . implode(') AND (',$conditions).')';
  1245. $groups=array();
  1246. foreach($this->groups as $group)
  1247. if($group!=='')
  1248. $groups[]=$group;
  1249. if($groups!==array())
  1250. $sql.=' GROUP BY ' . implode(', ',$groups);
  1251. $havings=array();
  1252. foreach($this->havings as $having)
  1253. if($having!=='')
  1254. $havings[]=$having;
  1255. if($havings!==array())
  1256. $sql.=' HAVING (' . implode(') AND (',$havings).')';
  1257. $orders=array();
  1258. foreach($this->orders as $order)
  1259. if($order!=='')
  1260. $orders[]=$order;
  1261. if($orders!==array())
  1262. $sql.=' ORDER BY ' . implode(', ',$orders);
  1263. $sql=$builder->applyLimit($sql,$this->limit,$this->offset);
  1264. $command=$builder->getDbConnection()->createCommand($sql);
  1265. $builder->bindValues($command,$this->params);
  1266. return $command;
  1267. }
  1268. }
  1269. /**
  1270. * CStatElement represents STAT join element for {@link CActiveFinder}.
  1271. *
  1272. * @author Qiang Xue <qiang.xue@gmail.com>
  1273. * @package system.db.ar
  1274. */
  1275. class CStatElement
  1276. {
  1277. /**
  1278. * @var CActiveRelation the relation represented by this tree node
  1279. */
  1280. public $relation;
  1281. private $_finder;
  1282. private $_parent;
  1283. /**
  1284. * Constructor.
  1285. * @param CActiveFinder $finder the finder
  1286. * @param CStatRelation $relation the STAT relation
  1287. * @param CJoinElement $parent the join element owning this STAT element
  1288. */
  1289. public function __construct($finder,$relation,$parent)
  1290. {
  1291. $this->_finder=$finder;
  1292. $this->_parent=$parent;
  1293. $this->relation=$relation;
  1294. $parent->stats[]=$this;
  1295. }
  1296. /**
  1297. * Performs the STAT query.
  1298. */
  1299. public function query()
  1300. {
  1301. if(preg_match('/^\s*(.*?)\((.*)\)\s*$/',$this->relation->foreignKey,$matches))
  1302. $this->queryManyMany($matches[1],$matches[2]);
  1303. else
  1304. $this->queryOneMany();
  1305. }
  1306. private function queryOneMany()
  1307. {
  1308. $relation=$this->relation;
  1309. $model=$this->_finder->getModel($relation->className);
  1310. $builder=$model->getCommandBuilder();
  1311. $schema=$builder->getSchema();
  1312. $table=$model->getTableSchema();
  1313. $parent=$this->_parent;
  1314. $pkTable=$parent->model->getTableSchema();
  1315. $fks=preg_split('/\s*,\s*/',$relation->foreignKey,-1,PREG_SPLIT_NO_EMPTY);
  1316. if(count($fks)!==count($pkTable->primaryKey))
  1317. throw new CDbException(Yii::t('yii','The relation "{relation}" in active record class "{class}" is specified with an invalid foreign key. The columns in the key must match the primary keys of the table "{table}".',
  1318. array('{class}'=>get_class($parent->model), '{relation}'=>$relation->name, '{table}'=>$pkTable->name)));
  1319. // set up mapping between fk and pk columns
  1320. $map=array(); // pk=>fk
  1321. foreach($fks as $i=>$fk)
  1322. {
  1323. if(!isset($table->columns[$fk]))
  1324. throw new CDbException(Yii::t('yii','The relation "{relation}" in active record class "{class}" is specified with an invalid foreign key "{key}". There is no such column in the table "{table}".',
  1325. array('{class}'=>get_class($parent->model), '{relation}'=>$relation->name, '{key}'=>$fk, '{table}'=>$table->name)));
  1326. if(isset($table->foreignKeys[$fk]))
  1327. {
  1328. list($tableName,$pk)=$table->foreignKeys[$fk];
  1329. if($schema->compareTableNames($pkTable->rawName,$tableName))
  1330. $map[$pk]=$fk;
  1331. else
  1332. throw new CDbException(Yii::t('yii','The relation "{relation}" in active record class "{class}" is specified with a foreign key "{key}" that does not point to the parent table "{table}".',
  1333. array('{class}'=>get_class($parent->model), '{relation}'=>$relation->name, '{key}'=>$fk, '{table}'=>$pkTable->name)));
  1334. }
  1335. else // FK constraints undefined
  1336. {
  1337. if(is_array($pkTable->primaryKey)) // composite PK
  1338. $map[$pkTable->primaryKey[$i]]=$fk;
  1339. else
  1340. $map[$pkTable->primaryKey]=$fk;
  1341. }
  1342. }
  1343. $records=$this->_parent->records;
  1344. $join=empty($relation->join)?'' : ' '.$relation->join;
  1345. $where=empty($relation->condition)?' WHERE ' : ' WHERE ('.$relation->condition.') AND ';
  1346. $group=empty($relation->group)?'' : ', '.$relation->group;
  1347. $having=empty($relation->having)?'' : ' HAVING ('.$relation->having.')';
  1348. $order=empty($relation->order)?'' : ' ORDER BY '.$relation->order;
  1349. $c=$schema->quoteColumnName('c');
  1350. $s=$schema->quoteColumnName('s');
  1351. $tableAlias=$model->getTableAlias(true);
  1352. // generate and perform query
  1353. if(count($fks)===1) // single column FK
  1354. {
  1355. $col=$tableAlias.'.'.$table->columns[$fks[0]]->rawName;
  1356. $sql="SELECT $col AS $c, {$relation->select} AS $s FROM {$table->rawName} ".$tableAlias.$join
  1357. .$where.'('.$builder->createInCondition($table,$fks[0],array_keys($records),$tableAlias.'.').')'
  1358. ." GROUP BY $col".$group
  1359. .$having.$order;
  1360. $command=$builder->getDbConnection()->createCommand($sql);
  1361. if(is_array($relation->params))
  1362. $builder->bindValues($command,$relation->params);
  1363. $stats=array();
  1364. foreach($command->queryAll() as $row)
  1365. $stats[$row['c']]=$row['s'];
  1366. }
  1367. else // composite FK
  1368. {
  1369. $keys=array_keys($records);
  1370. foreach($keys as &$key)
  1371. {
  1372. $key2=unserialize($key);
  1373. $key=array();
  1374. foreach($pkTable->primaryKey as $pk)
  1375. $key[$map[$pk]]=$key2[$pk];
  1376. }
  1377. $cols=array();
  1378. foreach($pkTable->primaryKey as $n=>$pk)
  1379. {
  1380. $name=$tableAlias.'.'.$table->columns[$map[$pk]]->rawName;
  1381. $cols[$name]=$name.' AS '.$schema->quoteColumnName('c'.$n);
  1382. }
  1383. $sql='SELECT '.implode(', ',$cols).", {$relation->select} AS $s FROM {$table->rawName} ".$tableAlias.$join
  1384. .$where.'('.$builder->createInCondition($table,$fks,$keys,$tableAlias.'.').')'
  1385. .' GROUP BY '.implode(', ',array_keys($cols)).$group
  1386. .$having.$order;
  1387. $command=$builder->getDbConnection()->createCommand($sql);
  1388. if(is_array($relation->params))
  1389. $builder->bindValues($command,$relation->params);
  1390. $stats=array();
  1391. foreach($command->queryAll() as $row)
  1392. {
  1393. $key=array();
  1394. foreach($pkTable->primaryKey as $n=>$pk)
  1395. $key[$pk]=$row['c'.$n];
  1396. $stats[serialize($key)]=$row['s'];
  1397. }
  1398. }
  1399. // populate the results into existing records
  1400. foreach($records as $pk=>$record)
  1401. $record->addRelatedRecord($relation->name,isset($stats[$pk])?$stats[$pk]:$relation->defaultValue,false);
  1402. }
  1403. /**
  1404. * @param string $joinTableName jointablename
  1405. * @param string $keys keys
  1406. * @throws CDbException
  1407. */
  1408. private function queryManyMany($joinTableName,$keys)
  1409. {
  1410. $relation=$this->relation;
  1411. $model=$this->_finder->getModel($relation->className);
  1412. $table=$model->getTableSchema();
  1413. $builder=$model->getCommandBuilder();
  1414. $schema=$builder->getSchema();
  1415. $pkTable=$this->_parent->model->getTableSchema();
  1416. $tableAlias=$model->getTableAlias(true);
  1417. if(($joinTable=$builder->getSchema()->getTable($joinTableName))===null)
  1418. throw new CDbException(Yii::t('yii','The relation "{relation}" in active record class "{class}" is not specified correctly: the join table "{joinTable}" given in the foreign key cannot be found in the database.',
  1419. array('{class}'=>get_class($this->_parent->model), '{relation}'=>$relation->name, '{joinTable}'=>$joinTableName)));
  1420. $fks=preg_split('/\s*,\s*/',$keys,-1,PREG_SPLIT_NO_EMPTY);
  1421. if(count($fks)!==count($table->primaryKey)+count($pkTable->primaryKey))
  1422. throw new CDbException(Yii::t('yii','The relation "{relation}" in active record class "{class}" is specified with an incomplete foreign key. The foreign key must consist of columns referencing both joining tables.',
  1423. array('{class}'=>get_class($this->_parent->model), '{relation}'=>$relation->name)));
  1424. $joinCondition=array();
  1425. $map=array();
  1426. $fkDefined=true;
  1427. foreach($fks as $i=>$fk)
  1428. {
  1429. if(!isset($joinTable->columns[$fk]))
  1430. throw new CDbException(Yii::t('yii','The relation "{relation}" in active record class "{class}" is specified with an invalid foreign key "{key}". There is no such column in the table "{table}".',
  1431. array('{class}'=>get_class($this->_parent->model), '{relation}'=>$relation->name, '{key}'=>$fk, '{table}'=>$joinTable->name)));
  1432. if(isset($joinTable->foreignKeys[$fk]))
  1433. {
  1434. list($tableName,$pk)=$joinTable->foreignKeys[$fk];
  1435. if(!isset($joinCondition[$pk]) && $schema->compareTableNames($table->rawName,$tableName))
  1436. $joinCondition[$pk]=$tableAlias.'.'.$schema->quoteColumnName($pk).'='.$joinTable->rawName.'.'.$schema->quoteColumnName($fk);
  1437. elseif(!isset($map[$pk]) && $schema->compareTableNames($pkTable->rawName,$tableName))
  1438. $map[$pk]=$fk;
  1439. else
  1440. {
  1441. $fkDefined=false;
  1442. break;
  1443. }
  1444. }
  1445. else
  1446. {
  1447. $fkDefined=false;
  1448. break;
  1449. }
  1450. }
  1451. if(!$fkDefined)
  1452. {
  1453. $joinCondition=array();
  1454. $map=array();
  1455. foreach($fks as $i=>$fk)
  1456. {
  1457. if($i<count($pkTable->primaryKey))
  1458. {
  1459. $pk=is_array($pkTable->primaryKey) ? $pkTable->primaryKey[$i] : $pkTable->primaryKey;
  1460. $map[$pk]=$fk;
  1461. }
  1462. else

Large files files are truncated, but you can click here to view the full file