def get_queryset(self):
return (super().get_queryset().filter(
url__startswith='/privacy-policy-').annotate(
version=dbf.Substr( # Poor man's regex ^/privacy-policy-(.+)/$
dbf.Substr('url',
1,
dbf.Length('url') - 1,
output_field=models.CharField()),
len('/privacy-policy-') + 1)))
def main():
from playlister import models
from django.db import connection
from django.db.models import F, Count, Q, FilteredRelation
import django.db.models.functions as DbF
from django.db.models.manager import BaseManager
playlists: BaseManager = models.Playlist.objects
songs: BaseManager = models.Song.objects
# slicing returns LimiterQuerySet, pure indexing does not
first_playlist = playlists.all()[:1]
# where name like '%Seeded%'
sql(playlists.filter(name__contains='Seeded').all())
# where any entry has a song of Amon Tobin
sql(playlists.filter(entries__song__artist__exact='Amon Tobin').all())
# where song artist is same as title
sql(songs.filter(artist__exact=F('title')))
# where artist start with title first char
sql(songs.filter(artist__startswith=DbF.Substr(F('title'), 1, 1)))
# where artist first char same as title first char
sql(
songs.annotate(afc=DbF.Substr(F('artist'), 1, 1)).filter(
afc__exact=DbF.Substr(F('title'), 1, 1)))
# group by first letter
sql(
songs.values(first_char=DbF.Substr(F('artist'), 1, 1)).annotate(
nm_songs=Count(F('id'))))
# id in array
sql(songs.filter(id__in=[1, 2, 3]))
# union
sql(
playlists.values('name', 'name').union(songs.values('title',
'artist')))
try_to_join()
# print all queries executed on database connection
[print(i['sql'], '\n') for i in connection.queries]
return
def __getitem__(self, slc: slice) -> models.Func:
"""Return field ``Substr`` or ``Right``."""
assert (slc.stop or 0) >= 0 and slc.step is None
start = slc.start or 0
if start < 0:
assert slc.stop is None
return functions.Right(self, -start)
size = slc.stop and max(slc.stop - start, 0)
return functions.Substr(self, start + 1, size)