2

Please consider the following use case.

There is a Post model as well as a Tag model. Both of them have a many-to-many relationship between them. A post can have multiple tags while a tag can have multiple posts.

In order to attain this use case, I have implemented a mapping table called, PostTag and it looks like as follows

from database.base import Base
from sqlalchemy import Column, ForeignKey, Integer
from sqlalchemy.orm import relationship
from .model_post import ModelPost
from .model_tag import ModelTag


class PostTag(Base):
   __tablename__ = 'posttag'
   post_id = Column("post_id",Integer, ForeignKey('post.id'), primary_key = True)
   tag_id = Column("tag_id",Integer, ForeignKey('tag.id'), primary_key = True)

With this setup, I can successfully query all the tags for a given post and vice versa, but I dont know to add a new association for a given post and tag.

Please see the below screenshot on how I query the related tags and posts off of each other.

enter image description here

If there is anything that I am missing here, please let me know.

Thanks

Gagan
  • 5,416
  • 13
  • 58
  • 86
  • You don't use PostTag while you're creating records, SQL Alchemy does that for you if you made a relationship. Your relationship field is a list and you append instances to it (if you name it `tags`): `post.tags.append(tag)`. It will be created in your `posttag` table after session commit. – ipaleka Aug 11 '19 at 08:40
  • Thanks for the help. Can you please tell me , how can I pass the `tag` value in the query so that I can add it to the postTag table and make a relationship with the post. Thanks – Gagan Aug 11 '19 at 08:59
  • There's no query involved in creation, just an instance appended to list. Retrieve ModelTag instance from the database by its id or create a new one with `tag = ModelTag()` and append it. – ipaleka Aug 11 '19 at 09:06
  • Suppose , a I have an existing tag and an existing post. Now during the update of the post, I want to assign it to the existing tag. I think i will need to pass the tag , in order to create the association between the post and the tag. – Gagan Aug 11 '19 at 21:06
  • Not sure if I understand that. If it isn't about `post.tags.append(tag)` then it's probably about `tag.posts.append(post)`, where `tags` and `posts` are names for relationship fields. – ipaleka Aug 11 '19 at 21:16
  • Sorry, I am not understanding it either. Would you mind, if you could post some example please or link me to a example code that I can refer to please - that would be a lot of help. My sincere apologies for not understanding that thing. Thanks – Gagan Aug 12 '19 at 08:45

1 Answers1

0

I peeped into some my old code, setup models like this:

from sqlalchemy.orm import sessionmaker

posttag = Table(
    "posttag",
    Base.metadata,
    Column("id", INTEGER, Sequence("seq_posttag_id"), primary_key=True),
    Column("post_id", INTEGER, ForeignKey("post.id")),
    Column("tag_id", INTEGER, ForeignKey("tag.id")),
)


class ModelTag(Base):
    __tablename__ = "tag"

    id = Column(INTEGER, Sequence("seq_tag_id"), primary_key=True)
    name = Column(VARCHAR(40), nullable=False)


class ModelPost(Base):
    __tablename__ = "post"

    id = Column(INTEGER, Sequence("seq_post_id"), primary_key=True)
    name = Column(VARCHAR(100), nullable=False)
    tags = relationship(Tag, secondary=posttag)

And so when you create a new Post, then you add a tag to it without directly referencing posttag table:

post = Post(name="foo")

tag = session.query(ModelTag).filter_by(name='some').first()
post.append(tag)

session = Session()
session.add(post)
session.commit()
ipaleka
  • 3,745
  • 2
  • 13
  • 33
  • Oh this is sqlalchemy. I was more of interested to know graphene-sqlalchemy for graphql. – Gagan Aug 12 '19 at 15:57
  • That's [the same](https://github.com/alexisrolland/flask-graphene-sqlalchemy/wiki/Flask-Graphene-SQLAlchemy-Tutorial). – ipaleka Aug 12 '19 at 16:10
  • Ok - then I have one additional question please - how do I pass `some` from my graphql query? I believe `some` is hardcoded in this query that you have here. I need to pass a tag from the query whenever I am adding/updating a post. – Gagan Aug 12 '19 at 18:22
  • That's an example where I get a ModelTag instance filtered by some field, that I made up, called name. You should find the way to locate a distinct ModelTag instance by a filed you've got in it, by the value you have got from your query. Whatever way you get a single instance of ModelTag, it will fit and you may append it to some MoelPost instance. – ipaleka Aug 12 '19 at 18:35