[TYPO3-mvc] [solved] Re: AVG, SUM in Repository

[Stefan Frömken]

OK...it works now. Following steps are restored from my brain, so it may 
not work with copy&paste:

1.) rewrite Tx_Extbase_Persistence_QueryInterface with my own Query 
class by TS. My class extends the original one.
2.) added a simple sum method to my query class:

protected $fields = array();
public function setSum(array$columns) {
   $this->fields = $columns
}

2a.) added a getter for the fields:

public function getFields() {
   return $this->fields;
}

3.) rewrite Tx_Extbase_Persistence_Storage_BackendInterface with my own 
Backend class by TS. My class extends the original one.
4.) Added a new method to my storage class:

public function parseFields(array $columns, &$sql) {
   if(count($columns)) {
     $sql = array(); // remove previously setted fields
     foreach($columns as $col) {
       $sql['fields'][$col] = 'SUM(' . $col . ') as ' . $col;
     }
   }
}

5.) call my method from parseQuery:

...
$this->parseSource($source, $sql, $parameters);
--> $this->parseFields($query->getFields(), $sql);
$this->parseConstraint($query->getConstraint(), $source, $sql, $parameters);
$this->parseOrderings($query->getOrderings(), $source, $sql);
$this->parseLimitAndOffset($query->getLimit(), $query->getOffset(), $sql);
...

6.) Now I can do following in my Repository:

public function getSum(array $columns) {
   $query = $this->createQuery();
   $query->getQuerySettings()->setReturnRawResults(true);
   $query->setSum($columns);
   return $query->execute();
}

For now I see only one problem: When you're working with multiple tables 
(joins) it can crash, when there are similar col names.

Stefan



I have implemented a sum method to the repository-class

Am 30.01.2012 17:30, schrieb Stefan Frömken:
> Hello MVC-List,
>
> has anybody an idea how to create a SUM or AVG Query in Repository?
>
> OK...I can query on my own with $query->statement, but the result is
> always empty. I think that is because of method: >>parseQuery<< where
> fields will be set to >>$table . '*'<<. So something like >>SUM(field)<<
> will never work.
>
> Further I tries to modify Tx_Extbase_Persistence_Query:
> config.tx_extbase {
> objects {
> Tx_Extbase_Persistence_Query {
> className = Tx_KeZr_Persistence_Query
> }
> }
> }
>
> But in Frontend following appears:
> Fatal error: Call to undefined method
> Tx_Extbase_Persistence_Query::sum() in
> /var/www/typo3_45/typo3conf/ext/myExt/Classes/Domain/Repository/InvoiceRepository.php
> on line 33
> Does my TS don't works with this class?
>
> I can do a
> $GLOBALS['TYPO3_DB']->sql_query('SELECT SUM(col) FROM table');
> because I need the where clause of the repository. The user can change
> the amount of records or filter them...so...how to get the current where
> clause out of the repository?
>
> Maybe you have some better ideas?
>
> Stefan