sql - how to define sqlalchemy orm models for difficult logic -

sql - how to define sqlalchemy orm models for difficult logic -

i using sqlalchemy project. have hard logic define models.

i have 4 database table

item_type - item_type_id(tinyint) - item_type_name (varchar) --item_types can "post(1)", "product(2)" , "gallery(3)" tag - tag_id(int) - tag_name(varchar) item_tag - item_type_id (tinyint) - item_id (int) -- item_id can post_id or product_id or gallery_id - tag_id(int) post - post_id - post_title - post_content

my models

class tag(base): __tablename__ = 'tag' tag_id = column(integer, primary_key=true) tag_name = column(string(255)) class post(base): __tablename__ = 'post' post_id = column(integer, primary_key=true) post_title = column(string(255)) post_title = column(text) #tags relation here class itemtag(base): __tablename__ = 'item_tag' item_type_id = column(integer, primary_key=true) item_id = column(integer, primary_key=true) tag_id = column(integer, foreignkey("tag.tag_id"), primary_key=true)

getting post tags sql (post_id = 12)

select tag.* item_tag inner bring together tag tag on it.tag_id=tag.tag_id it.item_type_id=1 , it.item_id=12

i not know how define relation in post model tags. right way post tags relation?

you can utilize primaryjoin argument orm.relationship() specify complex bring together conditions: http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#relationship-primaryjoin

for specific case, define following:

class="lang-py prettyprint-override">from sqlalchemy import orm class post(base): ... tags = orm.relationship('tag', primaryjoin='and_(itemtag.tag_id==tag.tag_id, itemtag.item_id==post.post_id, itemtag.item_type_id==1)')

i haven't tested may need modifications plenty on right track.

sql orm sqlalchemy