Relationships¶

Creating relationships in Edgy is as simple as importing the fields and apply them into the models.

There are currently two types, the ForeignKey and the OneToOne.

When declaring a foreign key, you can pass the value in two ways, as a string or as a model object. Internally Edgy lookups up inside the registry and maps your fields.

When declaring a model you can have one or more ForeignKey pointing to different tables or multiple foreign keys pointing to the same table as well.

Tip

Have a look at the related name documentation to understand how you can leverage reverse queries with foreign keys.

ForeignKey¶

Let us define the following 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 us create some entries for those 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¶

What if you want to have multiple foreign keys pointing to the same model? This is also easily possible to achieve.

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

Have a look at the related name documentation to understand how you can leverage reverse queries with foreign keys withe the related_name.

Load an instance without the foreign key relationship on it¶

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

# We have an album 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 recursive¶

Especcially in connection with model_dump it is helpful to populate all foreign keys. You can use load_recursive for that.

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

# We have an album instance and all foreign key relations populated
print(profile.user)       # User(id=1) [sparse]
print(profile.user.pk)    # 1
print(profile.user.email)  # ok

Load an instance with the foreign key relationship on it¶

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

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

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

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

Access the foreign key values directly from the model¶

Note

This is only possible since the version 0.9.0 of Edgy, before this version, the only way was by using the select_related or using the load().

You can access the values of the foreign keys of your model directly via model instance without using the select_related or the load().

Let us 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, you can specify constraints in a foreign key.

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

from edgy import CASCADE, SET_NULL, RESTRICT

When declaring a foreign key or a one to one key, the on_delete must be provided or an AssertationError is raised.

Looking back to 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

Profile model defines a edgy.ForeignKey to the User with on_delete=edgy.CASCADE which means that whenever a User is deleted from the database, all associated Profile instances will also be removed.

Delete options¶

CASCADE - Remove all referencing objects.
RESTRICT - Restricts the removing referenced objects.
SET_NULL - This will make sure that when an object is deleted, the associated referencing instances pointing to that object will set to null. When this SET_NULL is true, the null=True must be also provided or an AssertationError is raised.

OneToOne¶

Creating an OneToOneField relationship between models is basically the same as the ForeignKey with the key difference 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 for this field are the same as the ForeignKey as this derives from it.

Let us create a User and a Profile.

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

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

await Profile.query.create(user=user)

Limitations¶

We cannot cross the database with a query, yet. This means you can not 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