Doctrine filtering with precalculation

Question

I'm having trouble figuring if there is a better way to filter array collection . This is my code:

$searchResults = $this->get('app.user.repository')->getUsersBySearchData($searchDetails);

$searchResults->filter(

    function($user) use ($searchDetails){
        $distance = $this->get('app.distance.calculator')->calculateDistance($user->getCity(), $searchDetails->getCity());
        $distanceToTravel = $user->getDistanceToTravel();

        if($distance < $distanceToTravel)
            return true;

        return false;
     }
);

This returns all users from $searchResults filtered from already fetched collection.

I need to fetch all users form database whose location is not beyond distance which user enters in his profile and city entered in search form. To determine if that distance is not exceeded i have to calculate distance between location entered in search form to location of user and compare that distance to user profile distance.

Is it possible to do this kind of filtering on database level? I looked into Criteria API but couuldn't figure out how to do it.

score 1 · Accepted Answer · edited May 23 '17 at 11:46

1

Since you use an external module for filtering this is not possible. You should somehow be able to move filtering to database level. Since you need to add support for filtering to different locations I would suggest you store the locations in the database in a way that you can use them for filtering. Simply adding the name of the city will not be enough information.

I would like to suggest that you add longitude (location_longitude) and latitude (location_latitude) of your cities in the database and use this in your SQL query for filtering.

Check also this question for reference

You could also make a custom location mapping inside doctrine for making things work a bit smoother. Check the doctrine documentation chapter Advanced field value conversion using custom mapping types for an example.

This example is explaining a DBAL type a where a location (point) is stored in latitude and longitude values in the database.

edited May 23 '17 at 11:46

Community

1
1

answered Sep 16 '16 at 10:32

Wilt

41,477
12
152
203

Thanks for your help. I edited the question for better explanation. Please confirm if your suggestion still applies. – 3ND Sep 16 '16 at 12:22
Yes suggestion still applies, I updated my question a bit. Adding `longitude` and `latitude` columns to your table will allow you to search with distance. See the links in my answer for examples on how you can do that. – Wilt Sep 16 '16 at 12:46
Will do. Thanks a lot. – 3ND Sep 16 '16 at 12:53
1

@3ND check also [this answer here](http://stackoverflow.com/a/15268096/1697459). Might be helpful. – Wilt Sep 16 '16 at 12:59

Doctrine filtering with precalculation

1 Answers1