Gino: Is there IN cause in gino

[saeedghx68]

• GINO version: 0.8.0
• Python version: 3.7.0
• asyncpg version: 0.17.0
• aiocontextvars version: 0.2.0
• PostgreSQL version: 10

Description

My model definition are:

class Tag(BaseClass):
    __tablename__ = 'tags'

    tag_name = db.Column(db.String(64), nullable=False)

    def __str__(self):
        return f'{self.tag_name}'


class Tweet(BaseClass):
    __tablename__ = 'tweets'

    msg = db.Column(db.String(280), nullable=False)
    user_id = db.Column(None, db.ForeignKey('users.id'))

    def __str__(self):
        return f'{self.user_id} -> {self.msg}'


class TagTweet(BaseClass):
    __tablename__ = 'tag_tweet'

    tag_id = db.Column(db.ForeignKey('tags.id'))
    tweet_id = db.Column(db.ForeignKey('tweets.id'))

    def __str__(self):
        return f'{self.tag_id} - {self.tweet_id}'

Now, I have a post method to fetch all data which has specific tag
My query is:

select * from tweets where tweets.id IN(select tweet_id from tag_tweet where tag_tweet.tag_id = (SELECT tags.id FROM tags WHERE tags.tag_name='test'))

I want to run this query using gino
I there any way to fetch data using gino without raw query?
How do I do that?

[wwwjfy]

A literal translation could be

q = db.select(
    [Tweet.id, Tweet.msg, Tweet.user_id]
).where(
    Tweet.id.in_(
        db.select([TagTweet.tweet_id]).where(
            TagTweet.tag_id ==
            db.select([Tag.id]).where(Tag.tag_name == tag_name)
        )
    )
)
print(await q.gino.model(Tweet).first())

[saeedghx68]

thanks a lot , how do I set limit and offset to use pagination
for example same as this sql command:

SELECT * FROM tweets WHERE tweets.id IN(SELECT tweet_id FROM tag_tweet WHERE tag_tweet.tag_id = (SELECT tags.id FROM tags WHERE tags.tag_name='test')) ORDER BY created_at limit 10 offset 1

[wwwjfy]

add .limit(10).offset(1) at the end of query.
refs https://docs.sqlalchemy.org/en/latest/core/selectable.html#sqlalchemy.sql.expression.Select.offset

[saeedghx68]

Thanks