PageRenderTime 58ms CodeModel.GetById 25ms RepoModel.GetById 1ms app.codeStats 0ms

/packages/yii-1.1.7.r3135/framework/db/ar/CActiveFinder.php

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

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