SQLAlchemy rocks!
Today, he was engaged in models of his blog and decided to attach a comment counter to Entry (this is a blog entry). Presentation of data in the application is implemented using SQLAlchemy , I remind you that this is a Data Mapper implementation , unlike the current (mainly due to Django and Rails) Active Record implementations of the data source layer.
For counting comments, I don’t have any battery in the entries_table, so I have to count count on the comments every time. But how to do it right?
The first thing that comes to mind is to make the Entry.comments_count attribute downloadable on demand (lazy-loading attribute) and hang up a request on it :: It's simple, we turn to the attribute - the request comes in, we don't turn it in - it doesn't. But what if the appeal happens too often, for example, how now it is done on my main page in the blog (the number of comments for each entry is indicated). This will require an n + 1 query, where n is the number of records per page. Not very good ...
And now the most interesting: SQLAlchemy can map classes not only to individual tables, but also to JOINs and even SELECTs. That is, all we need to do is make a SQL query :: Here is what it looks like using the metadata description ( Metadata mapping ) SQLAlchemy :: It's pretty simple. Now we map the classic Entry to this SELECT :: Everything is ready! Now, when we get Entry entries, we will have the comments_count attribute on each instance. And all this in one request. Django.orm and Rails with their ActiveRecord nervously smoke aside;).
For counting comments, I don’t have any battery in the entries_table, so I have to count count on the 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 downloadable on demand (lazy-loading attribute) and hang up a request on it :: It's simple, we turn to the attribute - the request comes in, we don't turn it in - it doesn't. But what if the appeal happens too often, for example, how now it is done on my main page in the blog (the number of comments for each entry is indicated). This will require an n + 1 query, where n is the number of records per page. Not very good ...
SELECT COUNT(*) FROM comments_table WHERE comments_table.entry_id ==
Join tables
And now the most interesting: SQLAlchemy can map classes not only to individual tables, but also to JOINs and even SELECTs. That is, all we need to do is make a SQL query :: Here is what it looks like using the metadata description ( Metadata mapping ) SQLAlchemy :: It's pretty simple. Now we map the classic Entry to this SELECT :: Everything is ready! Now, when we get Entry entries, we will have the comments_count attribute on each instance. And all this in one request. Django.orm and Rails with their ActiveRecord nervously smoke aside;).
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
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")
mapper(Entry, schema.entries_with_comments,
primary_key=[schema.entries_with_comments.c.id],
)