Active Record now supports query criteria for implicitly declared related properties

5 files changed, 103 insertions, 43 deletions

diff --git a/HISTORY b/HISTORY
index 1fc3bcca..a7b3f08e 100644
--- a/HISTORY
+++ b/HISTORY
@@ -9,6 +9,7 @@ ENH: Ticket#722 - Add Read Only capabilities to TInPlaceTextBox (Christophe)
 ENH: Active Record supports multiple foreign references of the same table (Wei)
 ENH: Added TDbCommand.queryColumn() (Qiang)
 ENH: Active Record now supports implicitly declared related properties (Qiang)
+ENH: Active Record now supports query criteria for implicitly declared related properties (Qiang)
 NEW: Added TDbLogRoute (Qiang)
 NEW: Added TDataRenderer and TItemDataRenderer (Qiang)
 NEW: Ticket#544 - Added TXCache (Wei)
diff --git a/demos/quickstart/protected/pages/Database/ActiveRecord.page b/demos/quickstart/protected/pages/Database/ActiveRecord.page
index 28beaa93..95ce96c7 100644
--- a/demos/quickstart/protected/pages/Database/ActiveRecord.page
+++ b/demos/quickstart/protected/pages/Database/ActiveRecord.page
@@ -612,7 +612,8 @@ CREATE TABLE bar
 as an 1-M relationship. That is, one Team may contain 0 or more Players. In terms of
 object relationships, we say that a <tt>TeamRecord</tt> object <b>has many</b> <tt>PlayerRecord</tt> objects.
 (Notice the reversal of the direction of relationships between tables and objects.)
-<p id="710019" class="block-content">
+</p>
+
 <h3 id="142017">Has Many Relationship</h3>
 <p id="710020" class="block-content">
 We model the <tt>Team</tt> object as the following Active Record classes.
@@ -629,7 +630,7 @@ class TeamRecord extends TActiveRecord
     //define the $player member having has many relationship with PlayerRecord
     public static $RELATIONS=array
     (
-        'players' => array(self::HAS_MANY, 'PlayerRecord'),
+        'players' => array(self::HAS_MANY, 'PlayerRecord', 'team_name'),
     );
 
     public static function finder($className=__CLASS__)
@@ -644,10 +645,12 @@ property <tt>$players</tt> <b>has many</b> <tt>PlayerRecord</tt>s. Multiple rela
 is permitted by defining each relationship with an entry in the <tt>$RELATIONS</tt>
 array where array key for the entry corresponds to the property name.
 In <tt>array(self::HAS_MANY, 'PlayerRecord')</tt>, the first element defines the
-relationship type, the valid types are <tt>self::HAS_MANY</tt>,
-<tt>self::HAS_ONE</tt> and <tt>self::BELONGS_TO</tt>.
+relationship type, the valid types are <tt>self::HAS_MANY</tt>, <tt>self::HAS_ONE</tt>,
+<tt>self::BELONGS_TO</tt> and <tt>self::MANY_TO_MANY</tt>.
 The second element is a string <tt>'PlayerRecord'</tt> that corresponds to the
 class name of the <tt>PlayerRecord</tt> class.
+And the third element 'team_name' refers to the foreign key column in the Players table that
+references to the Teams table.
 </p>
 
 <div class="note"><b class="note">Note:</b>
@@ -670,8 +673,8 @@ in Active Record by inspecting the <tt>Players</tt> and <tt>Teams</tt> table def
 Since version <b>3.1.2</b>, Active Record supports multiple foreign key
 references of the same table. Ambiguity between multiple foreign key references to the same table is
 resolved by providing the foreign key column name as the 3rd parameter in the relationship array.
-For example, both the following foreign keys <tt>owner_id</tt> and <tt>reporter_id</tt>
-references the same table defined in <tt>UserRecord</tt>.
+For example, both of the following foreign keys <tt>owner_id</tt> and <tt>reporter_id</tt>
+references to the same table defined in <tt>UserRecord</tt>.
 <com:TTextHighlighter Language="php" CssClass="source block-content">
 class TicketRecord extends TActiveRecord
 {
@@ -729,6 +732,13 @@ load the related objects, while the <tt>with</tt> approach is more efficient if
 returned, each with some related objects.
 </div>
 
+<h3 id="142019">Has One Relationship</h3>
+<p id="710030" class="block-content">The entity relationship between <tt>Players</tt> and <tt>Profiles</tt> is one to one. That is,
+each <tt>PlayerRecord</tt> object <b>has one</b> <tt>ProfileRecord</tt> object (may be none or null).
+A <b>has one</b> relationship is nearly identical to a <b>has many</b> relationship with the exception
+that the related object is only one object (not a collection of objects).
+</p>
+
 <h3 id="142018">Belongs To Relationship</h3>
 <p id="710025" class="block-content">The "has many" relationship in the above section defines a collection of foreign
 objects. In particular, we have that a <tt>TeamRecord</tt> has many (zero or more)
@@ -752,9 +762,9 @@ class PlayerRecord extends TActiveRecord
 
     public static $RELATIONS=array
     (
-        'team' => array(self::BELONGS_TO, 'TeamRecord'),
+        'team' => array(self::BELONGS_TO, 'TeamRecord', 'team_name'),
         'skills' => array(self::MANY_TO_MANY, 'SkillRecord', 'Player_Skills'),
-        'profile' => array(self::HAS_ONE, 'ProfileRecord'),
+        'profile' => array(self::HAS_ONE, 'ProfileRecord', 'player_id'),
     );
 
     public static function finder($className=__CLASS__)
@@ -768,10 +778,11 @@ The static <tt>$RELATIONS</tt> property of <tt>PlayerRecord</tt> defines that th
 property <tt>$team</tt> <b>belongs to</b> a <tt>TeamRecord</tt>.
 The <tt>$RELATIONS</tt> array also defines two other relationships that we
 shall examine in later sections below.
-In <tt>array(self::BELONGS_TO, 'TeamRecord')</tt>, the first element defines the
-relationship type, in this case <strong><tt>self::BELONGS_TO</tt></strong> and
+In <tt>array(self::BELONGS_TO, 'TeamRecord', 'team_name')</tt>, the first element defines the
+relationship type, in this case <strong><tt>self::BELONGS_TO</tt></strong>;
 the second element is a string <tt>'TeamRecord'</tt> that corresponds to the
-class name of the <tt>TeamRecord</tt> class.
+class name of the <tt>TeamRecord</tt> class; and the third element 'team_name' refers
+to the foreign key of Players referencing Teams.
 A player object with the corresponding team object may be fetched as follows.
 </p>
 <com:TTextHighlighter Language="php" CssClass="source block-content">
@@ -830,13 +841,6 @@ Thus, the <tt>PlayerRecord</tt> object has a property (<tt>$team</tt>) that <b>b
 <tt>TeamRecord</tt> object.
 </p>
 
-<h3 id="142019">Has One Relationship</h3>
-<p id="710030" class="block-content">The entity relationship between <tt>Players</tt> and <tt>Profiles</tt> is one to one. That is,
-each <tt>PlayerRecord</tt> object <b>has one</b> <tt>ProfileRecord</tt> object (may be none or null).
-A <b>has one</b> relationship is nearly identical to a <b>has many</b> relationship with the exception
-that the related object is only one object (not a collection of objects).
-</p>
-
 <h3 id="142020">Parent Child Relationships</h3>
 <p id="710031" class="block-content">A parent child relationship can be defined using a combination of <tt>has many</tt> and <tt>belongs to</tt>
 relationship that refers to the same class. The following example shows a parent children relationship between
@@ -855,12 +859,28 @@ class Category extends TActiveRecord
 
     public static $RELATIONS=array
     (
-        'parent_category' => array(self::BELONGS_TO, 'Category'),
-        'child_categories' => array(self::HAS_MANY, 'Category'),
+        'parent_category' => array(self::BELONGS_TO, 'Category', 'parent_cat_id'),
+        'child_categories' => array(self::HAS_MANY, 'Category', 'parent_cat_id'),
     );
 }
 </com:TTextHighlighter>
 
+<h3>Query Criteria for Related Objects</h3>
+<p>
+In the above, we show that an Active Record object can reference to its related objects by
+declaring a static class member $RELATIONS which specifies a list of relations. Each relation
+is specified as an array consisting of three elements: relation type, related AR class name,
+and the foreign key(s). For example, we use <tt>array(self::HAS_MANY, 'PlayerRecord', 'team_name')</tt>
+to specify the players in a team. There are two more optional elements that can be specified
+in this array: query condition (the fourth element) and parameters (the fifth element).
+They are used to control how to query for the related objects. For example, if we want to obtain
+the players ordered by their age, we can specify <tt>array(self::HAS_MANY, 'PlayerRecord', 'team_name', 'ORDER BY age')</tt>.
+If we want to obtain players whose age is smaller than 30, we could use
+<tt>array(self::HAS_MANY, 'PlayerRecord', 'team_name', 'age<:age', array(':age'=>30))</tt>. In general,
+these two additional elements are similar as the parameters passed to the <tt>find()</tt> method in AR.
+</p>
+
+
 
 <h2 id="142013">Association Table Mapping</h2>
 <p id="710032" class="block-content">
diff --git a/framework/Data/ActiveRecord/Relations/TActiveRecordRelationContext.php b/framework/Data/ActiveRecord/Relations/TActiveRecordRelationContext.php
index 28d80683..ff6d9554 100644
--- a/framework/Data/ActiveRecord/Relations/TActiveRecordRelationContext.php
+++ b/framework/Data/ActiveRecord/Relations/TActiveRecordRelationContext.php
@@ -69,14 +69,6 @@ class TActiveRecordRelationContext
 	}

 

 	/**

-	 * @return string foreign record class name.

-	 */

-	public function getForeignRecordClass()

-	{

-		return $this->_relation[1];

-	}

-

-	/**

 	 * @return array foreign key of this relations, the keys is dependent on the

 	 * relationship type.

 	 * @since 3.1.2

@@ -97,6 +89,14 @@ class TActiveRecordRelationContext
 	}

 

 	/**

+	 * @return string foreign record class name.

+	 */

+	public function getForeignRecordClass()

+	{

+		return $this->_relation[1];

+	}

+

+	/**

 	 * @return string foreign key field names, comma delimited.

 	 * @since 3.1.2

 	 */

@@ -106,6 +106,24 @@ class TActiveRecordRelationContext
 	}

 

 	/**

+	 * @return string the query condition for the relation as specified in RELATIONS

+	 * @since 3.1.2

+	 */

+	public function getCondition()

+	{

+		return isset($this->_relation[3])?$this->_relation[3]:null;

+	}

+

+	/**

+	 * @return array the query parameters for the relation as specified in RELATIONS

+	 * @since 3.1.2

+	 */

+	public function getParameters()

+	{

+		return isset($this->_relation[4])?$this->_relation[4]:array();

+	}

+

+	/**

 	 * @return boolean true if the 3rd element of an TActiveRecord::$RELATION entry is set.

 	 * @since 3.1.2

 	 */

@@ -156,7 +174,7 @@ class TActiveRecordRelationContext
 				$this->_property, get_class($this->_record), 'RELATIONS');

 		}

 		if($criteria===null)

-			$criteria = new TActiveRecordCriteria;

+			$criteria = new TActiveRecordCriteria($this->getCondition(), $this->getParameters());

 		switch($this->getRelationType())

 		{

 			case TActiveRecord::HAS_MANY:

diff --git a/framework/Data/Common/TDbCommandBuilder.php b/framework/Data/Common/TDbCommandBuilder.php
index 0029b4c3..f358036c 100644
--- a/framework/Data/Common/TDbCommandBuilder.php
+++ b/framework/Data/Common/TDbCommandBuilder.php
@@ -159,10 +159,10 @@ class TDbCommandBuilder extends TComponent
 	 */

 	public function createFindCommand($where='1=1', $parameters=array(), $ordering=array(), $limit=-1, $offset=-1)

 	{

-		if($where===null)

-			$where='1=1';

 		$table = $this->getTableInfo()->getTableFullName();

-		$sql = "SELECT * FROM {$table} WHERE {$where}";

+		$sql = "SELECT * FROM {$table}";

+		if(!empty($where))

+			$sql .= " WHERE {$where}";

 		return $this->applyCriterias($sql, $parameters, $ordering, $limit, $offset);

 	}

 

@@ -185,10 +185,10 @@ class TDbCommandBuilder extends TComponent
 	 */

 	public function createCountCommand($where='1=1', $parameters=array(),$ordering=array(), $limit=-1, $offset=-1)

 	{

-		if($where===null)

-			$where='1=1';

 		$table = $this->getTableInfo()->getTableFullName();

-		$sql = "SELECT COUNT(*) FROM {$table} WHERE {$where}";

+		$sql = "SELECT COUNT(*) FROM {$table}";

+		if(!empty($where))

+			$sql .= " WHERE {$where}";

 		return $this->applyCriterias($sql, $parameters, $ordering, $limit, $offset);

 	}

 

@@ -204,8 +204,8 @@ class TDbCommandBuilder extends TComponent
 	{

 		$table = $this->getTableInfo()->getTableFullName();

 		if (!empty($where))

-			$where = 'WHERE '.$where;

-		$command = $this->createCommand("DELETE FROM {$table} ".$where);

+			$where = ' WHERE '.$where;

+		$command = $this->createCommand("DELETE FROM {$table}".$where);

 		$this->bindArrayValues($command, $parameters);

 		return $command;

 	}

@@ -242,7 +242,10 @@ class TDbCommandBuilder extends TComponent
 			$fields = implode(', ', $this->getColumnBindings($data, true));

 		else

 			$fields = implode(', ', $this->getColumnBindings($data));

-		$command = $this->createCommand("UPDATE {$table} SET {$fields} WHERE {$where}");

+

+		if (!empty($where))

+			$where = ' WHERE '.$where;

+		$command = $this->createCommand("UPDATE {$table} SET {$fields}".$where);

 		$this->bindArrayValues($command, array_merge($data, $parameters));

 		return $command;

 	}

diff --git a/framework/Data/DataGateway/TSqlCriteria.php b/framework/Data/DataGateway/TSqlCriteria.php
index d073cd10..a17ac0c6 100644
--- a/framework/Data/DataGateway/TSqlCriteria.php
+++ b/framework/Data/DataGateway/TSqlCriteria.php
@@ -51,6 +51,7 @@ class TSqlCriteria extends TComponent
 		$this->_parameters->copyFrom((array)$parameters);

 		$this->_ordersBy=new TAttributeCollection;

 		$this->_ordersBy->setCaseSensitive(true);

+

 		$this->setCondition($condition);

 	}

 

@@ -68,7 +69,14 @@ class TSqlCriteria extends TComponent
 	 */

 	public function setCondition($value)

 	{

-		$this->_condition=$value;

+		if(!empty($value) && preg_match('/ORDER\s+BY\s+(.*?)$/i',$value,$matches)>0)

+		{

+			// condition contains ORDER BY, we need to strip it output

+			$this->_condition=substr($value,0,strpos($value,$matches[0]));

+			$this->setOrdersBy($matches[1]);

+		}

+		else

+			$this->_condition=$value;

 	}

 

 	/**

@@ -107,13 +115,23 @@ class TSqlCriteria extends TComponent
 	}

 

 	/**

-	 * @param ArrayAccess ordering clause.

+	 * @param mixed ordering clause.

 	 */

 	public function setOrdersBy($value)

 	{

-		if(!(is_array($value) || $value instanceof ArrayAccess))

-			throw new TException('value must be array or ArrayAccess');

-		$this->_ordersBy->copyFrom($value);

+		if(is_array($value) || $value instanceof Traversable)

+			$this->_ordersBy->copyFrom($value);

+		else

+		{

+			$value=trim(preg_replace('/\s+/',' ',(string)$value));

+			$orderBys=array();

+			foreach(explode(',',$value) as $orderBy)

+			{

+				$vs=explode(' ',trim($orderBy));

+				$orderBys[$vs[0]]=isset($vs[1])?$vs[1]:'asc';

+			}

+			$this->_ordersBy->copyFrom($orderBys);

+		}

 	}

 

 	/**