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 Edgy has a similar approach to the problem but faces it in a different and more clear way.

The Edgy 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.

Note:

The syntax is like the django syntax. With __ a foreign key or related name of a foreign key is traversed. Only the last part of the path must be a inversion of a foreign key (related_name).

Note:

this kind of traversal for foreign keys is new. Former versions used the related_name of foreign keys with the effect that a model could only specify a related_name once despite on a different ForeignKey.

Note:

ManyToMany fields create a through model which must be traversed.

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 edgy import Prefetch

Or

from edgy.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¶

This means, imagine you have the following:

import edgy

database = edgy.Database("sqlite:///db.sqlite")
models = edgy.Registry(database=database)


class Album(edgy.Model):
    id = edgy.IntegerField(primary_key=True, autoincrement=True)
    name = edgy.CharField(max_length=100)

    class Meta:
        registry = models


class Track(edgy.Model):
    id = edgy.BigIntegerField(primary_key=True, autoincrement=True)
    album = edgy.ForeignKey("Album", on_delete=edgy.CASCADE, related_name="tracks")
    title = edgy.CharField(max_length=100)
    position = edgy.IntegerField()

    class Meta:
        registry = models


class Studio(edgy.Model):
    album = edgy.ForeignKey("Album", related_name="studios")
    name = edgy.CharField(max_length=255)

    class Meta:
        registry = models


class Company(edgy.Model):
    studio = edgy.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 edgy
from edgy import Prefetch

database = edgy.Database("sqlite:///db.sqlite")
models = edgy.Registry(database=database)


class User(edgy.Model):
    name = edgy.CharField(max_length=100)

    class Meta:
        registry = models


class Post(edgy.Model):
    user = edgy.ForeignKey(User, related_name="posts")
    comment = edgy.CharField(max_length=255)

    class Meta:
        registry = models


class Article(edgy.Model):
    user = edgy.ForeignKey(User, related_name="articles")
    content = edgy.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="studio__album__tracks", to_attr="tracks")
).get(studio=studio)

The Prefetch used the foreign key name and the related_name at the last part to perform the query and did the transversal approach.

The company now has an attribute tracks where it contains all the associated tracks list.

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 Edgy 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 Edgy 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¶

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 edgy

database = edgy.Database("sqlite:///db.sqlite")
models = edgy.Registry(database=database)


class User(edgy.Model):
    name = edgy.CharField(max_length=100)

    class Meta:
        registry = models


class Post(edgy.Model):
    user = edgy.ForeignKey(User, related_name="posts")
    comment = edgy.CharField(max_length=255)

    class Meta:
        registry = models


class Article(edgy.Model):
    user = edgy.ForeignKey(User, related_name="articles")
    content = edgy.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="Edgy")

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 edgy
from edgy import Prefetch

database = edgy.Database("sqlite:///db.sqlite")
models = edgy.Registry(database=database)


class User(edgy.Model):
    name = edgy.CharField(max_length=100)

    class Meta:
        registry = models


class Post(edgy.Model):
    user = edgy.ForeignKey(User, related_name="posts")
    comment = edgy.CharField(max_length=255)

    class Meta:
        registry = models


class Article(edgy.Model):
    user = edgy.ForeignKey(User, related_name="articles")
    content = edgy.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

edgy = [value for value in users if value.pk == edgy.pk][0]
assert len(edgy.to_posts) == 5  # Total posts for Edgy
assert len(edgy.to_articles) == 50  # Total articles for Edgy

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 edgy

database = edgy.Database("sqlite:///db.sqlite")
models = edgy.Registry(database=database)


class Album(edgy.Model):
    id = edgy.IntegerField(primary_key=True, autoincrement=True)
    name = edgy.CharField(max_length=100)

    class Meta:
        registry = models


class Track(edgy.Model):
    id = edgy.BigIntegerField(primary_key=True, autoincrement=True)
    album = edgy.ForeignKey("Album", on_delete=edgy.CASCADE, related_name="tracks")
    title = edgy.CharField(max_length=100)
    position = edgy.IntegerField()

    class Meta:
        registry = models


class Studio(edgy.Model):
    album = edgy.ForeignKey("Album", related_name="studios")
    name = edgy.CharField(max_length=255)

    class Meta:
        registry = models


class Company(edgy.Model):
    studio = edgy.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 edgy
from edgy import Prefetch

database = edgy.Database("sqlite:///db.sqlite")
models = edgy.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="studio__album__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.

Prefetch Related¶

Prefetch¶

Parameters¶

Special attention¶

Auto generated related names¶

What can be used¶

How to use¶

Using Prefetch¶

Using the queryset¶