Relationships
ForeignKey
Defining and querying relationships
ORM supports loading and filtering across foreign keys.
Let's say you have the following models defined:
import databases
import orm
database = databases.Database("sqlite:///db.sqlite")
models = orm.ModelRegistry(database=database)
class Album(orm.Model):
tablename = "albums"
registry = models
fields = {
"id": orm.Integer(primary_key=True),
"name": orm.String(max_length=100),
}
class Track(orm.Model):
tablename = "tracks"
registry = models
fields = {
"id": orm.Integer(primary_key=True),
"album": orm.ForeignKey(Album),
"title": orm.String(max_length=100),
"position": orm.Integer(),
}
You can create some Album
and Track
instances:
malibu = await Album.objects.create(name="Malibu")
await Track.objects.create(album=malibu, title="The Bird", position=1)
await Track.objects.create(album=malibu, title="Heart don't stand a chance", position=2)
await Track.objects.create(album=malibu, title="The Waters", position=3)
fantasies = await Album.objects.create(name="Fantasies")
await Track.objects.create(album=fantasies, title="Help I'm Alive", position=1)
await Track.objects.create(album=fantasies, title="Sick Muse", position=2)
To fetch an instance, without loading a foreign key relationship on it:
track = await Track.objects.get(title="The Bird")
# We have an album instance, but it only has the primary key populated
print(track.album) # Album(id=1) [sparse]
print(track.album.pk) # 1
print(track.album.name) # Raises AttributeError
You can load the relationship from the database:
await track.album.load()
assert track.album.name == "Malibu"
You can also fetch an instance, loading the foreign key relationship with it:
track = await Track.objects.select_related("album").get(title="The Bird")
assert track.album.name == "Malibu"
To fetch an instance, filtering across a foregin key relationship:
tracks = Track.objects.filter(album__name="Fantasies")
assert len(tracks) == 2
tracks = Track.objects.filter(album__name__iexact="fantasies")
assert len(tracks) == 2
ForeignKey constraints
ForeigknKey
supports specfiying a constraint through on_delete
argument.
This will result in a SQL ON DELETE
query being generated when the referenced object is removed.
With the following definition:
class Album(orm.Model):
tablename = "albums"
registry = models
fields = {
"id": orm.Integer(primary_key=True),
"name": orm.String(max_length=100),
}
class Track(orm.Model):
tablename = "tracks"
registry = models
fields = {
"id": orm.Integer(primary_key=True),
"album": orm.ForeignKey(Album, on_delete=orm.CASCADE),
"title": orm.String(max_length=100),
}
Track
model defines orm.ForeignKey(Album, on_delete=orm.CASCADE)
so whenever an Album
object is removed,
all Track
objects referencing that Album
will also be removed.
Available options for on_delete
are:
CASCADE
This will remove all referencing objects.
RESTRICT
This will restrict removing referenced object, if there are objects referencing it. A database driver exception will be raised.
SET NULL
This will set referencing objects ForeignKey
column to NULL
.
The ForeignKey
defined here should also have allow_null=True
.
OneToOne
Creating a OneToOne
relationship between models, this is basically
the same as ForeignKey
but it uses unique=True
on the ForeignKey column:
class Profile(orm.Model):
registry = models
fields = {
"id": orm.Integer(primary_key=True),
"website": orm.String(max_length=100),
}
class Person(orm.Model):
registry = models
fields = {
"id": orm.Integer(primary_key=True),
"email": orm.String(max_length=100),
"profile": orm.OneToOne(Profile),
}
You can create a Profile
and Person
instance:
profile = await Profile.objects.create(website="https://encode.io")
await Person.objects.create(email="info@encode.io", profile=profile)
Now creating another Person
using the same profile
will fail
and will raise an exception:
await Person.objects.create(email="info@encode.io", profile=profile)
OneToOne
accepts the same on_delete
parameters as ForeignKey
which is
described here.