Capital city: Sofia
Population: ~7 000 000
Ivo Donchev
HackSoft Academy
#3 types of problems
&
class SharedAccount(Model):
pass
class User(Model):
shared_account = ForeignKey(
SharedAccount,
related_name='users'
)
SELECT *
FROM "user"
ORM expression
User.objects.all()
SQL query
SELECT *
FROM "user"
INNER JOIN "shared_account"
ON ("user"."shared_account_id" = "shared_account"."id")
ORM expression
User.objects.select_related('shared_account')
SQL query
class SharedAccount(Model):
pass
class User(Model):
shared_account = ForeignKey(
SharedAccount,
related_name='users'
)
SELECT *
FROM "shared_account"
ORM expression
SharedAccount.objects.all()
SQL query
SELECT *
FROM "shared_account"
ORM expression
SharedAccount.objects.prefetch_related('users')
SQL query
SELECT *
FROM "users"
WHERE "user"."shared_account_id" IN (...);
wait for it....
you can use
.select_related() and .prefetch_related()
for simple JOIN-s
class User(Model):
name = CharField(max_length=255)
class Playlist(Model):
user = ForeignKey(User, related_name='playlists')
name = CharField(max_length=255)
class Song(Model):
playlist = ForeignKey(Playlist, related_name='songs')
title = CharField(max_length=255)
length = PositiveIntegerField()
class User(Model):
name = CharField(max_length=255)
class Playlist(Model):
user = ForeignKey(User, related_name='playlists')
name = CharField(max_length=255)
@property
def songs_total_length(self):
total_length = 0
for song in self.songs.all():
total_length += song.length
return total_length
class Song(Model):
playlist = ForeignKey(Playlist, related_name='songs')
title = CharField(max_length=255)
length = PositiveIntegerField()
class User(Model):
name = CharField(max_length=255)
class Playlist(Model):
user = ForeignKey(User, related_name='playlists')
name = CharField(max_length=255)
@property
def songs_total_length(self):
return sum([song.length for song in self.songs.all()])
class Song(Model):
playlist = ForeignKey(Playlist, related_name='songs')
title = CharField(max_length=255)
length = PositiveIntegerField()
class User(Model):
name = CharField(max_length=255)
@property
def playlists_total_length(self):
return sum([p.songs_total_length for p in self.playlists.all()])
class Playlist(Model):
user = ForeignKey(User, related_name='playlists')
name = CharField(max_length=255)
@property
def songs_total_length(self):
return sum([song.length for song in self.songs.all()])
class Song(Model):
playlist = ForeignKey(Playlist, related_name='songs')
title = CharField(max_length=255)
length = PositiveIntegerField()
Total objects to fetch = 10 (Users) +\
                     10 * 10 (Playlists) +\
                     10 * 10 * 10 (Songs)
1110 DB rows in 3 different tables
for just 10 users
class User(Model):
name = CharField(max_length=255)
@property
def playlists_total_length(self):
return sum([p.songs_total_length for p in self.playlists.all()])
class Playlist(Model):
user = ForeignKey(User, related_name='playlists')
name = CharField(max_length=255)
@property
def songs_total_length(self):
return sum([song.length for song in self.songs.all()])
class Song(Model):
playlist = ForeignKey(Playlist, related_name='songs')
title = CharField(max_length=255)
length = PositiveIntegerField()
SELECT "playlist"."id",
"playlist"."user_id",
"playlist"."name",
(
SELECT SUM("song"."length")
FROM "song"
WHERE "song"."playlist_id" = ("playlist"."id")
GROUP BY "song"."playlist_id" LIMIT 1
) AS "songs_total_length"
FROM "playlist";
class Playlist(Model):
user = ForeignKey(User, related_name='playlists')
name = CharField(max_length=255)
@property
def songs_total_length(self):
return sum([song.length for song in self.songs.all()])
class PlaylistQuerySet(QuerySet):
@classmethod
def songs_total_length(cls):
return <sum aggregation over songs grouped by the playlist ???>
def collect(self):
return self.annotate(_songs_total_length=self.songs_total_length())
class Playlist(Model):
objects = PlaylistQuerySet.as_manager()
user = ForeignKey(User, related_name='playlists')
name = CharField(max_length=255)
@property
def songs_total_length(self):
if hasattr(self, 'songs_total_length'):
return self._songs_total_length
return sum([song.length for song in self.songs.all()])
class PlaylistQuerySet(QuerySet):
@classmethod
def songs_total_length(cls):
return Subquery(
query=...,
output_field=...
)
def collect(self):
return self.annotate(_songs_total_length=self.songs_total_length())
class Playlist(Model):
objects = PlaylistQuerySet.as_manager()
user = ForeignKey(User, related_name='playlists')
name = CharField(max_length=255)
@property
def songs_total_length(self):
if hasattr(self, 'songs_total_length'):
return self._songs_total_length
return sum([song.length for song in self.songs.all()])
class PlaylistQuerySet(QuerySet):
@classmethod
def songs_total_length(cls):
return Subquery(
queryset=...,
output_field=models.IntegerField()
)
def collect(self):
return self.annotate(_songs_total_length=self.songs_total_length())
class Playlist(Model):
objects = PlaylistQuerySet.as_manager()
user = ForeignKey(User, related_name='playlists')
name = CharField(max_length=255)
@property
def songs_total_length(self):
if hasattr(self, 'songs_total_length'):
return self._songs_total_length
return sum([song.length for song in self.songs.all()])
class PlaylistQuerySet(QuerySet):
@classmethod
def songs_total_length(cls):
queryset = Song.objects \
.values('playlist__id') \ # This is the GROUP BY field
. # ¯\_(ツ)_/¯
return Subquery(
queryset=queryset,
output_field=models.IntegerField()
)
def collect(self):
return self.annotate(_songs_total_length=self.songs_total_length())
class Playlist(Model):
objects = PlaylistQuerySet.as_manager()
user = ForeignKey(User, related_name='playlists')
name = CharField(max_length=255)
@property
def songs_total_length(self):
if hasattr(self, 'songs_total_length'):
return self._songs_total_length
return sum([song.length for song in self.songs.all()])
class PlaylistQuerySet(QuerySet):
@classmethod
def songs_total_length(cls):
queryset = Song.objects \
.values('playlist__id') \ # This is the GROUP BY field
.filter(playlist__id=OuterRef('id')) \
. # ¯\_(ツ)_/¯
return Subquery(
queryset=queryset,
output_field=models.IntegerField()
)
def collect(self):
return self.annotate(_songs_total_length=self.songs_total_length())
class Playlist(Model):
objects = PlaylistQuerySet.as_manager()
user = ForeignKey(User, related_name='playlists')
name = CharField(max_length=255)
@property
def songs_total_length(self):
if hasattr(self, 'songs_total_length'):
return self._songs_total_length
return sum([song.length for song in self.songs.all()])
class PlaylistQuerySet(QuerySet):
@classmethod
def songs_total_length(cls):
queryset = Song.objects \
.values('playlist__id') \ # This is the GROUP BY field
.filter(playlist__id=OuterRef('id')) \
.values_list(Sum('length')) # The SUM aggregation
return Subquery(
queryset=queryset,
output_field=models.IntegerField()
)
def collect(self):
return self.annotate(_songs_total_length=self.songs_total_length())
class Playlist(Model):
objects = PlaylistQuerySet.as_manager()
user = ForeignKey(User, related_name='playlists')
name = CharField(max_length=255)
@property
def songs_total_length(self):
if hasattr(self, 'songs_total_length'):
return self._songs_total_length
return sum([song.length for song in self.songs.all()])
Playlist.objects.collect()
SELECT "playlist"."id",
"playlist"."user_id",
"playlist"."name",
(
SELECT SUM("song"."length")
FROM "song"
WHERE "song"."playlist_id" = ("playlist"."id")
GROUP BY "song"."playlist_id" LIMIT 1
) AS "songs_total_length"
FROM "playlist";
...is the Django ORM equivalent for
class PlaylistQuerySet(QuerySet):
@classmethod
def playlists_total_length(cls):
pass
def collect(self):
return self.annotate(_playlists_total_length=self.playlists_total_length())
class User(Model):
objects = UsersQuerySet.as_manager()
name = CharField(max_length=255)
@property
def playlists_total_length(self):
if hasattr(self, '_playlists_total_length'):
return self._playlists_total_length
playlists_length = [
playlist.songs_total_length
for playlist in self.playlists.all()
]
return sum(playlist_length)
class UserQuerySet(QuerySet):
@classmethod
def playlists_total_length(cls):
playlist_annotation = PlaylistQueryset.songs_total_length()
queryset = Playlist.objects \
.values('user__id') \
.filter(user__id=OuterRef('id')) \
.values_list(Sum(playlist_annotation))
return Subquery(
queryset=queryset,
output_field=IntegerField()
)
def collect(self):
return self.annotate(_playlists_total_length=self.playlists_total_length())
class User(Model):
objects = UserQuerySet.as_manager()
name = CharField(max_length=255)
@property
def playlists_total_length(self):
if hasattr(self, '_playlists_total_length'):
return self._playlists_total_length
return sum([p.songs_total_length for p in self.playlists.all()])
SELECT "user"."id",
"user"."name",
(SELECT SUM((
SELECT SUM(U0."length")
FROM "song" U0
WHERE U0."playlist_id" = (V0."id")
GROUP BY U0."playlist_id"
LIMIT 1
))
FROM "playlist" V0
WHERE V0."user_id" = ("user"."id")
GROUP BY V0."user_id") AS "_playlists_total_length"
FROM "user"
Subquery + OuterRef for GROUP BY
class User(Model):
name = CharField(max_length=255)
@property
def playlists_total_length(self):
return sum([p.songs_total_length for p in self.playlists.all()])
class Playlist(Model):
user = ForeignKey(User, related_name='playlists')
name = CharField(max_length=255)
@property
def songs_total_length(self):
return sum([song.real_length for song in self.songs.all()])
class Song(Model):
playlist = ForeignKey(Playlist, related_name='songs')
title = CharField(max_length=255)
length = PositiveIntegerField()
def real_length(self):
return self.length * 0.8
class SongQuerySet(QuerySet):
@classmethod
def real_length(cls):
return ExpressionWrapper(
expression=...,
output_field=IntegerField()
)
def collect(self):
return self.annotate(_real_length=self.real_length())
class Song(Model):
playlist = ForeignKey(Playlist, related_name='songs')
title = CharField(max_length=255)
length = PositiveIntegerField()
def real_length(self):
if hasattr(self, '_real_length'):
return self._real_length
return self.length * 0.8
class SongQuerySet(QuerySet):
@classmethod
def real_length(cls):
real_length = F('length') * Value(0.8)
return ExpressionWrapper(
expression=real_length,
output_field=IntegerField()
)
def collect(self):
return self.annotate(_real_length=self.real_length())
class Song(Model):
playlist = ForeignKey(Playlist, related_name='songs')
title = CharField(max_length=255)
length = PositiveIntegerField()
def real_length(self):
if hasattr(self, '_real_length'):
return self._real_length
return self.length * 0.8
# test_models.py
class SongTests(TestCase):
def test_song_length(self):
length = 120
real_length = 0.8 * length
song = Song.objects.create(title='Europython', length=120)
self.assertEqual(real_length, song.real_length)
# test_models.py
class SongTests(TestCase):
def test_song_length(self):
length = 120
real_length = 0.8 * length
song = Song.objects.create(title='Europython', length=120)
self.assertEqual(real_length, song.real_length)
song_collected = Song.objects.collect().get(id=song.id)
self.assertEqual(real_length, song_collected.real_length)