On Sat, Nov 25, 2017 at 9:41 AM, Stephan Hügel <[email protected]> wrote:
> I'm trying to model a series of hierarchical relationships:
>
> Widgets can have many Themes, which can be shared by many Widgets
> Themes can have many Sections, which can be shared by many Themes:
>
> Widgets - n:m - Themes - n:m - Sections
>
> I'd also like to define a Content table with a single Unicode field, which
> depends on a unique combination of Widget, Theme and Section IDs, and which
> I can access as a scalar from Sections.
so *usually*, as you might imagine, people end up having the Content
table be something along the lines of a three-way association table,
that is, you wouldn't have individual association tables between W, T,
S and the Content table would just make the link. However, you don't
need to do it that way, what you want to do is fine.
>
> I've defined Widgets, Themes and Sections, and join tables to make these
> work:
>
> # Theme-Section many-to-many join table Ts_Assoc = Table('ts_assoc',
> metadata, Column('themes_id', Integer, ForeignKey('themes.id')),
> Column('sections_id', Integer, ForeignKey('sections.id')), ) # Widget-Theme
> many-to-many join table Wt_Assoc = Table('wt_assoc', metadata,
> Column('widgets_id', Integer, ForeignKey('widgets.id')), Column('themes_id',
> Integer, ForeignKey('themes.id')) ) class Sections(Base): __tablename__ =
> "sections" id = Column(Integer, primary_key=True) section =
> Column(Unicode(100), index=False, unique=False) themes =
> relationship("Themes", secondary="ts_assoc", back_populates="sections")
> class Themes(Base): __tablename__ = "themes" id = Column(Integer,
> primary_key=True) theme = Column(Unicode(100), index=True, unique=True)
> widgets = relationship("Widgets", secondary="wt_assoc",
> back_populates="themes") sections = relationship("Sections",
> secondary="ts_assoc", back_populates="themes") class Widgets(Base):
> __tablename__ = "widgets" id = Column(Integer, primary_key=True) name =
> Column(Unicode(200), index=True, unique=True) themes =
> relationship("Themes", secondary=Wt_Assoc, back_populates="widgets")
>
> This seems to work correctly; I can query like so for a given Widget:
>
> # select Widget A only q = sess.query(Widgets) \ .join(Themes,
> Widgets.themes) \ .join(Sections, Themes.sections) \ .filter(Widgets.name ==
> u'Widget A') \ .one()
>
> And loop through its Themes, and for each Theme, loop through its sections.
>
> The difficulty arises when I'm defining Content. So far, I've got:
>
> class Content(Base): __tablename__ = "content" txt = Column(Unicode(100),
> primary_key=True) widgets_id = Column(Integer, ForeignKey('widgets.id'),
> nullable=False) themes_id = Column(Integer, ForeignKey('themes.id'),
> nullable=False) ForeignKeyConstraint(['widgets_id', 'themes_id'],
> ['wt_assoc.widget_id', 'wt_assoc.themes_id']) sections_id = Column(Integer,
> ForeignKey('sections.id'))
>
so yes, Content refers to the combinations of W, T, S so you foreign
key to the association table. That means you don't need the
ForeignKey local to widgets_id and themes_id, just the
ForeignKeyConstraint you have is enough. This implies that this
Content record cannot exist unless that W / T combination actually
exists which is great, though this is a little more work to map. What
I don't see is where you are constraining this Content to the fact
that the given section id is in fact linked to the theme. You don't
*have* to do this, like you don't have to refer to "wt_assoc" either,
but if you're going for "constrain Content to when an S / T
combination actually exists" then you would want to FK out to ts_assoc
as well.
Also, I would make sure the association tables have a primary key
constraint set up, a database like Postgresql won't let you create
those foreign key constraints unless it refers to a primary key or
unique set of columns:
Wt_Assoc = Table('wt_assoc', metadata,
Column('widgets_id', Integer, ForeignKey('widgets.id'), primary_key=True),
Column('themes_id', Integer, ForeignKey('themes.id'), primary_key=True)
)
then the multiple composites on content:
__table_args__ = (
ForeignKeyConstraint(['widgets_id', 'themes_id'],
['wt_assoc.widget_id', 'wt_assoc.themes_id']),
ForeignKeyConstraint(['sections_id', 'themes_id'],
['ts_assoc.sections_id', 'ts_assoc.themes_id']),
)
now yes this is more difficult on the persistence side since to insert
a Content you need all those identifiers set up as well as the
association rows need to have been INSERTed, and the ORM usually wants
to declare that "wt_assoc must be inserted before Content" using the
relationship() construct, and that requires a mapped target, so
something here has to change; either we map those association tables
and don't use "secondary" for persistence, or we do some trickery in
how Content is flushed so that the persistence occurs correctly.
If we don't map the association tables, it's not easy to ensure that
in the scope of a single flush, Content is INSERTed before
Theme.sections / Theme.widgets are inserted (and also the opposite
series of steps on the DELETE side). It does just happen to "just
work" in every test I can run however looking at the dependency graph
this is not guaranteed. There are ways to make this happen with the
ORM, but it would involve manipulation of the internal state of the
unit of work within a flush-time event, which at the moment is not a
thing that's documented / supported (e.g. could change someday). It is
at least something that would be API-level, but still very esoteric.
So the most expedient way would be that you always ensure your W / S /
T combination is flushed before you add a Content object referring to
them to the Session. This might be how your application works
anyway.
I'm not totally sure what works better for you, so let me at least
show you that version:
class Content(Base):
__tablename__ = "content"
txt = Column(Unicode(100), primary_key=True)
widgets_id = Column(Integer, nullable=False)
themes_id = Column(Integer, nullable=False)
sections_id = Column(Integer, nullable=False)
__table_args__ = (
ForeignKeyConstraint(
['widgets_id', 'themes_id'],
['wt_assoc.widgets_id', 'wt_assoc.themes_id']),
ForeignKeyConstraint(
['sections_id', 'themes_id'],
['ts_assoc.sections_id', 'ts_assoc.themes_id']),
)
_widget = relationship(
"Widgets", primaryjoin="Widgets.id == foreign(Content.widgets_id)")
_theme = relationship(
"Themes", primaryjoin="Themes.id == foreign(Content.themes_id)")
section = relationship(
"Sections", backref="content",
primaryjoin="Sections.id == foreign(Content.sections_id)")
def __init__(self, txt, section, widget, theme):
self.txt = txt
self.section = section
self._widget = widget
self._theme = theme
then persist / query:
sess = Session(e)
w = Widgets(name='Widget A')
s = Sections()
t = Themes()
w.themes.append(t)
s.themes.append(t)
sess.add_all([w, s, t])
# make sure w.themes, s.themes are flushed
sess.flush()
c = Content("txt", s, w, t)
sess.add(c)
sess.commit()
# select Widget A only
w = sess.query(Widgets) \
.join(Themes, Widgets.themes) \
.join(Sections, Themes.sections) \
.filter(Widgets.name == u'Widget A') \
.one()
print(w.themes[0].sections[0].content[0].txt)
so, that's a start. If you want the flush process to be fully
automated (also for deletes, removals) you would want to map classes
to Ts_Assoc and Wt_Assoc, and then do persistence in terms of those
mapped classes. For the Query side you can still make use of
Widgets.themes / Themes.sections and "secondary", you would just
establish viewonly=True. This is the "safer" way to go and after
working it up, it does look a little cleaner, sort of, here's a full
demo (since I have to make these things run anyway), note on the query
side, nothing changes:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
metadata = Base.metadata
class Content(Base):
__tablename__ = "content"
txt = Column(Unicode(100), primary_key=True)
widgets_id = Column(Integer, nullable=False)
themes_id = Column(Integer, nullable=False)
sections_id = Column(Integer, nullable=False)
__table_args__ = (
ForeignKeyConstraint(
['widgets_id', 'themes_id'],
['wt_assoc.widgets_id', 'wt_assoc.themes_id']),
ForeignKeyConstraint(
['sections_id', 'themes_id'],
['ts_assoc.sections_id', 'ts_assoc.themes_id']),
)
wt = relationship("Wt_Assoc")
ts = relationship("Ts_Assoc")
section = relationship(
"Sections", backref="content",
primaryjoin="Sections.id == foreign(Content.sections_id)",
viewonly=True)
class Ts_Assoc(Base):
__tablename__ = 'ts_assoc'
themes_id = Column('themes_id', Integer, ForeignKey('themes.id'),
primary_key=True)
sections_id = Column('sections_id', Integer,
ForeignKey('sections.id'), primary_key=True)
theme = relationship("Themes")
section = relationship("Sections")
class Wt_Assoc(Base):
__tablename__ = 'wt_assoc'
widgets_id = Column('widgets_id', Integer,
ForeignKey('widgets.id'), primary_key=True)
themes_id = Column('themes_id', Integer, ForeignKey('themes.id'),
primary_key=True)
widget = relationship("Widgets")
theme = relationship("Themes")
class Sections(Base):
__tablename__ = "sections"
id = Column(Integer, primary_key=True)
section = Column(Unicode(100), index=False, unique=False)
themes = relationship("Themes", secondary="ts_assoc",
back_populates="sections", viewonly=True)
class Themes(Base):
__tablename__ = "themes"
id = Column(Integer, primary_key=True)
theme = Column(Unicode(100), index=True, unique=True)
widgets = relationship("Widgets", secondary="wt_assoc",
back_populates="themes", viewonly=True)
sections = relationship("Sections", secondary="ts_assoc",
back_populates="themes", viewonly=True)
class Widgets(Base):
__tablename__ = "widgets"
id = Column(Integer, primary_key=True)
name = Column(Unicode(200), index=True, unique=True)
themes = relationship("Themes", secondary="wt_assoc",
back_populates="widgets", viewonly=True)
e = create_engine("mysql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
sess = Session(e)
w = Widgets(name='Widget A')
s = Sections()
t = Themes()
wt = Wt_Assoc(widget=w, theme=t)
ts = Ts_Assoc(section=s, theme=t)
# this is the part that's "cleaner", e.g. we explicitly see Content mapped
# to the associations which are themselves entities
c = Content(txt="txt", ts=ts, wt=wt)
# unit of work eats this right up, no problem
sess.add_all([wt, ts, c])
sess.commit()
# query time - use those viewonly=True relationships
w = sess.query(Widgets) \
.join(Themes, Widgets.themes) \
.join(Sections, Themes.sections) \
.filter(Widgets.name == u'Widget A') \
.one()
print(w.themes[0].sections[0].content[0].txt)
> I'm not sure that it's correct, and I'm not sure how to:
>
> - Define the relationship to Sections such that the three foreign key values
> are correctly constraining it
>
> - Modify the query above to take account of the new Content table
>
> - Add Content records via Section, and how the __init__ method would look
> more generally.
>
> In short, I'm tying myself in knots trying to figure out how to set up the
> join conditions between Content and Sections.
>
> --
> 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 post to this group, send email to [email protected].
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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 post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
