On 7/6/15 3:13 PM, Ryan Holmes wrote:
I have tried this, but still cannot get it figured out. I keep getting
exceptions such as " Could not determine join condition between
parent/child tables on relationship...", etc. There are two things
that seems to be hindering me:
- I'm using classical mappings. Many resources online assume
declarative style, and I'm having trouble converting it. The use of
classical mapping is due to how the project was started and is the
convention used. To move to declarative style would be a rather large
undertaking, though I guess it's possible.
classical mappings are more verbose but in many ways easier, because you
can just refer to the Table object you need rather than playing lots of
games with declarative's string-style config.
- I'm using a self-referential mapping, which also complicates things,
since most resources I find have two distinct objects along with the
association.
Well yes, self referential m2m is described at
http://docs.sqlalchemy.org/en/rel_1_0/orm/join_conditions.html#self-referential-many-to-many-relationship.
However, once we use an association object here, the relationship is
actually not self-referential anymore, it becomes two pairs of o2m/m2o
relationships.
Below is a proof concept.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.associationproxy import association_proxy
metadata = MetaData()
item_table = Table(
"items", metadata,
Column("ID", Integer, primary_key=True),
Column("name", String, nullable=False),
Column("timestamp", Integer, nullable=False),
Column("projected", Boolean),
)
projectedItem_table = Table(
"projectedItem", metadata,
Column("sourceID", ForeignKey("items.ID"), primary_key=True),
Column("destID", ForeignKey("items.ID"), primary_key=True),
Column("enabled", Integer))
class Item(object):
def __init__(self, name, timestamp):
self.name = name
self.timestamp = timestamp
projectedItem = association_proxy(
"dest_items", "source_item",
creator=lambda source_item: ProjectedItem(source_item, None, False)
)
class ProjectedItem(object):
def __init__(self, source_item, dest_item, enabled):
self.source_item = source_item
source_item.projected = True
self.dest_item = dest_item
self.enabled = enabled
mapper(
Item, item_table,
properties={
"dest_items": relationship(
ProjectedItem,
primaryjoin=projectedItem_table.c.destID == item_table.c.ID,
backref="dest_item"
),
"source_items": relationship(
ProjectedItem,
primaryjoin=item_table.c.ID == projectedItem_table.c.sourceID,
backref="source_item"
)
}
)
mapper(ProjectedItem, projectedItem_table)
e = create_engine("sqlite://", echo=True)
metadata.create_all(e)
s = Session(e)
i1 = Item("i1", 1)
i2 = Item("i2", 2)
i3 = Item("i3", 3)
i4 = Item("i4", 4)
i5 = Item("i5", 5)
i1.projectedItem.append(i2)
i1.projectedItem.append(i3)
i3.projectedItem.append(i4)
i3.dest_items[0].enabled = True
i4.dest_items.append(ProjectedItem(i5, i4, True))
s.add_all([i1, i2, i3, i4, i5])
s.commit()
i3 = s.query(Item).filter_by(name='i3').first()
for proj_item in i3.dest_items:
print(proj_item.source_item, proj_item.enabled)
On Monday, July 6, 2015 at 1:14:29 PM UTC-4, Michael Bayer wrote:
On 7/6/15 12:06 PM, Ryan Holmes wrote:
I have an interesting problem that I haven't been able to solve
for quite some time. I keep finding information about association
proxies and the like, but nothing that really helps (or maybe I'm
implementing it incorrectly).
Let's start with this:
|
classHandledProjectedItemList(list):
defappend(self,proj):
proj.projected =True
list.append(self,proj)
item_table =Table("items",saveddata_meta,
Column("ID",Integer,primary_key =True),
Column("itemID",Integer,nullable =False,index =True),
Column("name",String,nullable =False),
Column("timestamp",Integer,nullable =False),
)
projectedItem_table =Table("projectedItem",saveddata_meta,
Column("sourceID",ForeignKey("item.ID"),primary_key =True),
Column("destID",ForeignKey("item.ID"),primary_key =True),
Column("enabled",Integer))
mapper(Item,item_table,
properties ={"projectedItem":relation(Item,
primaryjoin =projectedItem_table.c.destID
==item_table.c.ID,
secondaryjoin =item_table.c.ID
==projectedItem_table.c.sourceID,
secondary =projectedItem_table,
collection_class =HandledProjectedItemList)
})
|
I have two tables: a `item` table, and a `projectedItem` table.
The `item` table is the main one, and contains information on
items. The projected items table is a self-referential
many-to-many relationship to the items table, where each item may
have a collection of other items attached to it. We use a custom
collection class to load this relationship so that we can modify
a special attribute in the Item objects (if they are loaded via
this relationship, they have their `projected` attribute set to
`True`).
This works great. But I also want read / write access to that
extra `enabled` column in the relationship table, while
maintaining the current functionality of loading the projected
items into this custom collection class. I haven't found any
information on that that helps, or like I said before, maybe I'm
just not using it correctly.
you would make a new class ProjectedItem and map it to the
projectedItem_table. This is the association object pattern (get
that to work first, without the "proxy" part), illustrated at
http://docs.sqlalchemy.org/en/rel_1_0/orm/basic_relationships.html#association-object
<http://docs.sqlalchemy.org/en/rel_1_0/orm/basic_relationships.html#association-object>.
Once that works, and all that's left is the inconvenience of
navigating from Item->projecteditems->item, then you can apply the
association proxy pattern to convert those two hops into just one
(http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/associationproxy.html
<http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/associationproxy.html>).
--
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] <javascript:>.
To post to this group, send email to [email protected]
<javascript:>.
Visit this group at http://groups.google.com/group/sqlalchemy
<http://groups.google.com/group/sqlalchemy>.
For more options, visit https://groups.google.com/d/optout
<https://groups.google.com/d/optout>.
--
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]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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 post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
