Changeset 727

Timestamp:

01/04/08 13:06:21 (1 year ago)

Author:

laurentj

Message:

fixed bug #398: jDao, bad generated query when calling countBy. order clause is not allowed here, and a WHERE keyword is generated even if conditions are empty

Files:

trunk/lib/jelix/dao/jDaoConditions.class.php (modified) (2 diffs)
trunk/lib/jelix/dao/jDaoConditions.class.php (modified) (2 diffs)
trunk/lib/jelix/dao/jDaoConditions.class.php (modified) (2 diffs)
trunk/lib/jelix/dao/jDaoFactoryBase.class.php (modified) (3 diffs)
trunk/lib/jelix/dao/jDaoFactoryBase.class.php (modified) (3 diffs)
trunk/lib/jelix/dao/jDaoFactoryBase.class.php (modified) (3 diffs)
trunk/testapp/modules/jelix_tests/tests/jdao.main_api.html.php (modified) (1 diff)
trunk/testapp/modules/jelix_tests/tests/jdao.main_api.html.php (modified) (1 diff)
trunk/testapp/modules/jelix_tests/tests/jdao.main_api.html.php (modified) (1 diff)

Legend:

: Unmodified
: Added
: Removed
: Modified
: Copied
: Moved

trunk/lib/jelix/dao/jDaoConditions.class.php

r705	r727
87	87
88	88	/**
89		* says if the ~~condition is empty~~
	89	* says if there are no conditions nor order
90	90	* @return boolean false if there isn't condition
91	91	*/
…	…
94	94	(count ($this->condition->conditions) == 0) &&
95	95	(count ($this->order) == 0) ;
	96	}
	97
	98	/**
	99	* says if there are no conditions
	100	* @return boolean false if there isn't condition
	101	* @since 1.0
	102	*/
	103	function hasConditions (){
	104	return (count ($this->condition->group) \|\| count ($this->condition->conditions));
96	105	}
97	106

trunk/lib/jelix/dao/jDaoConditions.class.php

r705	r727
87	87
88	88	/**
89		* says if the ~~condition is empty~~
	89	* says if there are no conditions nor order
90	90	* @return boolean false if there isn't condition
91	91	*/
…	…
94	94	(count ($this->condition->conditions) == 0) &&
95	95	(count ($this->order) == 0) ;
	96	}
	97
	98	/**
	99	* says if there are no conditions
	100	* @return boolean false if there isn't condition
	101	* @since 1.0
	102	*/
	103	function hasConditions (){
	104	return (count ($this->condition->group) \|\| count ($this->condition->conditions));
96	105	}
97	106

trunk/lib/jelix/dao/jDaoConditions.class.php

r705	r727
87	87
88	88	/**
89		* says if the ~~condition is empty~~
	89	* says if there are no conditions nor order
90	90	* @return boolean false if there isn't condition
91	91	*/
…	…
94	94	(count ($this->condition->conditions) == 0) &&
95	95	(count ($this->order) == 0) ;
	96	}
	97
	98	/**
	99	* says if there are no conditions
	100	* @return boolean false if there isn't condition
	101	* @since 1.0
	102	*/
	103	function hasConditions (){
	104	return (count ($this->condition->group) \|\| count ($this->condition->conditions));
96	105	}
97	106

trunk/lib/jelix/dao/jDaoFactoryBase.class.php

r701	r727
239	239	final public function findBy ($searchcond, $limitOffset=0, $limitCount=0){
240	240	$query = $this->_selectClause.$this->_fromClause.$this->_whereClause;
241		if (~~!$searchcond->isEmpty~~ ()){
	241	if ($searchcond->hasConditions ()){
242	242	$query .= ($this->_whereClause !='' ? ' AND ' : ' WHERE ');
243	243	$query .= $this->_createConditionsClause($searchcond);
244	244	}
	245	$query.= $this->_createOrderClause($searchcond);
245	246
246	247	if($limitCount != 0){
…	…
264	265	final public function countBy($searchcond) {
265	266	$query = 'SELECT COUNT(*) as c '.$this->_fromClause.$this->_whereClause;
266		if (~~!$searchcond->isEmpty~~ ()){
	267	if ($searchcond->hasConditions ()){
267	268	$query .= ($this->_whereClause !='' ? ' AND ' : ' WHERE ');
268	269	$query .= $this->_createConditionsClause($searchcond);
…	…
320	321	final protected function _createConditionsClause($daocond, $forSelect=true){
321	322	$props = $this->getProperties();
322		$sql = $this->_generateCondition ($daocond->condition, $props, $forSelect, true);
323
324		if($forSelect){
325		$order = array ();
326		foreach ($daocond->order as $name => $way){
327		if (isset($props[$name])){
328		$order[] = $name.' '.$way;
329		}
	323	return $this->_generateCondition ($daocond->condition, $props, $forSelect, true);
	324	}
	325
	326	/**
	327	* @internal
	328	*/
	329	final protected function _createOrderClause($daocond) {
	330	$order = array ();
	331	$props =$this->getProperties();
	332	foreach ($daocond->order as $name => $way){
	333	if (isset($props[$name])){
	334	$order[] = $name.' '.$way;
330	335	}
331		if(count ($order) > 0){
332		if(trim($sql) =='') {
333		$sql.= ' 1=1 ';
334		}
335		$sql.=' ORDER BY '.implode (', ', $order);
336		}
337		}
338		return $sql;
	336	}
	337
	338	if(count ($order)){
	339	return ' ORDER BY '.implode (', ', $order);
	340	}
	341	return '';
339	342	}
340	343

trunk/lib/jelix/dao/jDaoFactoryBase.class.php

r701	r727
239	239	final public function findBy ($searchcond, $limitOffset=0, $limitCount=0){
240	240	$query = $this->_selectClause.$this->_fromClause.$this->_whereClause;
241		if (~~!$searchcond->isEmpty~~ ()){
	241	if ($searchcond->hasConditions ()){
242	242	$query .= ($this->_whereClause !='' ? ' AND ' : ' WHERE ');
243	243	$query .= $this->_createConditionsClause($searchcond);
244	244	}
	245	$query.= $this->_createOrderClause($searchcond);
245	246
246	247	if($limitCount != 0){
…	…
264	265	final public function countBy($searchcond) {
265	266	$query = 'SELECT COUNT(*) as c '.$this->_fromClause.$this->_whereClause;
266		if (~~!$searchcond->isEmpty~~ ()){
	267	if ($searchcond->hasConditions ()){
267	268	$query .= ($this->_whereClause !='' ? ' AND ' : ' WHERE ');
268	269	$query .= $this->_createConditionsClause($searchcond);
…	…
320	321	final protected function _createConditionsClause($daocond, $forSelect=true){
321	322	$props = $this->getProperties();
322		$sql = $this->_generateCondition ($daocond->condition, $props, $forSelect, true);
323
324		if($forSelect){
325		$order = array ();
326		foreach ($daocond->order as $name => $way){
327		if (isset($props[$name])){
328		$order[] = $name.' '.$way;
329		}
	323	return $this->_generateCondition ($daocond->condition, $props, $forSelect, true);
	324	}
	325
	326	/**
	327	* @internal
	328	*/
	329	final protected function _createOrderClause($daocond) {
	330	$order = array ();
	331	$props =$this->getProperties();
	332	foreach ($daocond->order as $name => $way){
	333	if (isset($props[$name])){
	334	$order[] = $name.' '.$way;
330	335	}
331		if(count ($order) > 0){
332		if(trim($sql) =='') {
333		$sql.= ' 1=1 ';
334		}
335		$sql.=' ORDER BY '.implode (', ', $order);
336		}
337		}
338		return $sql;
	336	}
	337
	338	if(count ($order)){
	339	return ' ORDER BY '.implode (', ', $order);
	340	}
	341	return '';
339	342	}
340	343

trunk/lib/jelix/dao/jDaoFactoryBase.class.php

r701	r727
239	239	final public function findBy ($searchcond, $limitOffset=0, $limitCount=0){
240	240	$query = $this->_selectClause.$this->_fromClause.$this->_whereClause;
241		if (~~!$searchcond->isEmpty~~ ()){
	241	if ($searchcond->hasConditions ()){
242	242	$query .= ($this->_whereClause !='' ? ' AND ' : ' WHERE ');
243	243	$query .= $this->_createConditionsClause($searchcond);
244	244	}
	245	$query.= $this->_createOrderClause($searchcond);
245	246
246	247	if($limitCount != 0){
…	…
264	265	final public function countBy($searchcond) {
265	266	$query = 'SELECT COUNT(*) as c '.$this->_fromClause.$this->_whereClause;
266		if (~~!$searchcond->isEmpty~~ ()){
	267	if ($searchcond->hasConditions ()){
267	268	$query .= ($this->_whereClause !='' ? ' AND ' : ' WHERE ');
268	269	$query .= $this->_createConditionsClause($searchcond);
…	…
320	321	final protected function _createConditionsClause($daocond, $forSelect=true){
321	322	$props = $this->getProperties();
322		$sql = $this->_generateCondition ($daocond->condition, $props, $forSelect, true);
323
324		if($forSelect){
325		$order = array ();
326		foreach ($daocond->order as $name => $way){
327		if (isset($props[$name])){
328		$order[] = $name.' '.$way;
329		}
	323	return $this->_generateCondition ($daocond->condition, $props, $forSelect, true);
	324	}
	325
	326	/**
	327	* @internal
	328	*/
	329	final protected function _createOrderClause($daocond) {
	330	$order = array ();
	331	$props =$this->getProperties();
	332	foreach ($daocond->order as $name => $way){
	333	if (isset($props[$name])){
	334	$order[] = $name.' '.$way;
330	335	}
331		if(count ($order) > 0){
332		if(trim($sql) =='') {
333		$sql.= ' 1=1 ';
334		}
335		$sql.=' ORDER BY '.implode (', ', $order);
336		}
337		}
338		return $sql;
	336	}
	337
	338	if(count ($order)){
	339	return ' ORDER BY '.implode (', ', $order);
	340	}
	341	return '';
339	342	}
340	343

trunk/testapp/modules/jelix_tests/tests/jdao.main_api.html.php

r697	r727
178	178	}
179	179
	180
	181	function testFindByCountByOrder(){
	182	$dao = jDao::create ('products');
	183
	184	$conditions = jDao::createConditions();
	185	$conditions->addItemOrder('id','DESC');
	186
	187	$count = $dao->countBy($conditions);
	188	$this->assertEqual($count, 3, 'countBy: %s');
	189
	190	$res = $dao->findBy($conditions);
	191	$list = array();
	192	foreach($res as $r){
	193	$list[] = $r;
	194	}
	195	$this->assertEqual(count($list), 3, 'findBy doesn\'t return all products. %s ');
	196
	197	$verif='<array>
	198	<object>
	199	<string property="id" value="'.$this->prod3->id.'" />
	200	<string property="name" value="verre" />
	201	<string property="price" value="2.43" />
	202	</object>
	203	<object>
	204	<string property="id" value="'.$this->prod2->id.'" />
	205	<string property="name" value="fourchette" />
	206	<string property="price" value="1.54" />
	207	</object>
	208	<object>
	209	<string property="id" value="'.$this->prod1->id.'" />
	210	<string property="name" value="assiette nouvelle" />
	211	<string property="price" value="5.90" />
	212	</object>
	213	</array>';
	214	$this->assertComplexIdenticalStr($list, $verif);
	215	}
	216
	217	function testFindByCountByConditionsOrder(){
	218	$dao = jDao::create ('products');
	219
	220	$conditions = jDao::createConditions();
	221	$conditions->addItemOrder('id','DESC');
	222	$conditions->addCondition ('id', '>=', $this->prod2->id);
	223
	224	$count = $dao->countBy($conditions);
	225	$this->assertEqual($count, 2, 'countBy: %s');
	226
	227	$res = $dao->findBy($conditions);
	228	$list = array();
	229	foreach($res as $r){
	230	$list[] = $r;
	231	}
	232	$this->assertEqual(count($list), 2, 'findBy doesn\'t return all products. %s ');
	233
	234	$verif='<array>
	235	<object>
	236	<string property="id" value="'.$this->prod3->id.'" />
	237	<string property="name" value="verre" />
	238	<string property="price" value="2.43" />
	239	</object>
	240	<object>
	241	<string property="id" value="'.$this->prod2->id.'" />
	242	<string property="name" value="fourchette" />
	243	<string property="price" value="1.54" />
	244	</object>
	245	</array>';
	246	$this->assertComplexIdenticalStr($list, $verif);
	247	}
	248
	249
180	250	function testDelete(){
181	251	$dao = jDao::create ('products');

trunk/testapp/modules/jelix_tests/tests/jdao.main_api.html.php

r697	r727
178	178	}
179	179
	180
	181	function testFindByCountByOrder(){
	182	$dao = jDao::create ('products');
	183
	184	$conditions = jDao::createConditions();
	185	$conditions->addItemOrder('id','DESC');
	186
	187	$count = $dao->countBy($conditions);
	188	$this->assertEqual($count, 3, 'countBy: %s');
	189
	190	$res = $dao->findBy($conditions);
	191	$list = array();
	192	foreach($res as $r){
	193	$list[] = $r;
	194	}
	195	$this->assertEqual(count($list), 3, 'findBy doesn\'t return all products. %s ');
	196
	197	$verif='<array>
	198	<object>
	199	<string property="id" value="'.$this->prod3->id.'" />
	200	<string property="name" value="verre" />
	201	<string property="price" value="2.43" />
	202	</object>
	203	<object>
	204	<string property="id" value="'.$this->prod2->id.'" />
	205	<string property="name" value="fourchette" />
	206	<string property="price" value="1.54" />
	207	</object>
	208	<object>
	209	<string property="id" value="'.$this->prod1->id.'" />
	210	<string property="name" value="assiette nouvelle" />
	211	<string property="price" value="5.90" />
	212	</object>
	213	</array>';
	214	$this->assertComplexIdenticalStr($list, $verif);
	215	}
	216
	217	function testFindByCountByConditionsOrder(){
	218	$dao = jDao::create ('products');
	219
	220	$conditions = jDao::createConditions();
	221	$conditions->addItemOrder('id','DESC');
	222	$conditions->addCondition ('id', '>=', $this->prod2->id);
	223
	224	$count = $dao->countBy($conditions);
	225	$this->assertEqual($count, 2, 'countBy: %s');
	226
	227	$res = $dao->findBy($conditions);
	228	$list = array();
	229	foreach($res as $r){
	230	$list[] = $r;
	231	}
	232	$this->assertEqual(count($list), 2, 'findBy doesn\'t return all products. %s ');
	233
	234	$verif='<array>
	235	<object>
	236	<string property="id" value="'.$this->prod3->id.'" />
	237	<string property="name" value="verre" />
	238	<string property="price" value="2.43" />
	239	</object>
	240	<object>
	241	<string property="id" value="'.$this->prod2->id.'" />
	242	<string property="name" value="fourchette" />
	243	<string property="price" value="1.54" />
	244	</object>
	245	</array>';
	246	$this->assertComplexIdenticalStr($list, $verif);
	247	}
	248
	249
180	250	function testDelete(){
181	251	$dao = jDao::create ('products');

trunk/testapp/modules/jelix_tests/tests/jdao.main_api.html.php

r697	r727
178	178	}
179	179
	180
	181	function testFindByCountByOrder(){
	182	$dao = jDao::create ('products');
	183
	184	$conditions = jDao::createConditions();
	185	$conditions->addItemOrder('id','DESC');
	186
	187	$count = $dao->countBy($conditions);
	188	$this->assertEqual($count, 3, 'countBy: %s');
	189
	190	$res = $dao->findBy($conditions);
	191	$list = array();
	192	foreach($res as $r){
	193	$list[] = $r;
	194	}
	195	$this->assertEqual(count($list), 3, 'findBy doesn\'t return all products. %s ');
	196
	197	$verif='<array>
	198	<object>
	199	<string property="id" value="'.$this->prod3->id.'" />
	200	<string property="name" value="verre" />
	201	<string property="price" value="2.43" />
	202	</object>
	203	<object>
	204	<string property="id" value="'.$this->prod2->id.'" />
	205	<string property="name" value="fourchette" />
	206	<string property="price" value="1.54" />
	207	</object>
	208	<object>
	209	<string property="id" value="'.$this->prod1->id.'" />
	210	<string property="name" value="assiette nouvelle" />
	211	<string property="price" value="5.90" />
	212	</object>
	213	</array>';
	214	$this->assertComplexIdenticalStr($list, $verif);
	215	}
	216
	217	function testFindByCountByConditionsOrder(){
	218	$dao = jDao::create ('products');
	219
	220	$conditions = jDao::createConditions();
	221	$conditions->addItemOrder('id','DESC');
	222	$conditions->addCondition ('id', '>=', $this->prod2->id);
	223
	224	$count = $dao->countBy($conditions);
	225	$this->assertEqual($count, 2, 'countBy: %s');
	226
	227	$res = $dao->findBy($conditions);
	228	$list = array();
	229	foreach($res as $r){
	230	$list[] = $r;
	231	}
	232	$this->assertEqual(count($list), 2, 'findBy doesn\'t return all products. %s ');
	233
	234	$verif='<array>
	235	<object>
	236	<string property="id" value="'.$this->prod3->id.'" />
	237	<string property="name" value="verre" />
	238	<string property="price" value="2.43" />
	239	</object>
	240	<object>
	241	<string property="id" value="'.$this->prod2->id.'" />
	242	<string property="name" value="fourchette" />
	243	<string property="price" value="1.54" />
	244	</object>
	245	</array>';
	246	$this->assertComplexIdenticalStr($list, $verif);
	247	}
	248
	249
180	250	function testDelete(){
181	251	$dao = jDao::create ('products');

Download in other formats: Unified Diff Zip Archive

Developers' Corner

Changeset 727

Legend: