Prefetch Related¶
What is this thing of prefetch? Well, imagine you want to get a record from the database and at the same time you also want to get the nested models related to that same model as well.
The prefetch does this job for you, in other words, pre-loads the related models.
Django for example has the prefetch_related
as well and Saffier has a similar approach to the
problem but faces it in a different and more clear way.
The Saffier way of doing it its by also calling the prefetch_related
queryset but passing
Prefetch instances and utilising the related_name to do it so.
Prefetch¶
The main object used for the prefetch_related
query. This particular object contains a set
of instructions that helps mapping the results with the given returned object.
from saffier import Prefetch
Or
from saffier.core.db.querysets import Prefetch
Parameters¶
To make the Prefetch work properly some parameters are needed to make sure it runs smootly:
- related_name - The related_name type of query to perform.
- to_attr - The name of the new attribute in the model being queried where the results will be stored.
- queryset (Optional) - Additional queryset for the type of query being made.
Special attention¶
Using the Prefetch
also means something. You must use the related names
of the ForeignKey declared.
Warning
The Prefetch does not work on ManyToMany fields.
This means, imagine you have the following:
import saffier
database = saffier.Database("sqlite:///db.sqlite")
models = saffier.Registry(database=database)
class Album(saffier.Model):
id = saffier.IntegerField(primary_key=True)
name = saffier.CharField(max_length=100)
class Meta:
registry = models
class Track(saffier.Model):
id = saffier.IntegerField(primary_key=True)
album = saffier.ForeignKey("Album", on_delete=saffier.CASCADE, related_name="tracks")
title = saffier.CharField(max_length=100)
position = saffier.IntegerField()
class Meta:
registry = models
class Studio(saffier.Model):
album = saffier.ForeignKey("Album", related_name="studios")
name = saffier.CharField(max_length=255)
class Meta:
registry = models
class Company(saffier.Model):
studio = saffier.ForeignKey(Studio, related_name="companies")
class Meta:
registry = models
We have now three related names:
- companies - ForeignKey in
Company
model. - studios - ForeignKey in
Studio
model. - tracks - ForeignKey in
Track
model.
Add some data into it
# Create the album
album = await Album.query.create(name="Malibu")
# Create the track
await Track.query.create(album=album, title="The Bird", position=1)
# Create the studio
studio = await Studio.query.create(album=album, name="Valentim")
# Create the company
await Company.query.create(studio=studio)
You know want to query:
- All the tracks that belong to a specific
Company
. The tracks are associated withalbums
andstudios
.
import saffier
from saffier import Prefetch
database = saffier.Database("sqlite:///db.sqlite")
models = saffier.Registry(database=database)
class User(saffier.Model):
name = saffier.CharField(max_length=100)
class Meta:
registry = models
class Post(saffier.Model):
user = saffier.ForeignKey(User, related_name="posts")
comment = saffier.CharField(max_length=255)
class Meta:
registry = models
class Article(saffier.Model):
user = saffier.ForeignKey(User, related_name="articles")
content = saffier.CharField(max_length=255)
class Meta:
registry = models
# All the tracks that belong to a specific `Company`.
# The tracks are associated with `albums` and `studios`
company = await Company.query.prefetch_related(
Prefetch(related_name="companies__studios__tracks", to_attr="tracks")
).get(studio=studio)
Did you notice what happened there? The Prefetch used all the related_names associated with the models to perform the query and did the transversal approach.
The company
now has an attribute tracks
where it contains all the associated tracks
list.
Auto generated related names¶
What if you don't add a related_name
? That is covered in related_names
related with the auto generation of the related name, which means,
if you don't provide a related name, automatically Saffier generates it and that is the one you must use.
What can be used¶
The way you do queries remains exactly the same you do all the time with Saffier as the Prefetch is another process running internally, so that means you can apply any filter you want as you would normal do in a query.
How to use¶
Make sure you do not skip the special attention section as it explains
how the related_name
query works.
Now its where the good stuff starts. How can you take advantage of the Prefetch
object in your
queries.
Let us assume we have three models:
- User
- Post
- Article
Something like this.
import saffier
database = saffier.Database("sqlite:///db.sqlite")
models = saffier.Registry(database=database)
class User(saffier.Model):
name = saffier.CharField(max_length=100)
class Meta:
registry = models
class Post(saffier.Model):
user = saffier.ForeignKey(User, related_name="posts")
comment = saffier.CharField(max_length=255)
class Meta:
registry = models
class Article(saffier.Model):
user = saffier.ForeignKey(User, related_name="articles")
content = saffier.CharField(max_length=255)
class Meta:
registry = models
Note
For example purposes, the connection string will be to SQLite and the models quite simple but enough for this.
We now want to create some posts and some articles and associate to the user
. Something like this:
user = await User.query.create(name="Saffier")
for i in range(5):
await Post.query.create(comment="Comment number %s" % i, user=user)
for i in range(50):
await Article.query.create(content="Comment number %s" % i, user=user)
esmerald = await User.query.create(name="Esmerald")
for i in range(15):
await Post.query.create(comment="Comment number %s" % i, user=esmerald)
for i in range(20):
await Article.query.create(content="Comment number %s" % i, user=esmerald)
Note
We simply want to generate posts and articles just to have data for the queries.
Using Prefetch¶
With all the data generated for the models, we now want to query:
- All the users in the system.
- All the posts associated to each user.
- All the articles associated to each user.
import saffier
from saffier import Prefetch
database = saffier.Database("sqlite:///db.sqlite")
models = saffier.Registry(database=database)
class User(saffier.Model):
name = saffier.CharField(max_length=100)
class Meta:
registry = models
class Post(saffier.Model):
user = saffier.ForeignKey(User, related_name="posts")
comment = saffier.CharField(max_length=255)
class Meta:
registry = models
class Article(saffier.Model):
user = saffier.ForeignKey(User, related_name="articles")
content = saffier.CharField(max_length=255)
class Meta:
registry = models
# All the users with all the posts and articles
# of each user
users = await User.query.prefetch_related(
Prefetch(related_name="posts", to_attr="to_posts"),
Prefetch(related_name="articles", to_attr="to_articles"),
).all()
You can confirm all the data by simply asserting it.
assert len(users) == 2 # Total ussers
saffier = [value for value in users if value.pk == saffier.pk][0]
assert len(saffier.to_posts) == 5 # Total posts for Saffier
assert len(saffier.to_articles) == 50 # Total articles for Saffier
esmerald = [value for value in users if value.pk == esmerald.pk][0]
assert len(esmerald.to_posts) == 15 # Total posts for Esmerald
assert len(esmerald.to_articles) == 20 # Total articles for Esmerald
Using the queryset¶
What if you want to use the queryset
parameter of the Prefetch. Let us use the same
example of before.
import saffier
database = saffier.Database("sqlite:///db.sqlite")
models = saffier.Registry(database=database)
class Album(saffier.Model):
id = saffier.IntegerField(primary_key=True)
name = saffier.CharField(max_length=100)
class Meta:
registry = models
class Track(saffier.Model):
id = saffier.IntegerField(primary_key=True)
album = saffier.ForeignKey("Album", on_delete=saffier.CASCADE, related_name="tracks")
title = saffier.CharField(max_length=100)
position = saffier.IntegerField()
class Meta:
registry = models
class Studio(saffier.Model):
album = saffier.ForeignKey("Album", related_name="studios")
name = saffier.CharField(max_length=255)
class Meta:
registry = models
class Company(saffier.Model):
studio = saffier.ForeignKey(Studio, related_name="companies")
class Meta:
registry = models
Add some data into it
# Create the album
album = await Album.query.create(name="Malibu")
# Create the track
await Track.query.create(album=album, title="The Bird", position=1)
# Create the studio
studio = await Studio.query.create(album=album, name="Valentim")
# Create the company
await Company.query.create(studio=studio)
You know want to queryusing the queryset:
- All the tracks that belong to a specific
Company
. The tracks are associated withalbums
andstudios
but theTrack
will be also internally filtered.
import saffier
from saffier import Prefetch
database = saffier.Database("sqlite:///db.sqlite")
models = saffier.Registry(database=database)
# All the tracks that belong to a specific `Company`.
# The tracks are associated with `albums` and `studios`
# where the `Track` will be also internally filtered
company = await Company.query.prefetch_related(
Prefetch(
related_name="companies__studios__tracks",
to_attr="tracks",
queryset=Track.query.filter(title__icontains="bird"),
)
)
This easy, right? The total tracks should be 1 as the bird is part of the title of the
track
that belongs to the studio
that belongs to the company
.