[TYPO3-dev] Remove records from extbase query result

[Stephan Schuler]

Hey Dirk.


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". 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.


Regards,




Stephan Schuler
Web-Entwickler

Telefon: +49 (911) 539909 - 0
E-Mail: Stephan.Schuler at netlogix.de
Website: media.netlogix.de



--
netlogix GmbH & Co. KG
IT-Services | IT-Training | Media
Neuwieder Straße 10 | 90411 Nürnberg
Telefon: +49 (911) 539909 - 0 | Fax: +49 (911) 539909 - 99
E-Mail: info at netlogix.de | Internet: http://www.netlogix.de

netlogix GmbH & Co. KG ist eingetragen am Amtsgericht Nürnberg (HRA 13338)
Persönlich haftende Gesellschafterin: netlogix Verwaltungs GmbH (HRB 20634)
Umsatzsteuer-Identifikationsnummer: DE 233472254
Geschäftsführer: Stefan Buchta, Matthias Schmidt



-----Ursprüngliche Nachricht-----
Von: typo3-dev-bounces at lists.typo3.org [mailto:typo3-dev-bounces at lists.typo3.org] Im Auftrag von Dirk Wenzel
Gesendet: Sonntag, 12. Januar 2014 22:46
An: typo3-dev at lists.typo3.org
Betreff: [TYPO3-dev] Remove records from extbase query result

Hi all,
is it possible to remove records from a query result?

I tried using
unset($result[$key]) and
$result->offsetUnset($key)
(where key is the current key in a foreach loop), but neither works.

Background:
I do search for domain objects in a radius around a given location.
Therefore I compute a bounding box of geographic latitude and longitude around this location. All objects which are located in this box are returned.

For an actual *radius* I have to compute the distance between the location and each object. This can not be done as a query constraint. So I have to narrow down the result later.

Converting the result to an array is not an option since the result is being displayed with pagination and the paginator expects a query result.

The code can be seen under
https://github.com/dwenzel/placements/blob/release/0.6/Classes/Controller/PositionController.php
beginning at line 153

Cheers
Dirk
_______________________________________________
TYPO3-dev mailing list
TYPO3-dev at lists.typo3.org
http://lists.typo3.org/cgi-bin/mailman/listinfo/typo3-dev