Peewee Access one-to-many attribute after Join

Question

I have a table called Posts

class Posts(BaseModel):
    post_id= peewee.PrimaryKeyField()
    content = CharField(null=True)
    author = CharField(null=True)

and a table called Likes

class Likes(BaseModel):
    user_like_id = ForeignKeyField(User,to_field='uniqueid', db_column='user_like_id')
    post_like_id = ForeignKeyField(Posts,to_field='post_id', db_column='post_like_id')

    class Meta:
        db_table='likes'
        primary_key = CompositeKey("user_like_id","post_like_id")

Where the post_like_id refers to the post_id. I am trying to make a single database call where I can get 10 posts and all the likes for those posts without issuing a database call for each post (iterating over each post_id and getting the count of Likes with that ID).

When I join as follows:

k = Posts.select().join(Likes,on=(Posts.post_id == Likes.post_like_id)).execute()

I have no way of accessing how many Likes were made on a single post.

Answer 1

Get the 10 most-liked posts, e.g. (JOIN.LEFT_OUTER is needed to include posts that have zero likes, fyi):

query = (Posts
         .select(Post, fn.COUNT(Likes.id).alias('ct'))
         .join(Likes, JOIN.LEFT_OUTER)
         .group_by(Post)
         .order_by(fn.COUNT(Likes.id).desc())
         .limit(10))