[TYPO3-dev] Remove records from extbase query result

[Dirk Wenzel]

Hey Stephan,
thank you very much for your quick and elaborate answer!

Actualy I wasn't aware of the fact that the pagination uses the $query 
part of the extbase query result (and thought it would use only the 
$queryResult which seems to be an array).

In fact your suggestion to fetch only uids and location data is what I 
came up with in the meantime. The penalty is a second query. But this 
might be much faster then the first one...

I already have a findMultipleByUid() method which does exactly that. [1]
The ordering part is simple:
 > $query->setOrderings(array($sortField => $sortOrder));
(Where $sortfield is a field name and $sortOrder a 
\TYPO3\CMS\Extbase\Persistence\QueryInterface::<SORT_CONSTANT>)

Kind regards
Dirk

[1] 
https://github.com/dwenzel/placements/blob/release/0.6/Classes/Domain/Repository/AbstractDemandedRepository.php#L110 


Am 13.01.14 11:19, schrieb Stephan Schuler:
> What you're trying to do will not solve your problem even if it was possible -- which it isn't.
>
> The paginate mechanism is based on one important concept: Query manipulation.
> You have a QueryResult object. This very object knows its Query object and you can retrieve the Query object from the QueryResult whenever you like.
> And that's what paginate does: It calls $queryResultObject->getQuery(), clones it (I think, at that point I'm not completely sure), and adds some constraints. In terms of SQL it adds "LIMIT ($offset, $numberOfRows)". In terms of QOM it's like "->setLimit($numberOfRows)->setOffset($offset)".
> So the paginate mechanism relies on the original Query object to reflect exactly the set of data you want to show.
>
> If there would be a way to add certain objects to the QueryResult object or to remove some, this would not be reflected by the Query object being represented by the QueryResult.
>
> This feature is very important when it comes to performance. Think about a list of hundreds or even thousands of records. Now you want to have a paginate mechanism that only shows say 20 at a time. You definitively don't extbase to have its DataMapper mapped every single one of this >1000 but only the 20 you really want to display. That's important for both, traffic between your webserver and your MySQL server and the RAM your PHP process consumes.
>
> And this (the part where pagination relies on the Query more then on the actual QueryResult you put into the pagination) is the reason why you e.g. cannot paginate over arrays (you don't have a Query to manipulate) or over QueryResults based on raw statements (there the setLimit() and setOffset() methods simply don't apply).
>
> There are two things to say now.
>
> First thing: You can (sort of) calculate distances in SQL.
>
> But since that's a very costly query, you shouldn't. I guess that's what you tried to say when saying "this cannot be done as a query constraint".
Right.

And of course because that's not possible to express with QOM but needs 
a raw statement, you lose your paginate ability here, too.
> http://forum.typo3.org/index.php/t/194493/
> It's explained here, somewhere in the middle.
>
> Second thing: The actual reduction of the result should be done *before* data mapping.
>
> I would fetch all "uid/lat/lang" values in the first place. This can be done just without the data mapper and as a raw statement. I don't know which settings to use, but I would ty to retrieve only uid, lat and lang from the database .You only need those three attributes.
> Then calculate distances and drop every pair that doesn't fit your radius. You will end up with a bunch of uids that can be ordered by radius. Do that in PHP and have just an array like "array($closestUid, $secondClosestUid, $thirdClosestUid, ...)".
> After that, you can use another query ->in('uid', $listofUids).
>
> Now you have your reduced QueryResult.
>
> There is only one thing left: I don't know how to apply the "orderBy" part. In raw SQL I would do something like "SELECT * FROM dots WHERE uid IN ($listOfUids) ORDER BY FIND_IN_SET('uid', '$listOfUids')". Maybe you can extend the Query stuff to support this. But you need this to be done without the "statement" part of a query because that's the only way to have the paginate thingy dynamically adjust the limit and offset.
>