Nrthugu

I have a versioning system of annotations

class Annotation(db.Model):

    id = db.Column(db.Integer, primary_key=True)



class AnnotationVersion(db.Model):

    id = db.Column(db.Integer, primary_key=True)

    book_id = db.Column(db.Integer, db.ForeignKey("book.id"))

    previous_id = db.Column(db.Integer, db.ForeignKey("post_version.id"), default=None)

    pointer_id = db.Column(db.Integer, db.ForeignKey("annotation.id"))

    current = db.Column(db.Boolean, index=True)

    first_line_num = db.Column(db.Integer)

    last_line_num = db.Column(db.Integer)



class Line(db.Model):

    id = db.Column(db.Integer, primary_key=True)

    book_id = db.Column(db.Integer, db.ForeignKey("book.id")

    line = db.Column(db.String(255))

I have the following two relationships on the Annotation class:

lines = db.relationship("Line", secondary="annotation_version",

        primaryjoin="and_(Annotation.id==AnnotationVersion.pointer_id,"

            "AnnotationVersion.current==True)",

        secondaryjoin="and_(Line.l_num>=AnnotationVersion.first_line_num,"

            "Line.l_num<=AnnotationVersion.last_line_num,"

            "Line.book_id==AnnotationVersion.book_id)",

        viewonly=True, uselist=True)

context = db.relationship("Line", secondary="annotation_version",

        primaryjoin="and_(Annotation.id==AnnotationVersion.pointer_id,"

            "AnnotationVersion.current==True)",

        secondaryjoin="and_(Line.l_num>=AnnotationVersion.first_line_num-5,"

            "Line.l_num<=AnnotationVersion.last_line_num+5,"

            "Line.book_id==AnnotationVersion.book_id)",

        viewonly=True, uselist=True)

As you can see, the context is simply the first_line_num-5 and last_line_num+5; in other words, the context of the annotation is simply the the prior five and next five lines to the actual body of the text of the annotation.

I am trying to define the same context relationship on the actual AnnotationVersion:

context = db.relationship("Line",

    primaryjoin="and_(Line.l_num>=AnnotationVersion.first_line_num-5,"

        "Line.l_num<=AnnotationVersion.last_line_num+5,"

        "Line.book_id==AnnotationVersion.book_id)",

        viewonly=True, uselist=True)

But this exact definition always returns a failure of

sqlalchemy.exc.ArgumentError: Could not locate any relevant foreign
key columns for primary join condition 'line.l_num >=
annotation_version.first_line_num - :first_line_num_1 AND line.l_num
<= annotation_version.last_line_num + :last_line_num_1 AND
line.book_id = annotation_version.book_id' on relationship
AnnotationVersion.context. Ensure that referencing columns are
associated with a ForeignKey or ForeignKeyConstraint, or are annotated
in the join condition with the foreign() annotation.

If I remove either the +5 or the -5 it works. But as soon as I define both, I get that error.

What on earth could cause this particular failure? As you can see it only happens when defined in the primaryjoin condition, because it works perfectly as a secondaryjoin condition.

Ilja Everila's reference to the documentation helped solve it. All I had to do was specify the foreign_key param:

context = db.relationship("Line",

    primaryjoin="and_(Line.l_num>=AnnotationVersion.first_line_num-5,"

        "Line.l_num<=AnnotationVersion.last_line_num+5,"

        "Line.book_id==AnnotationVersion.book_id)",

        foreign_keys=[first_line_num, last_line_num],

        viewonly=True, uselist=True)