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 with albums and studios.

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:

1. User
2. Post
3. 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 with albums and studios but the Track 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.