xp-framework/rfc — Issue #123: Use aggregation/projection with criteria
Author: thekid
Created: 2011-10-17 15:56:54+00:00
State: closed
Labels: framework, implemented

Scope of Change
====
- Extend the criteria api to aggregate or cut down the
querie's resultset.
- After a projection, the resultset does not fit to
the dataSet's types anymore, the rdbms.Record will be
implemented

This is not rfc0051.

Rationale
====
- counting, summing up, etc. - using aggregation
functions that are provided by SQL
- cutting down the resultset, as one do not need the
whole table resut
- to give a table column as Parameter to the projection,
the rdbms.Column class was implemented
All DataSets are extendet to have a column factory
- Criteria::setProjection, Criteria::withProjection and ProjectionList::add can also
handle rdbms.SQLFragment (leke function or column) as parameter, a Property Projection
is assuemed then

Functionality
====

```
To use projections the package rdbms.criterion.Projections has to be imported.

```

Column class
----
to get an instance of a column:

```php

```

Aggregation
----
projection to the function avg

```php
doSelect(Criteria::newInstance()
->setProjection(Projections::average($idColumn))
);
?>
```

projection to the function max

```php
doSelect(Criteria::newInstance()
->setProjection(Projections::max($idColumn))
);
?>
```

projection to the function min

```php
doSelect(Criteria::newInstance()
->setProjection(Projections::min($idColumn))
);
?>
```

projection to the function sum

```php
doSelect(Criteria::newInstance()
->setProjection(Projections::sum($idColumn))
);
?>
```

projection to the function count

```php
doSelect(Criteria::newInstance()
->setProjection(Projections::count())
);
?>
```

Projection
----
projection to the property name

```php
doSelect(Criteria::newInstance()
->setProjection(Projections::property($nameColumn))
);
?>
```
is th same as:

```php
doSelect(Criteria::newInstance()
->setProjection($nameColumn)
);
?>
```

projection to a set of properties

```php
doSelect(Criteria::newInstance()
->setProjection(
Projections::projectionList()
->add(Projections::property($idColumn))
->add(Projections::property($nameColumn))
)
);
?>
```
is th same as:

```php
doSelect(Criteria::newInstance()
->setProjection(
Projections::projectionList()
->add($idColumn)
->add($nameColumn)
)
);
?>
```

projection to the property name with the alias surname

```php
doSelect(Criteria::newInstance()
->setProjection(Projections::property($nameColumn), "surname")
);
?>
```
is th same as:

```php
doSelect(Criteria::newInstance()
->setProjection($nameColumn, "surname")
);
?>
```

temporary use
----
the method Criteria::withProjection can be used to build a temporary
Criteria with a projection - e.g. count to test how many results will be
in the resultset

```php
add(Restrictions::equal("name", "Mary"));
$amountOfMarys= Person::getPeer()->iteratorFor(
$crit->withProjection(Projections::count())
)->next()->get('count');
if (LIMIT >= $amountOfMarys) Person::getPeer()->doSelect($crit);
?>
```

Security considerations
====
n/a

Speed impact
====
May be slows the criteria rendering process

Dependencies
====
- RFC 0126
http://xp-framework.net/rfc/0126.rfc.html
- method isProjection() was added to the interface rdbms.SQLExpression

Related documents
====
- xp-lab
http://experiments.xp-framework.net/?people,wagner,rfc,0123
http://experiments.xp-framework.net/?people,wagner,rfc,0126
- Joint patch for RFC0096, RFC0123, RFC0124 and RFC0129 against skeleton
http://xp-framework.net/rfc/contrib/rfc0096_0123_0124_0129/skeleton.diff
- Joint patch for RFC0096, RFC0123, RFC0124 and RFC0129 against ports
http://xp-framework.net/rfc/contrib/rfc0096_0123_0124_0129/ports.diff