Today I worked on my blog's modelbooks and decided to attach a comment count to Entry (this is a blog entry). I have implemented the data representation in the application using SQLAlchemy , let me remind you that this is the implementation of the Data Mapper , in contrast to the current implementations of the data source layer, which are currently fashionable (mainly due to Django and Rails).



I have no accumulator in the entries_table table to count comments, so I have to count count on comments every time. But how to do it right?





')

Download on demand

The first thing that comes to mind is to make the Entry.comments_count attribute loadable on demand (lazy-loading attribute) and hang up a query on it ::



SELECT COUNT(*) FROM comments_table WHERE comments_table.entry_id == <ENTRY_ID>





Everything is simple, we turn to the attribute - the request occurs, we do not appeal - it does not. But what if the appeal happens too often, for example, as it is now done on my homepage on the blog (the number of comments to each post is indicated). This will require n + 1 request, where n is the number of records per page. Not very good ...

We join tables

And now the most interesting: SQLAlchemy is able to map classes not only to separate tables, but also to JOINs and even SELECTs. That is all we need to do is compile a SQL query ::



SELECT < entries_table>, COUNT(comments_table.id)

FROM entries_table

LEFT OUTER JOIN comments_table

ON entries_table.id == comments_table.entry_id

GROUP BY entries_table.id





Here is what it looks like using the metadata description ( Metadata mapping ) SQLAlchemy ::



entries_with_comments = select(

[

entries_table,

func.count(comments_table.c.id).label("comments_count")

],

from_obj=[entries_table.outerjoin(comments_table)],

group_by=[c for c in entries_table.c]

).alias("entries_with_comments")





It's pretty simple. Now map the classic Entry to this SELECT ::



mapper(Entry, schema.entries_with_comments,

primary_key=[schema.entries_with_comments.c.id],

)





All is ready! Now, when getting Entry entries, we will have an attribute for each instance comments_count. And all this for one request.



Django.orm and Rails with their ActiveRecord nervously smoking on the sidelines;).