Hi,
I have a problem to do the model I need.
I have 3 types of object : Category, Picture and Video.
All of these types have some common part, like a name.
Also I want to be able to select all object with a name like "bar"
(Category, Picture and Video)
So I use the Joined_table inheritance pattern. All work fine.
But I want to add : "they all can be child of a Category"
So I done this model :
class Item(Base):
__tablename__ = 'item'
uid = Column(Integer, primary_key=True)
name = Column(String(50))
item_type = Column(String(50))
parent_id = Column(Integer, ForeignKey('category.uid'), nullable=True)
__mapper_args__ = {
'polymorphic_identity':'item',
'polymorphic_on':item_type,
}
###############################################################
class Picture(Item):
__tablename__ = 'picture'
uid = Column(Integer, ForeignKey('item.uid'), primary_key=True)
__mapper_args__ = {
'polymorphic_identity':'picture',
}
###############################################################
class Video(Item):
__tablename__ = 'picture'
uid = Column(Integer, ForeignKey('item.uid'), primary_key=True)
__mapper_args__ = {
'polymorphic_identity':'video',
}
###############################################################
class Category(Item):
__tablename__ = 'category'
uid = Column(Integer, ForeignKey('item.uid'), primary_key=True)
__mapper_args__ = {
'inherit_condition': (uid==Item.uid),
'polymorphic_identity': 'category'
}
First I add parent_id Column in the Item class. But when I do this, I have
AmbiguousForeignKeysError exception:
sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between
'item' and 'category'; tables have more than one foreign key constraint
relationship between them. Please specify the 'onclause' of this join
explicitly.
After some research on internet (documentation, stack overflow and this
maling list) I heard about the 'inherit_condition'.
So I add it in the __mapper_args__ of Category. But when I do that, I have
a CircularDependencyError:
sqlalchemy.exc.CircularDependencyError: Circular dependency detected.
Cycles:
{
Table(
'item',
MetaData(bind=None),
Column('uid', Integer(), table=<item>, primary_key=True,
nullable=False),
Column('name', String(length=50), table=<item>),
Column('item_type', String(length=50), table=<item>),
Column('parent_id', Integer(), ForeignKey('category.uid'),
table=<item>),
schema=None),
Table(
'category',
MetaData(bind=None),
Column('uid', Integer(), ForeignKey('item.uid'), table=<category>,
primary_key=True, nullable=False),
schema=None)
}
all edges:
{
(
Table(
'item',
MetaData(bind=None),
Column('uid', Integer(), table=<item>, primary_key=True,
nullable=False),
Column('name', String(length=50), table=<item>),
Column('item_type', String(length=50), table=<item>),
Column('parent_id', Integer(), ForeignKey('category.uid'),
table=<item>),
schema=None),
Table(
'category',
MetaData(bind=None),
Column('uid', Integer(), ForeignKey('item.uid'),
table=<category>, primary_key=True, nullable=False),
schema=None)
),
(
Table(
'item',
MetaData(bind=None),
Column('uid', Integer(), table=<item>, primary_key=True,
nullable=False),
Column('name', String(length=50), table=<item>),
Column('item_type', String(length=50), table=<item>),
Column('parent_id', Integer(), ForeignKey('category.uid'),
table=<item>),
schema=None),
Table(
'video',
MetaData(bind=None),
Column('uid', Integer(), ForeignKey('item.uid'), table=<video>,
primary_key=True, nullable=False),
schema=None)
),
(
Table(
'item',
MetaData(bind=None),
Column('uid', Integer(), table=<item>, primary_key=True,
nullable=False),
Column('name', String(length=50), table=<item>),
Column('item_type', String(length=50), table=<item>),
Column('parent_id', Integer(), ForeignKey('category.uid'),
table=<item>),
schema=None),
Table(
'picture',
MetaData(bind=None),
Column('uid', Integer(), ForeignKey('item.uid'),
table=<picture>, primary_key=True, nullable=False),
schema=None)
),
(
Table(
'category',
MetaData(bind=None),
Column('uid', Integer(), ForeignKey('item.uid'),
table=<category>, primary_key=True, nullable=False),
schema=None),
Table(
'item',
MetaData(bind=None),
Column('uid', Integer(), table=<item>, primary_key=True,
nullable=False),
Column('name', String(length=50), table=<item>),
Column('item_type', String(length=50), table=<item>),
Column('parent_id', Integer(), ForeignKey('category.uid'),
table=<item>),
schema=None)
)
}
I joined the python file use to test some solutions.
Have you got an idea to solve my issue ?
Thanks a lot
Boris
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import (
scoped_session,
sessionmaker,
)
from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
BASE = declarative_base()
class Item(BASE):
__tablename__ = 'item'
uid = Column(Integer, primary_key=True)
name = Column(String(50))
item_type = Column(String(50))
parent_id = Column(Integer, ForeignKey('category.uid'), nullable=True)
__mapper_args__ = {
'polymorphic_identity':'item',
'polymorphic_on':item_type,
}
###############################################################
class Picture(Item):
__tablename__ = 'picture'
uid = Column(Integer, ForeignKey('item.uid'), primary_key=True)
__mapper_args__ = {
'polymorphic_identity':'picture',
}
###############################################################
class Video(Item):
__tablename__ = 'video'
uid = Column(Integer, ForeignKey('item.uid'), primary_key=True)
__mapper_args__ = {
'polymorphic_identity':'video',
}
###############################################################
class Category(Item):
__tablename__ = 'category'
uid = Column(Integer, ForeignKey('item.uid'), primary_key=True)
__mapper_args__ = {
'inherit_condition': (uid==Item.uid),
'polymorphic_identity': 'category'
}
DB_SESSION = scoped_session(sessionmaker())
engine = create_engine('sqlite://', echo=True)
DB_SESSION.configure(bind=engine)
BASE.metadata.create_all(engine)
