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





All Articles