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
> 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/ba5f0b2f-fae9-4196-8c10-1f2d76009601%40www.fastmail.com.
