80 $this->baseClass = $base;
85 foreach($this->baseItem->getFields() as $field => $type)
87 $this->baseConstraint = preg_replace(
"/\\b".$field.
"\\b/i",
"b.$field", $this->baseConstraint);
92 if (preg_match(
"/ORDER BY.*/i", $this->baseConstraint, $matches))
94 $this->orderBy = $matches[0];
95 $this->baseConstraint = str_replace($this->orderBy,
"", $this->baseConstraint);
98 $this->pivotItem =
null;
103 $basePK = $this->baseItem->getPrimaryKey();
104 $this->fields[$basePK] = $this->baseItem->getType($basePK);
107 $this->groupByFields[] =
"b.{$basePK}";
124 foreach($this->pivotItem->getFields() as $field => $type)
126 $this->pivotConstraint = preg_replace(
"/\\b".$field.
"\\b/i",
"p.$field", $this->pivotConstraint);
154 function field($field, $alias =
"", $type = String)
156 $field = preg_replace(
"/\\b".$this->baseClass.
"\\b/i",
"b", $field);
157 $field = preg_replace(
"/\\b".$this->pivotClass.
"\\b/i",
"p", $field);
159 $bareField = preg_replace(
"/^.*?\\./",
"", $field);
161 $this->fields[$bareField] = String;
163 $this->groupByFields[] = $field;
179 function pivotField($format, $value, $alias =
null, $expression =
"SUM", $type = Number)
181 if (!$alias) $alias = $format;
183 $this->fieldNameFormats[$format] = array(
"expression" => $expression,
"alias" => $alias,
"value" => $value,
"type" => $type);
194 if (!$this->rangeClass)
196 if (!$this->pivotClass)
198 throw new FakoliExpression(
"No Range or Pivot class provided");
206 $pivot = $obj->getPrimaryKey();
208 $rangeItems =
Query::create($this->rangeClass, $this->rangeConstraint)->execute();
210 $expressionMap = array();
212 foreach($rangeItems as $item)
214 foreach($this->fieldNameFormats as $fieldName => $defn)
216 $fieldName = $item->format($fieldName);
217 $value = $defn[
"value"];
218 $expression = $defn[
"expression"];
219 $alias = $defn[
"alias"];
221 $empty = ($defn[
"type"] == String) ?
"''" : 0;
223 $expressionMap[$fieldName] =
"$expression(IF (p.{$pivot}=".$item->quoteFieldValue($pivot).
", IFNULL(p.{$value}, {$empty}), {$empty}))";
224 $this->fields[$fieldName] = $defn[
"type"];
225 $this->fieldAliases[$fieldName] = $item->format($alias);
229 return $expressionMap;
243 $groupByFields = implode(
", ", $this->groupByFields);
248 $basePK = $base->getPrimaryKey();
249 $pivotPK = $pivot->getPrimaryKey();
251 $query =
"SELECT $additionalFields";
252 foreach($expressionMap as $field => $expression)
255 $query .=
"\n\t{$expression} as $field";
259 $query .=
"\nFROM {$base->table} b left outer join {$pivot->table} p \n";
261 $query .=
"ON (p.{$basePK}=b.{$basePK} ";
263 $constraint = preg_replace(
"/^\\s*WHERE /i",
"AND ", $this->pivotConstraint);
264 $query .=
" ".$constraint;
266 $constraint = $this->baseConstraint ? $this->baseConstraint :
"";
267 $query .=
") $constraint GROUP BY $groupByFields";
268 $query .=
" ".$this->orderBy;
270 $this->
query = $query;
281 return new PivotItem($this->fields, $this->baseClass, $this->baseItem->getPrimaryKey(), $this->fieldAliases);
289 for($i = 0; $i < func_num_args(); ++$i)
291 $field = func_get_arg($i);
292 $type = $this->baseItem->getType($field);
295 $this->fields[$field] = $type;
344 trace(
"Page: $this->page Size: $this->size", 3);
353 $result = $db->prepare($query);
354 $result->execute($this->
params);
358 $count = ($this->
page - 1) * $this->size;
365 while($line = $result->fetch())
368 $item->populate($line);
372 if (
$size == 0)
break;
377 catch(PDOException $e)
379 $err =
"query() failed - " . $e->getMessage();
384 trace(count($items).
" items found", 3);
430 $result = $db->prepare($query);
431 $result->execute($this->
params);
433 while($line = $result->fetch())
436 $item->populate($line);
437 $val = $item->get($field);
439 if (array_key_exists($val, $items))
442 if (!is_array($items[$val]))
444 $items[$val] = array($items[$val]);
446 $items[$val][] = $item;
450 $items[$val] = $item;
456 catch(PDOException $e)
458 $err =
"IndexedPivotQuery failed - " . $e->getMessage();
524 $result = $db->prepare($query);
525 $result->execute($this->
params);
527 while($line = $result->fetch())
530 $item->populate($line);
531 $items[$item->$field][] = $item;
536 catch(PDOException $e)
538 $err =
"GroupedPivotQuery failed - " . $e->getMessage();
585 $this->primary_key = $primaryKey;
590 parent::__construct();
603 if ($this->baseClass == $method)
613 foreach($this->fields as $field => $type)
615 if ($field != $this->primary_key)
617 $total += $this->
get($field);
661 function column($pattern, $format, $sortable =
true, $style =
"", $typeHint =
null, $onExport =
null, $sortFormat =
null, $footerTotal =
false)
663 $this->columnSpecs[$pattern] = array(
"format" => $format,
"sortable" => $sortable,
"style" => $style,
664 "typeHint" => $typeHint,
"onExport" => $onExport,
"sortFormat" => $sortFormat,
665 "footerTotal" => $footerTotal);
674 $item = $this->pivot->createPivotItem();
676 $fields = $item->getFields();
678 foreach($fields as $field => $type)
680 foreach($this->columnSpecs as $spec => $details)
683 if (preg_match(
"/{$spec}/", $field, $matches))
685 $format = str_replace(
"%FIELD%", $field, $details[
"format"]);
687 if ($matches[1]) $format = str_replace(
"%KEY%", $matches[1], $format);
689 $this->table->column($item->prettifyFieldName($field), $format, $details[
"sortable"], $details[
"style"],
690 $details[
"typeHint"], $details[
"onExport"], $details[
"sortFormat"]);
691 if ($details[
"footerTotal"])
693 $this->table->footerTotal($format);
AbstractPivotQuery provides the common base class for the shaped pivot query classes PivotQuery,...
generateQuery()
Generates SQL for the full pivot query, with grouping.
pivot($pivotClass, $pivotConstraint, $pivotFunction="SUM")
Adds the pivot to the query.
generateFieldExpressions()
Builds the expressions for the pivot query.
createPivotItem()
Creates an empty PivotItem, configured with fields and field aliases based on the pivot and range.
pivotField($format, $value, $alias=null, $expression="SUM", $type=Number)
Adds a pivot field format to the output object.
range($rangeClass, $rangeConstraint)
Adds the range to the query.
additionalFields()
Register additional fields from the base to be included in the PivotItem output.
__construct($base, $baseConstraint="")
Constructs a new AbstractPivotQuery.
field($field, $alias="", $type=String)
Adds a fixed field to the output object.
params($params)
Sets the bound parameters array.
static getConnection()
Retrieves a reference to the global database connection.
DataItem is the generic base class for database mapped classes.
getRelated($class, $field="")
Returns a single item related by the specified foreign key.
Performs a pivot query against the database, returning an array of arrays of PivotItems,...
groupBy($groupBy)
Set the field by which results should be grouped.
static create($class, $constraints="", $groupBy="")
Create a new GroupedPivotQuery.
__construct($base, $baseConstraint="", $groupBy="")
Run a pivot query and return the results indexed by a selected field.
__construct($base, $baseConstraint="", $indexBy="")
indexBy($indexBy)
Sets the field by which the results should be indexed.
static create($base, $baseConstraint="", $indexBy="")
Creates a new IndexedPivotQuery instance.
PivotItems are dynamically constructed DataItems that map the results of PivotQueries.
__construct($fields, $baseClass, $primaryKey, $fieldAliases=array())
Creates a new PivotItem.
__call($method, $args)
Provide a relation pseudo-method that traverses back to the object at the base of the pivot.
Standard Pivot Query, with support for paging.
page($page, $size)
Sets the page number and size for constraining the result set by page.
__construct($base, $baseConstraint="")
Constructs a new AbstractPivotQuery.
static create($base, $baseConstraint="")
The PivotTableHelper class provides support for configuring display tables (DataListViews and Grouped...
create()
Adds columns to the table based on the column specifications that have been provided.
__construct($pivot, $table)
Creates a new PivotTableHelper.
column($pattern, $format, $sortable=true, $style="", $typeHint=null, $onExport=null, $sortFormat=null, $footerTotal=false)
Adds a column specification.
static create($class, $constraints="")
Static factory method to create a new Query.
trace($msg, $lvl=3, $callStack=null)
Send output to the trace log.
query($class)
Performs a query against the database, returning an array of DataItem objects of the specified class.