is it possible to implement this anti-pattern in SQLAlchemy?

[Jonathan Vanasco]

I have two classes where one f-keys onto another.

Things work perfectly:

     class Foo(Base):

        id = Column(Integer, primary_key=True)

        bar_id = Column(Integer, ForeignKey("bar.id"), nullable=True)

        bar = relationship(

        "bar", 

primaryjoin="Foo.bar_id==Bar.id",

uselist=False,

back_populates="foo",

)

     class Bar(Base):

        id = Column(Integer, primary_key=True)

        foo = relationship(

        "Foo", 

primaryjoin="Bar.id==Foo.bar_id",

uselist=False,

back_populates="bar",

)

Thanks to SQLAlchemy, I can do this:

myFoo.bar = myBar

As expected `myFoo.bar_id` is updated.  Wonderful.

I am working on some new functionality, and hit a potential performance issue.  While my `Foo` objects inherently know if there is an associated `Bar`, I have to query (direct or via lazy-load) the database to find out if a `Bar` has an associate `Foo`.  In most situations, this is not an issue. In a few contexts, the lazyloading or joins are a bit burdonsome.

Is there a way to set up the `relationship` so I could do cache the `foo_id` on Bar? Something like this:

     class Foo(Base):

        id = Column(Integer, primary_key=True)

        bar_id = Column(Integer, ForeignKey("bar.id"), nullable=True)

        bar = relationship("Bar", ???)

 

      class Bar(Base):

        id = Column(Integer, primary_key=True)

        foo_id = Column(Integer, ForeignKey("foo.id"), nullable=True)

        foo = relationship("Foo", ???)

This is obviously an anti-pattern in database design, as I only need to duplicate this data to improve performance in a few places.

[Jonathan Vanasco]

Ok never mind!

I realized I could scrap this entire functionality and replace it with something else.

The use-case was trying to detect the backup renewal options for SSL Certificates if the private key Or account key is revoked. (foo is an ACME order if available, bar is the certificate). Instead of automatically fixing these, I’m just going to log them as “needs triage” and let each one be managed individually.