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

/demo/yii/db/ar/CActiveFinder.php

https://bitbucket.org/quarkmarino/yii-bootstrap
PHP | 1615 lines | 1191 code | 133 blank | 291 comment | 200 complexity | 982b336304d25b5c72229b2df1d2b349 MD5 | raw file
Possible License(s): GPL-3.0, LGPL-2.1, BSD-2-Clause, BSD-3-Clause

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

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

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