InnerJoin Class Reference

This class is used to programmatically perform inner join queries across multiple objects. More...

Inheritance diagram for InnerJoin:

Collaboration diagram for InnerJoin:

Public Member Functions
	InnerJoin ($tag="join")
	Create a new InnerJoin object. More...
	generateQuery ($constraints="", $countOnly=false)
Public Member Functions inherited from AbstractJoin
	add ($class)
	Adds a class to the join definition. More...
	setFilter ($class, $filter)
	Sets an InclusionFilter or ExclusionFilter on the specified class. More...
	constrain ($class, $constraint)
	Sets a query constraint on the given class. More...
	excludeKeyFromJoin ($class, $key)
	By default joins are generated following the "most restrictive search" principle. More...
	xref ($xref, $xrefFieldMaps=null)
	Adds a cross-reference class to the join definition. More...
	pseudo ($class, $items, $indexField)
	Registers a class and indexed array as a pseudo-join item. More...
	count ($constraints="", $countExpr=true)
	query ($constraints="", $page=-1, $size=-1)
	Performs a query against the database returning an array of JoinResult objects. More...
	iteratedQuery ($constraints="")
	Performs a query against the database returning an iterator of JoinResult objects. More...
	indexedQuery ($constraints="", $indexBy="")
	Performs a query against the database, returning an array of DataItem objects of the specified class, indexed by a particular field. More...
	groupedQuery ($constraints="", $indexBy="")
	Performs a query against the database, returning an array of arrays of DataItem objects of the specified class, grouped by a particular field. More...

Additional Inherited Members
Public Attributes inherited from AbstractJoin
	$classes = array()
	The names of the DataItem classes being joined. More...
	$protos = array()
	Prototype instances of the DataItem classes being joined. More...
	$xrefs = array()
	Cross-reference classes (i.e. classes that are part of the join, but not the result) More...
	$xrefFieldMaps = array()
	Cross-reference field map (to support cross-referencing through fields with differing names) More...
	$aliases = array()
	Field aliases for output. More...
	$xrefAliases = array()
	Cross-reference table aliases. More...
	$constraints = array()
	Class-specific constraints add via the constrain() method. More...
	$unique = false
	true to output distinct rows only More...
	$tag = "join"
	XML tag name. More...
	$excludedKeys = array()
	$pseudos = array()
	Pseudo-join lookup tables. More...

Detailed Description

This class is used to programmatically perform inner join queries across multiple objects.

The InnerJoin will automatically generate the SQL for the join based on the primary key field names of each object. In order for an object to be successfully joined, it must contain a primary key with the same name as a foreign key in another participating object (or a foreign key that matches another object's primary key).

Results are returned as an array of JoinResult composite objects. The matching row from each object in the join as added as a sub-object of the JoinResult with the field name being the class name of the object.

Definition at line 647 of file join.inc.

Member Function Documentation

generateQuery()

InnerJoin::generateQuery	(		$constraints = "",
			$countOnly = false
	)

Reimplemented from AbstractJoin.

Definition at line 658 of file join.inc.

     {
          $tables = "";
          $fields = "";
          $alias  = "";
          for($i = 0; $i < count($this->classes); ++$i)
          {
               $class =  $this->classes[$i];
               $proto =& $this->protos[$class];
                
               $alias = "t$i";
               $this->aliases[$proto->table] = $alias;
               
               if (array_key_exists($class, $this->constraints))
               {
                    $c = $this->constraints[$class];
                    
                    foreach($proto->getFields() as $field => $type)
                    {
                         $c = preg_replace("/([\s\‍(])(".$field.")\\b/", "$1t$i.$2", $c);
                    }
 
                    $c = "(".preg_replace("/^\\s*(WHERE\\s+|AND\\s+|OR\\s+)/i", "", $c).")";
                    
                    $this->constraints[$class] = $c;             
               }
               
               if ($tables) $tables .= ", ";
               $tables .= "{$proto->table} $alias";
               
               $f = $proto->getFieldList($alias); 
               if ($fields && $f) $fields .=", ";
               $fields .= $f;
          }
          
          for($i = 0; $i < count($this->xrefs); ++$i)
          {
               $xref = $this->xrefs[$i];
               
               $alias = "x$i";
               $this->xrefAliases[$xref->table] = $alias;
               
               if ($tables) $tables .= ", ";
               $tables .= "{$xref->table} $alias";
          }
          
          if ($this->unique) $fields = "DISTINCT $fields";
          
          $classConstraints = implode(" AND ", array_values($this->constraints));
 
          if (strncmp($constraints, "WHERE", 5))
          {
               if ($classConstraints) $classConstraints = " AND ".$classConstraints;
               $constraints = "WHERE 1=1 $classConstraints $constraints";
          }
          else
          {
               if ($classConstraints) $classConstraints = $classConstraints." AND ";
               $constraints = preg_replace("/^\\s*WHERE/i", "WHERE $classConstraints", $constraints);
          }
          
          $join = "";
          
          // Build Inner Join
          
          for($i = 0; $i < count($this->classes) - 1; ++$i)
          {
               $c = $this->classes[$i];
               $p = $this->protos[$c];
               
               for ($j = $i+1; $j < count($this->classes); ++$j)
               {
                    $d = $this->classes[$j];
                    $q = $this->protos[$d];
                    
                    if ($this->excludedKeys[$d][$q->primary_key] ||
                        $this->excludedKeys[$d][$p->primary_key])
                    {
                         continue;
                    }
                    
                    trace($p->primary_key." ".array_key_exists($p->primary_key, $q->getFields()), 5);
                    trace($q->primary_key." ".array_key_exists($q->primary_key, $p->getFields()), 5);
                    
                    if (array_key_exists($p->primary_key, $q->getFields()))
                    {
                         $join .= " AND t$i.{$p->primary_key}=t$j.{$p->primary_key}";
                    }
                    else if (array_key_exists($q->primary_key, $p->getFields()))
                    {
                         $join .= " AND t$i.{$q->primary_key}=t$j.{$q->primary_key}";
                    }
                    
               }
               
          }
          
          // Alias main table fields
          
          for($i = 0; $i < count($this->classes); ++$i)
          {
               $c = $this->classes[$i];
               $p = $this->protos[$c];
               foreach($p->getFields() as $field => $type)
               {
                    $constraints = preg_replace("/([\s\‍(])(".$field.")\\b/", "$1t$i.$2", $constraints);
                    $constraints = preg_replace("/{$c}\.({$field})\\b/", "t$i.$1", $constraints);
               }
               
               foreach($p->calculatedFields as $field => $expr)
               {
                    foreach($p->getFields() as $f => $type)
                    {
                         $expr = preg_replace("/([\s\‍(])(".$f.")\\b/", "$1t$i.$2", $expr);
                    }
               
                    $constraints = preg_replace("/([\s\‍(])(".$field.")\\b/", " $expr", $constraints);
               }
          }
          
          // Handle XRefs
          
          foreach($this->xrefs as $xref)
          {
               $map = $this->xrefFieldMaps[$xref->table];
               
               foreach($this->protos as $p)
               {
                    $pk = $p->primary_key;
                    $xk = ($map && array_key_exists($pk, $map)) ? $map[$pk] : $pk;
                         
                    if ($xref->hasField($xk))
                    {
                         $xa = $this->xrefAliases[$xref->table];
                         $ta = $this->aliases[$p->table];
                         
                         $join .= " AND $xa.{$xk}=$ta.{$pk}";
                    }
               }
          }
          
          $matches = array();
 
          $orderBy = "";
          if (preg_match("/^(.*?)(ORDER\\s+BY.*)/i", $constraints, $matches))
          {
               $constraints = $matches[1];
               $orderBy = $matches[2];
          }
          else if (preg_match("/^(.*?)(LIMIT.*)/i", $constraints, $matches))
          {
               $constraints = $matches[1];
               $orderBy = $matches[2];
          }
          
          if ($countOnly) $fields = (is_bool($countOnly)) ? "COUNT(1) as result" : "COUNT($countOnly) as result";
                    
          $query = "SELECT $fields FROM $tables $constraints $join $orderBy";
          
          trace("InnerJoin::generateQuery() - $query", 3);
          
          return $query;
     }

InnerJoin()

InnerJoin::InnerJoin

(

$tag = "join"

)

Create a new InnerJoin object.

Definition at line 653 of file join.inc.

     {
          $this->tag = $tag;
     }

---

The documentation for this class was generated from the following file:

• C:/code/cms.sonjara.com/framework/join.inc