AbstractPivotQuery provides the common base class for the shaped pivot query classes PivotQuery, IndexexPivotQuery and GroupedPivotQuery. More...

Inheritance diagram for AbstractPivotQuery:

Collaboration diagram for AbstractPivotQuery:

Public Member Functions
	__construct ($base, $baseConstraint="")
	Constructs a new AbstractPivotQuery. More...

	pivot ($pivotClass, $pivotConstraint, $pivotFunction="SUM")
	Adds the pivot to the query. More...

	range ($rangeClass, $rangeConstraint)
	Adds the range to the query. More...

	field ($field, $alias="", $type=String)
	Adds a fixed field to the output object. More...

	pivotField ($format, $value, $alias=null, $expression="SUM", $type=Number)
	Adds a pivot field format to the output object. More...

	createPivotItem ()
	Creates an empty PivotItem, configured with fields and field aliases based on the pivot and range. More...

	additionalFields ()
	Register additional fields from the base to be included in the PivotItem output. More...

Public Member Functions inherited from AbstractQuery
	constraints ($constraints)
	Sets the constraint clause for the Query. More...

	filter ($filter)
	Sets a filter to constrain the fields retrieved when the query is executed. More...

	params ($params)
	Sets the bound parameters array. More...

	bind ()
	Binds placeholders to parameter values. More...

	execute ()

Public Attributes
	$baseClass

	$baseConstraint

	$baseItem

	$rangeClass

	$rangeConstraint

	$pivotClass

	$pivotConstraint

	$pivotItem

	$fields

	$fieldAliases

	$additionalFields = array()

	$groupByFields = array()

	$orderBy

	$totalField = null

Public Attributes inherited from AbstractQuery
	$class

	$constraints

	$filter

	$params

Protected Member Functions
	generateFieldExpressions ()
	Builds the expressions for the pivot query. More...

	generateQuery ()
	Generates SQL for the full pivot query, with grouping. More...

Detailed Description

AbstractPivotQuery provides the common base class for the shaped pivot query classes PivotQuery, IndexexPivotQuery and GroupedPivotQuery.

Every pivot consists of a 'base', 'range' and 'pivot'. The base is the root object, the pivot is the object that will be pivoted (i.e. have rows converted to columns in the output) and the range is the object that provides the range of values over which this conversion will occur.

Author: Andrew Green

Definition at line 50 of file pivot.inc.

Constructor & Destructor Documentation

◆ __construct()

AbstractPivotQuery::__construct	(	$base,
		$baseConstraint = `""`
	)

Constructs a new AbstractPivotQuery.

Parameters

string	$base	Class name of the DataItem that will be the base
string	$baseConstraint	SQL constraint over which the base query will operate.

Reimplemented from AbstractQuery.

Reimplemented in PivotQuery.

Definition at line 78 of file pivot.inc.

      {
           $this->baseClass = $base;
           $this->baseConstraint = $baseConstraint;
      
           $this->baseItem = new $this->baseClass;
      
           foreach($this->baseItem->getFields() as $field => $type)
           {
                $this->baseConstraint = preg_replace("/\\b".$field."\\b/i", "b.$field", $this->baseConstraint);
           }
      
           $matches = array();
           
           if (preg_match("/ORDER BY.*/i", $this->baseConstraint, $matches))
           {
                $this->orderBy = $matches[0];
                $this->baseConstraint = str_replace($this->orderBy, "", $this->baseConstraint);
           }
           
           $this->pivotItem = null;
      
           $this->page = -1;
           $this->size = -1;
      
           $basePK = $this->baseItem->getPrimaryKey();
           $this->fields[$basePK] = $this->baseItem->getType($basePK);
      
           $this->additionalFields[] = "b.{$basePK} as {$basePK}";
           $this->groupByFields[] = "b.{$basePK}";
      }

Member Function Documentation

◆ additionalFields()

AbstractPivotQuery::additionalFields ( )

Register additional fields from the base to be included in the PivotItem output.

Definition at line 287 of file pivot.inc.

      {
           for($i = 0; $i < func_num_args(); ++$i)
           {
                $field = func_get_arg($i);
                $type = $this->baseItem->getType($field);
                if ($type)
                {
                     $this->fields[$field] = $type;
                     $this->additionalFields[] = "b.{$field} as {$field}";
                }
           }
           return $this;
      }

◆ createPivotItem()

AbstractPivotQuery::createPivotItem ( )

Creates an empty PivotItem, configured with fields and field aliases based on the pivot and range.

Returns: PivotItem

Definition at line 279 of file pivot.inc.

      {
           return new PivotItem($this->fields, $this->baseClass, $this->baseItem->getPrimaryKey(), $this->fieldAliases);
      }

◆ field()

AbstractPivotQuery::field	(	$field,
		$alias = `""`,
		$type = `String`
	)

Adds a fixed field to the output object.

These fields can be from the base object or the pivot object. If they are from the pivot object, then the resulting output will be subgrouped by each fixed field.

Parameters

string	$field	field in the format "Object.field"
string	$alias	Alias name for inclusion as labels or column headings in output
string	$type	Type of the field

Returns: AbstractPivotQuery

Definition at line 154 of file pivot.inc.

      {
           $field = preg_replace("/\\b".$this->baseClass."\\b/i", "b", $field);
           $field = preg_replace("/\\b".$this->pivotClass."\\b/i", "p", $field);
      
           $bareField = preg_replace("/^.*?\\./", "", $field);
      
           $this->fields[$bareField] = String;
           $this->additionalFields[] = "$field as $bareField";
           $this->groupByFields[] = $field;
      
           return $this;
      }

◆ generateFieldExpressions()

AbstractPivotQuery::generateFieldExpressions ( )

protected

Builds the expressions for the pivot query.

Exceptions

FakoliExpression

Returns: Array

Definition at line 192 of file pivot.inc.

      {
           if (!$this->rangeClass)
           {
                if (!$this->pivotClass)
                {
                     throw new FakoliExpression("No Range or Pivot class provided");
                }
                     
                $this->rangeClass = $this->pivotClass;
                $this->rangeConstaint = $this->pivotConstraint;
           }
      
           $obj = new $this->rangeClass;
           $pivot = $obj->getPrimaryKey();
      
           $rangeItems = Query::create($this->rangeClass, $this->rangeConstraint)->execute();
      
           $expressionMap = array();
      
           foreach($rangeItems as $item)
           {
                foreach($this->fieldNameFormats as $fieldName => $defn)
                {
                     $fieldName = $item->format($fieldName);
                     $value = $defn["value"];
                     $expression = $defn["expression"];
                     $alias = $defn["alias"];
      
                     $empty = ($defn["type"] == String) ? "''" : 0;
                     
                     $expressionMap[$fieldName] = "$expression(IF (p.{$pivot}=".$item->quoteFieldValue($pivot).", IFNULL(p.{$value}, {$empty}), {$empty}))";
                     $this->fields[$fieldName] = $defn["type"];
                     $this->fieldAliases[$fieldName] = $item->format($alias);
                }
           }
      
           return $expressionMap;
      }

◆ generateQuery()

AbstractPivotQuery::generateQuery ( )

protected

Generates SQL for the full pivot query, with grouping.

Returns: string

Definition at line 236 of file pivot.inc.

      {
           // First generate fields
      
           $expressionMap = $this->generateFieldExpressions();
      
           $additionalFields = implode(", ", $this->additionalFields);
           $groupByFields = implode(", ", $this->groupByFields);
      
           $base = $this->baseItem;
           $pivot = $this->pivotItem;
      
           $basePK  = $base->getPrimaryKey();
           $pivotPK = $pivot->getPrimaryKey();
      
           $query = "SELECT $additionalFields";
           foreach($expressionMap as $field => $expression)
           {
                $query .= ", ";
                $query .= "\n\t{$expression} as $field";
                $first = false;
           }
      
           $query .= "\nFROM {$base->table} b left outer join {$pivot->table} p \n";
      
           $query .= "ON (p.{$basePK}=b.{$basePK} ";
      
           $constraint = preg_replace("/^\\s*WHERE /i", "AND ", $this->pivotConstraint);
           $query .= " ".$constraint;
      
           $constraint = $this->baseConstraint ? $this->baseConstraint : "";
           $query .= ") $constraint GROUP BY $groupByFields";
           $query .= " ".$this->orderBy;
           
           $this->query = $query;
           return $query;
      }

◆ pivot()

AbstractPivotQuery::pivot	(	$pivotClass,
		$pivotConstraint,
		$pivotFunction = `"SUM"`
	)

Adds the pivot to the query.

Parameters

string	$pivotClass	the class of DataItem that will be the pivot
string	$pivotConstraint	SQL constraint over which the pivot query will operate
string	$pivotFunction	default SQL aggregation function used to reduce rows when pivoting (default is SUM, but MAX, MIN, AVG, etc. can also be useful)

Returns: AbstractPivotQuery

Definition at line 117 of file pivot.inc.

      {
           $this->pivotClass = $pivotClass;
           $this->pivotConstraint = $pivotConstraint;
      
           $this->pivotItem = new $this->pivotClass;
      
           foreach($this->pivotItem->getFields() as $field => $type)
           {
                $this->pivotConstraint = preg_replace("/\\b".$field."\\b/i", "p.$field", $this->pivotConstraint);
           }
      
           return $this;
      }

◆ pivotField()

AbstractPivotQuery::pivotField	(	$format,
		$value,
		$alias = `null`,
		$expression = `"SUM"`,
		$type = `Number`
	)

Adds a pivot field format to the output object.

One matching output field will be added for each entry in the range.

Parameters

string	$format	format specifier for the output field (i.e. "pivot_{pivot_id}")
string	$value	value expression that the field will be populated with. This could be simply a field in the pivot, or it might be a more complex SQL expression
string	$alias	Alias name for inclusion as labels or column headings in output
string	$expression	SQL aggregation function used to reduce rows when pivoting (default is SUM, but MAX, MIN, AVG, etc. can also be useful)
string	$type	Type of the field

Returns: AbstractPivotQuery

Definition at line 179 of file pivot.inc.

      {
           if (!$alias) $alias = $format;
      
           $this->fieldNameFormats[$format] = array("expression" => $expression, "alias" => $alias, "value" => $value, "type" => $type);
           return $this;
      }

◆ range()

AbstractPivotQuery::range	(	$rangeClass,
		$rangeConstraint
	)

Adds the range to the query.

Parameters

string	$rangeClass	the class of DataItem that will be supplying the range for the pivot
string	$rangeConstraint	SQL constraint over which the range will operate

Returns: AbstractPivotQuery

Definition at line 138 of file pivot.inc.

      {
           $this->rangeClass = $rangeClass;
           $this->rangeConstraint = $rangeConstraint;
           return $this;
      }

Member Data Documentation

◆ $additionalFields

AbstractPivotQuery::$additionalFields = array()

Definition at line 67 of file pivot.inc.

◆ $baseClass

AbstractPivotQuery::$baseClass

Definition at line 52 of file pivot.inc.

◆ $baseConstraint

AbstractPivotQuery::$baseConstraint

Definition at line 53 of file pivot.inc.

◆ $baseItem

AbstractPivotQuery::$baseItem

Definition at line 54 of file pivot.inc.

◆ $fieldAliases

AbstractPivotQuery::$fieldAliases

Definition at line 65 of file pivot.inc.

◆ $fields

AbstractPivotQuery::$fields

Definition at line 64 of file pivot.inc.

◆ $groupByFields

AbstractPivotQuery::$groupByFields = array()

Definition at line 68 of file pivot.inc.

◆ $orderBy

AbstractPivotQuery::$orderBy

Definition at line 70 of file pivot.inc.

◆ $pivotClass

AbstractPivotQuery::$pivotClass

Definition at line 59 of file pivot.inc.

◆ $pivotConstraint

AbstractPivotQuery::$pivotConstraint

Definition at line 60 of file pivot.inc.

◆ $pivotItem

AbstractPivotQuery::$pivotItem

Definition at line 62 of file pivot.inc.

◆ $rangeClass

AbstractPivotQuery::$rangeClass

Definition at line 56 of file pivot.inc.

◆ $rangeConstraint

AbstractPivotQuery::$rangeConstraint

Definition at line 57 of file pivot.inc.

◆ $totalField

AbstractPivotQuery::$totalField = null

Definition at line 71 of file pivot.inc.

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

C:/code/cms.sonjara.com/framework/pivot.inc

Public Member Functions

Public Attributes

Protected Member Functions

Detailed Description

Constructor & Destructor Documentation

◆ __construct()

Member Function Documentation

◆ additionalFields()

◆ createPivotItem()

◆ field()

◆ generateFieldExpressions()

◆ generateQuery()

◆ pivot()

◆ pivotField()

◆ range()

Member Data Documentation

◆ $additionalFields

◆ $baseClass

◆ $baseConstraint

◆ $baseItem

◆ $fieldAliases

◆ $fields

◆ $groupByFields

◆ $orderBy

◆ $pivotClass

◆ $pivotConstraint

◆ $pivotItem

◆ $rangeClass

◆ $rangeConstraint

◆ $totalField