SQLALchemy - relationship with no mapped foreign keys using custom primaryjoin with func
I have two models and I want to link them (many-to-many), but I don't want to use a secondary table for that, because the location (geom property) can constantly change. I am using ST_Intersects, this is a function from postgis and returns boolean, this is true when geometry calls other geometry.
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.sql.functions import func
from flask import Flask
from geoalchemy2.types import Geometry
db = SQLAlchemy()
def create_app():
app = Flask(...)
...
db.init_app(app)
...
return app
class City(db.Model):
__tablename__ = "city"
id = db.Column(db.Integer, primary_key=True)
geom = db.Column(Geometry("POLYGON",4326))
...
class Location(db.Model):
__tablename__ = "location"
id = db.Column(db.Integer, primary_key=True)
geom = db.Column(Geometry("POLYGON",4326))
...
cities = db.relationship(City, primaryjoin=func.ST_Instesects(geom,City.geom), remote_side=id, foreign_keys=City.id, viewonly=True, uselist=True, lazy='joined')
With this configuration, throw an exception
sqlalchemy.exc.ArgumentError: Relationship Location.cities could not determine any unambiguous local/remote column pairs based on join condition and remote_side arguments. Consider using the remote() annotation to accurately mark those elements of the join condition that are on the remote side of the relationship.
But if I add remote () and foreign () to primaryjoin sqlalchemy throws the same exception
With pure SQL, I can do with:
SELECT city.* FROM location loc JOIN city ON ST_Intersects(city.geom,loc.geom)
+3
source to share
No one has answered this question yet
Check out similar questions: