CODEWITHSUNDEEP
pythonsqlalchemy
Vitalii Ponomar
Vitalii Ponomar

Reputation: 10936

SQLAlchemy: how to make relationship between few models?

For example, there are such models:

class User(Base):
    photo_id = Column(ForeignKey('photo.id'))

class Group(Base):
    photo_id = Column(ForeignKey('photo.id'))

class Photo(Base):
    __tablename__ = 'photo'

    user = relationship('User', backref='photo')
    group = relationship('Group', backref='photo')

But in last model relationship to User and Group is not good because in one case first relationship will be None and in other case second relationship will be None (because photo owner can be only user or group, but not both)... And if there will be more than 2 models with foreignkeys to model Photo - situation will be even worse. How to do such relationship correct?

Thanks in advance!

Upvotes: 0

Views: 180

Answers (3)

Tyler Liu
Tyler Liu

Reputation: 20356

Use table inheritance: http://docs.sqlalchemy.org/en/rel_0_7/orm/extensions/declarative.html#joined-table-inheritance

I recommend this slide to you: http://www.slideshare.net/tyler4long/quickorm . It is about quick_orm, which is base on SQLAlchemy. You will see how the same problem is resolved by means of table inheritance.

Slide 7: many models should have relationship with "comments" Slide 8: add a parent class named "Commentable" to solve the problem.

The syntax is different from SQLAlchemy, but you can get the main idea.

Upvotes: 1

van
van

Reputation: 76962

I do not think there is one correct way of modeling this kind of relationships. Cardinality, navigability are also facts to consider.

To a solution very similar to your modeling problem, see Generic Associations examples. The examples might look somewhat complicated at first, but if you read Mike's blog on Polymorphic Associations with SQLAlchemy it should be pretty clear what is happening there. You will end up with somewhat different model, and navigating back from Photo to the correct parent by single attribute (parent or owner) might not be achievable, but do you really need to navigate the relationship from the side of Photo?

Upvotes: 0

lucemia
lucemia

Reputation: 6617

If your User and Group are not stored in the same table, there is nothing wrong to defined them with two relationship. These two relationship means two different SQL query, and you actually needs these two different query in your case.

If your User and group can be stored in the same table, you can use inheritance. and create a relationshop to the parent table http://docs.sqlalchemy.org/en/latest/orm/inheritance.html

or create a view for that http://docs.sqlalchemy.org/en/rel_0_7/core/schema.html#reflecting-views

Upvotes: 1

Related Questions