How to convert SQL with WHERE clause as subquery to SQLAlchemy-CodePudding

class Post(Base):
    __tablename__ = "posts"
    id = Column(Integer, primary_key=True, index=True)
    title = Column(String(50))
    
    posts_to_tags = relationship(
        "PostsToTags", back_populates="post", cascade="all,delete")

class Tag(Base):
    __tablename__ = "tags"
    id = Column(Integer, primary_key=True, index=True)
    tag = Column((String(50)), index=True)

    posts_to_tags = relationship(
        "PostsToTags", back_populates="tag", cascade="all,delete")

class PostsToTags(Base):

    __tablename__ = "posts_to_tags"

    id = Column(Integer, primary_key=True, index=True)
    post_id = Column(Integer, ForeignKey(
        "posts.id", ondelete="CASCADE"), index=True)
    post = relationship("Post", back_populates="posts_to_tags")

    tag_id = Column(Integer, ForeignKey(
        "tags.id", ondelete="CASCADE"), index=True)
    tag = relationship("Tag", back_populates="posts_to_tags")

My attemp:

        q = session.query(Post)
        if tags:
            tag_id_list = []
            for tag in tags:
                tag = session.query(Tag).filter(Tag.tag == tag).first()
                tag_id_list.append(tag.id)

            q = q.join(PostsToTags, Post.id == PostsToTags.post_id)

            q = q.filter(PostsToTags.tag_id.in_(tag_id_list))

I need to create an empty array in python to store tag_id_list. I know it's not a best, because in MYSQL. It's very clear:

SELECT DISTINCT p.id, p.title, p.body, p.created_at
FROM posts p INNER JOIN posts_to_tags ptt ON p.id = ptt.post_id
WHERE 
    (
    SELECT t.tag
    FROM tags t
    WHERE t.id = ptt.tag_id
    )
IN ("animal", "people")

And I can't understand in SQL query. There duplicate with some posts which have both tags: ("animal", "people")

But in my code. There no duplicate!:

For example:

John - #people
Peter - #people
Dog - #animal
Cat - #animal
John Tiger - #people #animal

Then with my code in Python:

tags = ["people", "animal"] ->result: John, Peter, Dog, Cat, John Tiger

And with SQL query:

tags = ["people", "animal"] ->result: John, Peter, Dog, Cat, John Tiger, John Tiger

So "John Tiger" duplicate.

So my question:

What is the reason of different results
The best way to write SQLAlchemy which don't have to create empty array. I try something like subquery but because of miss understanding, I can't write the good code without error.

CodePudding user response：

You've already done the hard work in setting up the ORM, use it to your advantage.

Something like this should get you the correct posts:

tags = ["people", "animal"]
q = (
    session.query(Post)
    .where(
        Post.posts_to_tags.any(
            PostToTags.tag.has(
                Tag.tag.in_(tags)
            )
        )
    )
)

Also look up association tables. Your PostToTags table can be made invisible, so that you can directly access Post.tags as a relationship.