Relationships

Establishing relationships between models in Edgy is straightforward, involving importing the necessary fields and applying them to your models.

Edgy currently supports two relationship types: ForeignKey and OneToOne.

When defining a foreign key, you can specify the related model either as a string or as a model object. Edgy internally resolves the relationship using the registry.

A model can have one or more foreign keys pointing to different tables or multiple foreign keys referencing the same table.

Tip

Refer to the related name documentation to learn how to leverage reverse queries with foreign keys.

ForeignKey

Let's define two models, User and Profile.

import edgy
from edgy import Database, Registry

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


class User(edgy.Model):
    is_active: bool = edgy.BooleanField(default=True)
    first_name: str = edgy.CharField(max_length=50, null=True)
    last_name: str = edgy.CharField(max_length=50, null=True)
    email: str = edgy.EmailField(max_lengh=100)
    password: str = edgy.CharField(max_length=1000, null=True)

    class Meta:
        registry = models


class Profile(edgy.Model):
    user: User = edgy.ForeignKey(User, on_delete=edgy.CASCADE)

    class Meta:
        registry = models

Now, let's create some entries for these models.

user = await User.query.create(first_name="Foo", email="foo@bar.com")
await Profile.query.create(user=user)

user = await User.query.create(first_name="Bar", email="bar@foo.com")
await Profile.query.create(user=user)

Multiple Foreign Keys Pointing to the Same Table

You can have multiple foreign keys referencing the same model.

import edgy
from edgy import Database, Registry

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


class User(edgy.Model):
    is_active: bool = edgy.BooleanField(default=True)
    first_name: str = edgy.CharField(max_length=50)
    last_name: str = edgy.CharField(max_length=50)
    email: str = edgy.EmailField(max_lengh=100)
    password: str = edgy.CharField(max_length=1000)

    class Meta:
        registry = models


class Thread(edgy.Model):
    sender: User = edgy.ForeignKey(
        User,
        on_delete=edgy.CASCADE,
        related_name="sender",
    )
    receiver: User = edgy.ForeignKey(
        User,
        on_delete=edgy.CASCADE,
        related_name="receiver",
    )
    message: str = edgy.TextField()

    class Meta:
        registry = models

Tip

Refer to the related name documentation to understand how to leverage reverse queries with foreign keys using the related_name attribute.

Load an Instance Without the Foreign Key Relationship Populated

profile = await Profile.query.get(id=1)

# We have a profile instance, but it only has the primary key populated
print(profile.user)       # User(id=1) [sparse]
print(profile.user.pk)    # 1
print(profile.user.email)  # Raises AttributeError

Load Recursively

Especially when using model_dump, it's helpful to populate all foreign keys. You can use load_recursive for this.

profile = await Profile.query.get(id=1)
await profile.load_recursive()

# We have a profile instance and all foreign key relations populated
print(profile.user)       # User(id=1)
print(profile.user.pk)    # 1
print(profile.user.email)  # foo@bar.com

Load an Instance with the Foreign Key Relationship Populated

profile = await Profile.query.get(user__id=1)

await profile.user.load() # loads the foreign key

Load an Instance with the Foreign Key Relationship Populated Using select_related

profile = await Profile.query.select_related("user").get(id=1)

print(profile.user)       # User(id=1)
print(profile.user.pk)    # 1
print(profile.user.email)  # foo@bar.com

Access Foreign Key Values Directly from the Model

Note

This is possible since Edgy version 0.9.0. Before this version, you had to use select_related or load().

You can access foreign key values directly from the model instance without using select_related or load().

Let's see an example.

Create a user and a profile

user = await User.query.create(first_name="Foo", email="foo@bar.com")
await Profile.query.create(user=user)

Accessing the user data from the profile

profile = await Profile.query.get(user__email="foo@bar.com")

print(profile.user.email) # "foo@bar.com"
print(profile.user.first_name) # "Foo"

ForeignKey Constraints

As mentioned in the foreign key field documentation, you can specify constraints for foreign keys.

The available values are CASCADE, SET_NULL, and RESTRICT, which can be imported from edgy.

from edgy import CASCADE, SET_NULL, RESTRICT

When defining a foreign key or one-to-one key, the on_delete parameter is mandatory.

Looking back at the previous example:

import edgy
from edgy import Database, Registry

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


class User(edgy.Model):
    is_active: bool = edgy.BooleanField(default=True)
    first_name: str = edgy.CharField(max_length=50, null=True)
    last_name: str = edgy.CharField(max_length=50, null=True)
    email: str = edgy.EmailField(max_lengh=100)
    password: str = edgy.CharField(max_length=1000, null=True)

    class Meta:
        registry = models


class Profile(edgy.Model):
    user: User = edgy.ForeignKey(User, on_delete=edgy.CASCADE)

    class Meta:
        registry = models

The Profile model defines an edgy.ForeignKey to User with on_delete=edgy.CASCADE. This means that whenever a User is deleted, all associated Profile instances will also be removed.

Delete Options

• CASCADE: Remove all referencing objects.
• RESTRICT: Restricts the removal of referenced objects.
• SET_NULL: Sets the referencing instance's foreign key to null when the referenced object is deleted. When using SET_NULL, null=True must also be provided.

OneToOne

Creating a OneToOneField relationship between models is similar to ForeignKey, with the key difference being that it uses unique=True on the foreign key column.

import edgy
from edgy import Database, Registry

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


class User(edgy.Model):
    is_active: bool = edgy.BooleanField(default=True)
    first_name: str = edgy.CharField(max_length=50)
    last_name: str = edgy.CharField(max_length=50)
    email: str = edgy.EmailField(max_lengh=100)
    password: str = edgy.CharField(max_length=1000)

    class Meta:
        registry = models


class Profile(edgy.Model):
    user: User = edgy.OneToOneField(User, on_delete=edgy.CASCADE)

    class Meta:
        registry = models

The same rules apply to this field as to ForeignKey, as it derives from it.

Let's create a User and a Profile.

user = await User.query.create(email="foo@bar.com")
await Profile.query.create(user=user)

Creating another Profile with the same user will fail and raise an exception.

await Profile.query.create(user=user)

Limitations

Edgy currently does not support cross-database queries.

This means you cannot join a MySQL table with a PostgreSQL table.

How can this be implemented?

Of course joins are not possible. The idea is to execute a query on the child database and then check which foreign key values match.

Of course the ForeignKey has no constraint and if the data vanish it points to nowhere.