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?
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/d834ff0d-654b-4b55-906c-0f062c7bb26fn%40googlegroups.com.
