Query help

[boonstoppel]

How would I write the following query with your eloquent syntax?

SELECT 
    places.* 
FROM 
    places, tour_tracks
WHERE 
    ST_DWithin(places.location::geography, tour_tracks.location::geography, 10000)
GROUP BY
    places.id;

[ahawlitschek]

Hey there,

in general like this:

Place::query()
->crossJoin('tour_tracks')
->stWhere(ST::DWithin(....))
->groupBy('places.id')
->get();

I just looked over all the implemented ST functions and it seems, that we haven't implemented ST_DWithin yet.
I might be able to add it later today and publish a new version with the ST::DWithin

[ahawlitschek]

Hey again,

we just updated our package and included all the missing Distance Relationships.
You can now do this:

Place::query()
->crossJoin('tour_tracks')
->stWhere(ST::dWithinGeography('places.location', 'tour_tracks.location', 10000))
->groupBy('places.id')
->get();

You might also archive the same behaviour using the dwithin as join condition:

Place::query()
->join('tour_tracks', function($join) {
   $join->stWhere(ST::dWithinGeography('places.location', 'tour_tracks.location', 10000))
})
->groupBy('places.id')
->get();

Note
In your query you are grouping by places.id column, but selecting every column from the places table.
The default postgres settings prevent selecting more than the columns that are used in the group by clause.

If you just want to retrieve all Places that are within at least 10000m of a tour_track, you can use it within a subquery:

Place::query()
->whereIn('id', function($sub) {
  $sub
    ->select('places.id')
    ->from('places')
    ->join('tour_tracks', function($join) {
          $join->stWhere(ST::dWithinGeography('places.location', 'tour_tracks.location', 10000))
      });
})
->get();

[boonstoppel]

Thanks for getting these changes in so fast. Unfortunately it's not working correctly quite yet. I am only getting rows that have location = null.

Here my 2 queries that I test 1:1

$ids1 = collect(DB::select(
                'SELECT places.id ' . 
                'FROM places, tour_tracks ' .
                'WHERE ST_DWithin(places.location::geography, tour_tracks.location::geography, ' . $maxDistance . ') ' .
                'GROUP BY places.id'
            ))
            ->pluck('id')
            ->all();

$ids2 =  Place::query()
            ->select('places.id')
            ->join('tour_tracks', function($join) use ($maxDistance) {
                $join->stWhere(ST::dWithinGeography('places.location', 'tour_tracks.location', $maxDistance));
            })
            ->groupBy('places.id')
            ->limit($limit)
            ->pluck('id')
            ->all();

$ids3 =  Place::query()
            ->select('places.id')
            ->whereNotNull('places.location')
            ->join('tour_tracks', function($join) use ($maxDistance) {
                $join->stWhere(ST::dWithinGeography('places.location', 'tour_tracks.location', $maxDistance));
            })
            ->groupBy('places.id')
            ->limit($limit)
            ->pluck('id')
            ->all();

// $ids1 = [6]    -> correct
// $ids2 = [7]   -> wrong
// $ids2 = null   -> wrong

[ahawlitschek]

My bad.
We implemented laravels default where behavior.
Using where with the first expression only always result in null check (see using ->dd() instead of get()).

Try using stWhere(ST::dWithin(...), True)

[boonstoppel]

yes, that did the trick. Very nice. Thanks for the quick turnaround!