Given the following example model:
class Node(Base): __tablename__ = "node"
id = Column(Integer, primary_key=True, autoincrement=True)
class Edge(Base):
__tablename__ = "edge"
id = Column(Integer, primary_key=True, autoincrement=True)
color = Column(Enum(Color))
src_node_id = Column(Integer, ForeignKey("node.id")) dst_node_id = Column(Integer, ForeignKey("node.id")) src_node = relationship(
"Node",
foreign_keys=[src_node_id],
backref=backref("downstream_edges")
)
dst_node = relationship(
"Node",
foreign_keys=[dst_node_id],
backref=backref("upstream_edges")
)
The collections Node.downstream_edges and Node.upstream_edges are so large that it is detrimental to load them entirely on a regular basis (although I still need the ability to do so). So for performance reasons, I am trying to come up with a way of sub-dividing these relationships based on the "color" attribute. One way I have tried to do this is by defining additional relationships on the Node class which filter the query using a custom primaryjoin. For example:
blue_downstream_edges = relationship(
"Edge",
primaryjoin="and_(Node.id == Edge.src_node_id, Edge.color == 'blue'",
viewonly=True,
)
The downside to this approach is that the viewonly=True attribute means I cannot directly make modifications to this collection. When I want to add new Edges, I have to do it on the complete downstream_edges relationship, which will trigger the full load that I'm trying to avoid.
Another idea I had was to use the lazy="dynamic" attribute on the downstream_edges and upstream_edges relationships, so that I could do something like:
blue_downstream_edges = node.downstream_edges.filter(Edge.color == 'blue')
As I understand it, I would then be able to append items to this subset collection. However, those changes would not be seen in the complete downstream_edges collection.
In short, what I'm trying to achieve is a way to provide subsets of a large collection, which are modifiable, and those modifications are visible in both the larger collection and the subset collection.
I'm guessing this would need to involve some sort of shared access to the same in-memory ORM mapper class, because a modification made to one representation of the collection that has not yet been flushed/persisted to the database would not be visible to another representation of that collection, assuming it requires a database query to load.
Is this possible at all?