"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 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 sqlal...@googlegroups.com.
>>> To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/sqlalchemy/3e1b18a1-cf3c-4303-9c18-38250d6d479c%40googlegroups.com
>>>
>>> <https://groups.google.com/d/msgid/sqlalchemy/3e1b18a1-cf3c-4303-9c18-38250d6d479c%40googlegroups.com?utm_medium=email&utm_source=footer>.
>>
>
> --
> 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
> https://groups.google.com/d/msgid/sqlalchemy/485697b7-51d8-43b5-ac1f-e66631501ea5%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/485697b7-51d8-43b5-ac1f-e66631501ea5%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
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
https://groups.google.com/d/msgid/sqlalchemy/5784895d-b59e-4b51-9852-a8bad36cafbb%40www.fastmail.com.
