On Thu, Dec 11, 2014 at 2:44 PM, Boris Sabatier
<sabatier.bo...@gmail.com> wrote:
> 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.
The problem is that you've got a foreign key from "category.uid" to
"item.uid", and also a foreign key from "item.parent_id" to
"category.uid". Normally, foreign keys are defined in the "CREATE
TABLE" sql statement, but that can't work when you have a circular
dependency, because whichever table you define first will try to refer
to another table which doesn't exist yet.
The solution is "use_alter", which is a flag you can pass when
defining your ForeignKey:
http://docs.sqlalchemy.org/en/rel_0_9/core/constraints.html#sqlalchemy.schema.ForeignKeyConstraint.params.use_alter
When it is set, SQLAlchemy will create the table *without* the foreign
key, then when all the tables have been defined it will run an "ALTER
TABLE" statement to create the foreign key.
Hope that helps,
Simon
--
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.
