[TYPO3-mvc] Query with Group by

[Jochen Rau]

Hi Fabian.

On 28.04.10 17:50, Fabian Fisahn wrote:
> Hi,
>
> I need to build a query with a "Group by".
>
> In SQL it is:
> SELECT user_id, COUNT(user_id) AS counter
> FROM table
> WHERE pid = 152
> GROUP BY user_id
> ORDER BY counter DESC
>
> I tried to find a solution with the Extbase API. The only solution I
> found is
>
> $query = $this->createQuery();
> return $query->statement('
> 	SELECT user_id, COUNT(user_id) AS counter
> 	FROM table
> 	WHERE pid = 152
> 	GROUP BY user_id
> 	ORDER BY counter DESC')->execute();
> }
>
> Question 1:
> is there another/better way to build the query?

Currently you can only query for all fields (*) or count the results 
(COUNT(*)).

> Question 2:
> with my solution I get a wrong result. The expected result is

Extbase tries to map your result to an object of class 
Tx_FfExt_Domain_Model_Voting.If you want to get the raw query results, try

$query = $this->createQuery();
$query->getQuerySettings()->setReturnRawQueryResult(TRUE);
...


> "user_id","counter"
> 73,3
> 74,1
>
> But I only get:
>
> |Object:
>
> Tx_FfExt_Domain_Model_Voting Object
> (
>      [userId:protected] =>  73
>      [counter:protected] =>
>      [_cleanProperties:private] =>  Array
>          (
>              [userId] =>  73
>              [uid] =>
>          )
>
>      [uid:protected] =>
>      [isClone:private] =>
> )
>
> ||Object:
>
> Tx_FfExt_Domain_Model_Voting Object
> (
>      [userId:protected] =>  73
>      [counter:protected] =>
>      [_cleanProperties:private] =>  Array
>          (
>              [userId] =>  73
>              [uid] =>
>          )
>
>      [uid:protected] =>
>      [isClone:private] =>
> )
>
> Any idea why I don't get the correct  result? Does Extbase manipulate
> the given Query?
>

Regards
Jochen