Complex Model Relation involving a postgis database function


I’m going in circles with how to build a complex relation between two models that are based on database tables that have a spatial/geometry field (postgis layers) using a spatial databse function .

Model 1 - Points
Model 2 - Polygons

Geographically many points are within one polygon feature. In my app I regularly need to query this and find the points within the polygons. The basic SQL to do something like this is:

select * from points, polygons
where = 123
and ST_within(points.geom, polygons.geom)

As you can see the join really happens with a function rather than a traditional field x = field y

So I’ve tried lots of ways to try and get this to work with relations like this on the polygons model:

(the organisation_id => organisation_id is something I haven’t discussed in this post but its valid and just gets a subset of the records in the Point table)

public function getPoints(){
return $this->hasMany(Points::className(), [‘organisation _id’ => ‘organisation_id’])
->where(“ST_Within(Points.geom, Polygons.geom)”);

I’ve also tried onCondition and a few other things but no matter what I do when I run something like this to use the join:

$polygon = $this->findModel($id);
$requiredPoints = polygon->points;

I end up with errors like:

ERROR: missing FROM-clause entry for table “Polygons”

The SQL being executed was: SELECT * FROM “Points” WHERE
(“organisation_id”=1) AND (((“Points”.“organisation_id”=1)) AND (ST_Within(Points.geom, Polygons.geom)))

Feel like Im approaching this the wrong way or missing something simple. Is using a subquery the way to do it? Any suggestions?



Wrap ST_Within(Points.geom, Polygons.geom) into Expression.