Python sqlalchemy: How do I use the relation to get count data from many to many

Question

I have a User table

I have a Post table

Users can vote posts, here I use a UserVotesPost table which is a many to many relation.

Here is the code:

class UserVotesPost(Base):
    __tablename__ = 'uservotesposts'
    user_id = Column(Integer, ForeignKey('users.id'), primary_key=True)
    post_id = Column(Integer, ForeignKey('posts.id'), primary_key=True)
    likes_post = Column(Boolean, nullable=False)
    date_added = Column(DateTime, nullable=False)
    child = relationship("Post")

class User(UserMixin, Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    email = Column(Text, nullable=False, unique=True)
    password = Column(Text, nullable=False)
    children_UserVotesPost = relationship("UserVotesPost")
    # etc ...

class Post(Base):
    __tablename__ = 'posts'
    id = Column(Integer, primary_key=True)
    title = Column(Text, nullable=False)
    description = Column(Text, nullable=True)
    created_on = Column(DateTime, nullable=False)
    updated_on = Column(DateTime, nullable=False)
    views = Column(Integer, nullable=False, default=0)
    visible_to_tier_id = Column(Integer, nullable=True)
    hidden_post = Column(Boolean, nullable=False, default=False)
    # etc ...

Adding data works fine, but now I want to display the data in view by using the relation & correct syntax.

What I want exactly is to display the total amount of likes and dislikes for a certain post in view.

My ideas so far:

I could simply query all UserVotesPost and create nested for ... if loops in view to compare and count the posts. That would look something like that:

all_votes = UserVotesPost.query.all()
all_posts = Post.query.all()

In view:

{% for post in all_posts  %}
    {% for vote in all votes %}
         {% if post.id == vote.post_id %}
             {% if vote.likes_post == True %}
                 # increase count for likes true 
             {% else %}   
                 # increase count for likes false
             {% endif %}
         {% endif %}
     {% endfor %} 
{% endfor %}

But this is a complete workaround, which does not use the DB relations and is probably very bad for performance. But it would work.

I am currently playing around with the relation, but so far I only managed to get all user likes/dislikes by using the relation:

for vote in current_user.children_UserVotesPost:
    print (vote.likes_post)

More than one way to skin a cat: https://stackoverflow.com/questions/11567666/count-number-of-rows-in-a-many-to-many-relationship-sqlalchemy, https://stackoverflow.com/questions/25081358/count-of-related-records-in-many-to-many-relationship, https://stackoverflow.com/questions/13378758/sqlalchemy-count-related. Note that if you want to work on posts and their votes, it'd be easier if you defined such a relationship on `Post`. If such a relationship existed, you could also just `len([v for v in post.votes if v.likes_post])` as a last resort. — Ilja Everilä, Apr 12 '18 at 10:39
I found all these threads aswell, but none of these explain how to access the data in view, f.e. how I call `session.query(Entry).with_parent(mytag).count()` in view. I iterate through the object in view not on the py file. — Roman, Apr 12 '18 at 11:48
More than 1 included a `column_property` or a `hybrid_property` approach. And you can always just pass additional data to your view, as you're already doing. — Ilja Everilä, Apr 12 '18 at 11:50
The hybrid property sounds very promising, I will try to use it. Sadly I was not able to find this thread, probably I used the wrong keywords. Thanks again! — Roman, Apr 12 '18 at 12:19
I solved it, thank you Ilja. Answer below in case I missed something. — Roman, Apr 12 '18 at 13:02

score -1 · Accepted Answer · answered Apr 12 '18 at 13:02

Thanks to @Ilja Everilä for showing me the correct threads. I followed the solution proposed in this thread: SO Thread.

You can use @property in your model class to define certain data which needs to be shown somewhere.

In addition to that I had also to link Post to UserVotesPost with a relation:

class Post(Base):
    __tablename__ = 'posts'
    id = Column(Integer, primary_key=True)
    title = Column(Text, nullable=False)
    description = Column(Text, nullable=True)
    created_on = Column(DateTime, nullable=False)
    updated_on = Column(DateTime, nullable=False)
    views = Column(Integer, nullable=False, default=0)
    visible_to_tier_id = Column(Integer, nullable=True)
    hidden_post = Column(Boolean, nullable=False, default=False)
    # This has been added:
    children_UserVotesPost = relationship("UserVotesPost")

I created two properties inside the Post class:

@property
def count_votes_up(self):
    return object_session(self).query(UserVotesPost).filter(UserVotesPost.likes_post == True).with_parent(self).count()

@property
def count_votes_down(self):
    return object_session(self).query(UserVotesPost).filter(UserVotesPost.likes_post == False).with_parent(self).count()

Also you will need to import object_session:

from sqlalchemy.orm import object_session

Now its very easy to access the data in python or view:

all_posts = Post.query.all()
for p in all_posts:
    print (p.id, ' has ', p.count_votes_up, ' ups')
    print (p.id, ' has ', p.count_votes_down, ' downs')

Downvoted because this does a new query every time you access each property (`@property` is standard Python; SQLAlchemy doesn’t do anything with it). It would be better to make a hybrid property or something similar in order to "load" it only once when the object is retrieved from the database. (see also [this](https://gist.github.com/hest/8798884)) — bfontaine, May 02 '23 at 10:10

Python sqlalchemy: How do I use the relation to get count data from many to many

1 Answers1