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.
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'))
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.
