Re: [sqlalchemy] Query time generated columns like hybrid properties(?)
"IF" in SQL is the CASE statement, SQLAlchemy has this here:
https://docs.sqlalchemy.org/en/13/core/sqlelement.html?highlight=case#sqlalchemy.sql.expression.case
not sure if that's the thing you are looking for
On Sat, May 9, 2020, at 3:38 PM, taha wrote:
> Sorry maybe I wasn't clear enough. Thank you for the hint but actually I have
> this hybrid expressions as well, I just stuck on how to use them. About that,
> let me try to explain in with example usage what I am looking for:
>
> I got it that I can query in this way:
> query(Post, Post.like_count, Post.dislike_count).all()
>
> But I need this extra columns for related objects as well. and I don't know
> how to express it in SA actually. Something like query(Post, Post.like_count,
> Post.dislike_count, PostComment.like_count, PostComment.dislike_count... but
> obviously thats' not the right way. So I kinda understand the use the hybrid
> expressions for my main modelPost, in that case) but not for the nested
> related models.
>
> And actually my main question that I tried to explain in the last paragraph
> was that I need something like
> user_id = request.args...
> query(Post, func.IF(PostLike.user_id == user_id, PostLike.like_type,
> None).label('user_like'))
>
> And also this as nested for PostCommentLike and PostCOmmentReplyLike.
>
> I hope It's more clear what I need.
>
> 8 Mayıs 2020 Cuma 17:27:58 UTC+3 tarihinde Mike Bayer yazdı:
>>
>>
>> On Fri, May 8, 2020, at 5:20 AM, taha wrote:
>>> Hi,
>>>
>>> This is simplified version of my post-comment-like models. There's 2 level
>>> of nesting.
>>>
>>>
>>> class Post(Base):
>>> description = db.Column(db.Text())
>>> comments = relationship("PostComment", backref="post")
>>> post_likes = relationship("PostLike", backref="post", lazy='joined')
>>> ` user_id = db.Column(db.Integer, ForeignKey('users.id'))
`
>>>
>>> class PostLike(Base):
>>> post_id = db.Column(db.Integer, ForeignKey('posts.id'))
>>> user_id = db.Column(db.Integer, ForeignKey('users.id'))
>>> like_type = db.Column(db.Enum(LikeType), nullable=False)
>>> `class PostComment(Base):`
>>> post_id = db.Column(db.Integer, ForeignKey('posts.id'))`
`` description = db.Column(db.Text())
>>> replies = relationship("CommentReply", backref="comment")
>>> comment_likes = relationship("PostCommentLike", backref="comment",
>>> lazy='joined')
>>> ` user_id = db.Column(db.Integer, ForeignKey('users.id'))
`
>>>
>>> class PostCommentLike(Base):
>>> post_comment_id = db.Column(db.Integer, ForeignKey('post_comments.id'))
>>> user_id = db.Column(db.Integer, ForeignKey('users.id'))
>>> like_type = db.Column(db.Enum(LikeType))
`
>>> ```class PostCommentReply(Base):`
>>> comment_id = db.Column(db.Integer, ForeignKey('post_comments.id'))`
`` description = db.Column(db.Text())
>>> reply_likes = relationship("PostCommentReplyLike", backref="reply",
>>> lazy='joined')
>>> ` user_id = db.Column(db.Integer, ForeignKey('users.id'))
`
>>>
>>> class ``PostCommentReplyLike(Base):
>>> reply_id = db.Column(db.Integer, ForeignKey('post_comment_replies.id'))
>>> user_id = db.Column(db.Integer, ForeignKey('users.id'))
>>> like_type = db.Column(db.Enum(LikeType))
```
>>>
>>> I have some hybrid properties for counting the related objects.
>>>
>>> @hybrid_property
>>> def like_count(self):
>>> return sum([1 if x.like_type == LikeType.LIKE else 0 for x in
>>> self.post_likes])
>>> `@hybrid_property
>>> def dislike_count(self):
>>> return sum([1 if x.like_type == LikeType.DISLIKE else 0 for x in
>>> self.post_likes])`
>>>
>>> I use a query similar to this one to bring posts and it's nested
>>> comments-replies and likes.
>>>
>>> posts = Post.query.filter(Post.user_id == 1).\
>>> outerjoin(Post.comments).\
>>> outerjoin(PostComment.replies).\
>>> all()
>>>
>>>
>>> I can make beatiful selection like:
>>> posts[0].comments[0].replies[0].like_count
>>>
>>> `It's perfect so far. But now I am a searching a way to make columns like
>>> hybrid properties but generated in query.(?)`
>>
>>
>> so the first thing I will note is that those @hybrid_propertys are great,
>> but they are python-only because they are using "if /else". If that were all
>> they did, you would only need to use Python's built in @property decorator.
>>
>> the point of hybrids is exactly that the same expression can do something in
>> a query, as if you said:
>>
>> query(Post.like_count)
>>
>> would use SQL sum(), or actually in this case I think count(), to render
>> SQL. Looking at what you have there this seems like more of a count() of
>> like_type==LIKE rather than a sum.
>>
>> The kind of SQL that would work here in a syntactially compact way, meaning
>> easy to use with a hybrid property, would be like:
>>
>> SELECT count(SELECT like_type from post_likes where
>> post_likes.post_id=posts.id) FROM posts
>>
>> that is, we are using a correlated scalar subquery. This will work just
>> fine, however correlated
Re: [sqlalchemy] Query time generated columns like hybrid properties(?)
Sorry maybe I wasn't clear enough. Thank you for the hint but actually I
have this hybrid expressions as well, I just stuck on how to use them.
About that, let me try to explain in with example usage what I am looking
for:
I got it that I can query in this way:
query(Post, Post.like_count, Post.dislike_count).all()
But I need this extra columns for related objects as well. and I don't know
how to express it in SA actually. Something like query(Post,
Post.like_count, Post.dislike_count, PostComment.like_count,
PostComment.dislike_count... but obviously thats' not the right way. So I
kinda understand the use the hybrid expressions for my main modelPost, in
that case) but not for the nested related models.
And actually my main question that I tried to explain in the last paragraph
was that I need something like
user_id = request.args...
query(Post, func.IF(PostLike.user_id == user_id, PostLike.like_type, None).
label('user_like'))
And also this as nested for PostCommentLike and PostCOmmentReplyLike.
I hope It's more clear what I need.
8 Mayıs 2020 Cuma 17:27:58 UTC+3 tarihinde Mike Bayer yazdı:
>
>
> On Fri, May 8, 2020, at 5:20 AM, taha wrote:
>
> Hi,
>
> This is simplified version of my post-comment-like models. There's 2 level
> of nesting.
>
>
> class Post(Base):
> description = db.Column(db.Text())
> comments = relationship("PostComment", backref="post")
> post_likes = relationship("PostLike", backref="post", lazy='joined')
>
> user_id = db.Column(db.Integer, ForeignKey('users.id'))
>
>
> class PostLike(Base):
> post_id = db.Column(db.Integer, ForeignKey('posts.id'))
> user_id = db.Column(db.Integer, ForeignKey('users.id'))
> like_type = db.Column(db.Enum(LikeType), nullable=False)
>
> class PostComment(Base):
> post_id = db.Column(db.Integer, ForeignKey('posts.id'))
> description = db.Column(db.Text())
> replies = relationship("CommentReply", backref="comment")
> comment_likes = relationship("PostCommentLike", backref="comment",
> lazy='joined')
> user_id = db.Column(db.Integer, ForeignKey('users.id'))
>
>
> class PostCommentLike(Base):
> post_comment_id = db.Column(db.Integer, ForeignKey('post_comments.id')
> )
> user_id = db.Column(db.Integer, ForeignKey('users.id'))
> like_type = db.Column(db.Enum(LikeType))
>
> class PostCommentReply(Base):
> comment_id = db.Column(db.Integer, ForeignKey('post_comments.id'))
> description = db.Column(db.Text())
> reply_likes = relationship("PostCommentReplyLike", backref="reply",
> lazy='joined')
> user_id = db.Column(db.Integer, ForeignKey('users.id'))
>
>
> class PostCommentReplyLike(Base):
> reply_id = db.Column(db.Integer, ForeignKey('post_comment_replies.id')
> )
> user_id = db.Column(db.Integer, ForeignKey('users.id'))
> like_type = db.Column(db.Enum(LikeType))
>
>
>
> I have some hybrid properties for counting the related objects.
>
> @hybrid_property
> def like_count(self):
> return sum([1 if x.like_type == LikeType.LIKE else 0 for x in self
> .post_likes])
> @hybrid_property
> def dislike_count(self):
> return sum([1 if x.like_type == LikeType.DISLIKE else 0 for x in
> self.post_likes])
>
> I use a query similar to this one to bring posts and it's nested
> comments-replies and likes.
>
> posts = Post.query.filter(Post.user_id == 1).\
> outerjoin(Post.comments).\
> outerjoin(PostComment.replies).\
> all()
>
>
> I can make beatiful selection like:
> posts[0].comments[0].replies[0].like_count
>
> It's perfect so far. But now I am a searching a way to make columns like
> hybrid properties but generated in query.(?)
>
>
>
> so the first thing I will note is that those @hybrid_propertys are great,
> but they are python-only because they are using "if /else". If that were
> all they did, you would only need to use Python's built in @property
> decorator.
>
> the point of hybrids is exactly that the same expression can do something
> in a query, as if you said:
>
> query(Post.like_count)
>
> would use SQL sum(), or actually in this case I think count(), to render
> SQL. Looking at what you have there this seems like more of a count() of
> like_type==LIKE rather than a sum.
>
> The kind of SQL that would work here in a syntactially compact way,
> meaning easy to use with a hybrid property, would be like:
>
> SELECT count(SELECT like_type from post_likes where post_likes.post_id=
> posts.id) FROM posts
>
> that is, we are using a correlated scalar subquery. This will work just
> fine, however correlated subqueries sometimes don't optimize well depending
> on the database backend in use and if you are against millions of rows.
> But in any case, the hybrid here would be like:
>
> @hybrid_property
> def like_count(self):
> return len([like for like in self.post_likes if like.like_type ==
> LikeType.LIKE])
>
> @like_count.expression
> def like_count(cls):
> return func.count(
>
Re: [sqlalchemy] Query time generated columns like hybrid properties(?)
On Fri, May 8, 2020, at 5:20 AM, taha wrote:
> Hi,
>
> This is simplified version of my post-comment-like models. There's 2 level of
> nesting.
>
>
> class Post(Base):
> description = db.Column(db.Text())
> comments = relationship("PostComment", backref="post")
> post_likes = relationship("PostLike", backref="post", lazy='joined')
> ` user_id = db.Column(db.Integer, ForeignKey('users.id'))
`
>
> class PostLike(Base):
> post_id = db.Column(db.Integer, ForeignKey('posts.id'))
> user_id = db.Column(db.Integer, ForeignKey('users.id'))
> like_type = db.Column(db.Enum(LikeType), nullable=False)
>
> `class PostComment(Base):`
> post_id = db.Column(db.Integer, ForeignKey('posts.id'))`
`` description = db.Column(db.Text())
> replies = relationship("CommentReply", backref="comment")
> comment_likes = relationship("PostCommentLike", backref="comment",
> lazy='joined')
> ` user_id = db.Column(db.Integer, ForeignKey('users.id'))
`
>
> class PostCommentLike(Base):
> post_comment_id = db.Column(db.Integer, ForeignKey('post_comments.id'))
> user_id = db.Column(db.Integer, ForeignKey('users.id'))
> like_type = db.Column(db.Enum(LikeType))
>
`
> ```class PostCommentReply(Base):`
> comment_id = db.Column(db.Integer, ForeignKey('post_comments.id'))`
`` description = db.Column(db.Text())
> reply_likes = relationship("PostCommentReplyLike", backref="reply",
> lazy='joined')
> ` user_id = db.Column(db.Integer, ForeignKey('users.id'))
`
>
> class ``PostCommentReplyLike(Base):
> reply_id = db.Column(db.Integer, ForeignKey('post_comment_replies.id'))
> user_id = db.Column(db.Integer, ForeignKey('users.id'))
> like_type = db.Column(db.Enum(LikeType))
```
>
>
> I have some hybrid properties for counting the related objects.
>
> @hybrid_property
> def like_count(self):
> return sum([1 if x.like_type == LikeType.LIKE else 0 for x in
> self.post_likes])
> `@hybrid_property
> def dislike_count(self):
> return sum([1 if x.like_type == LikeType.DISLIKE else 0 for x in
> self.post_likes])
`
>
> I use a query similar to this one to bring posts and it's nested
> comments-replies and likes.
>
> posts = Post.query.filter(Post.user_id == 1).\
> outerjoin(Post.comments).\
> outerjoin(PostComment.replies).\
> all()
>
>
> I can make beatiful selection like: posts[0].comments[0].replies[0].like_count
>
> `It's perfect so far. But now I am a searching a way to make columns like
> hybrid properties but generated in query.(?)`
so the first thing I will note is that those @hybrid_propertys are great, but
they are python-only because they are using "if /else". If that were all they
did, you would only need to use Python's built in @property decorator.
the point of hybrids is exactly that the same expression can do something in a
query, as if you said:
query(Post.like_count)
would use SQL sum(), or actually in this case I think count(), to render SQL.
Looking at what you have there this seems like more of a count() of
like_type==LIKE rather than a sum.
The kind of SQL that would work here in a syntactially compact way, meaning
easy to use with a hybrid property, would be like:
SELECT count(SELECT like_type from post_likes where
post_likes.post_id=posts.id) FROM posts
that is, we are using a correlated scalar subquery. This will work just fine,
however correlated subqueries sometimes don't optimize well depending on the
database backend in use and if you are against millions of rows. But in any
case, the hybrid here would be like:
@hybrid_property
def like_count(self):
return len([like for like in self.post_likes if like.like_type ==
LikeType.LIKE])
@like_count.expression
def like_count(cls):
return func.count(
select([post_likes.like_type]).
where(post_likes.post_id==cls.id).
where(post_likes.like_type == LikeType.LIKE)
)
I think that's what you're asking at least.
> ``
> `I want to mark every comment/reply with a something like
> *'current_user_like'* to show marked if the current user liked on the object.
> So I need a user_id filtering in query time which can't be done with hybrid
> properties (or can it be?).`
> ``
> `First solution coming to my mind is taking all 'liked_user_ids' data for
> every object and make a filtering on them with python list comprehensions
> etc.`
> `So is there a way that I can make it with sqlalchemy?`
>
> `Thanks in advance.`
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
>
