def test_combined_with_length(self):
Author.objects.create(name="Rhonda", alias="john_smith")
Author.objects.create(name="♥♣♠", alias="bytes")
authors = Author.objects.annotate(filled=LPad("name", Length("alias")))
self.assertQuerysetEqual(
authors.order_by("alias"),
[" ♥♣♠", " Rhonda"],
lambda a: a.filled,
)
def test_combined_with_length(self):
Author.objects.create(name='Rhonda', alias='john_smith')
Author.objects.create(name='♥♣♠', alias='bytes')
authors = Author.objects.annotate(
filled=LPad('name', Length('alias'), output_field=CharField()))
self.assertQuerysetEqual(
authors.order_by('alias'),
[' ♥♣♠', ' Rhonda'],
lambda a: a.filled,
)
def test_pad(self):
Author.objects.create(name='John', alias='j')
tests = (
(LPad('name', 7, Value('xy')), 'xyxJohn'),
(RPad('name', 7, Value('xy')), 'Johnxyx'),
(LPad('name', 6, Value('x')), 'xxJohn'),
(RPad('name', 6, Value('x')), 'Johnxx'),
# The default pad string is a space.
(LPad('name', 6), ' John'),
(RPad('name', 6), 'John '),
# If string is longer than length it is truncated.
(LPad('name', 2), 'Jo'),
(RPad('name', 2), 'Jo'),
(LPad('name', 0), ''),
(RPad('name', 0), ''),
)
for function, padded_name in tests:
with self.subTest(function=function):
authors = Author.objects.annotate(padded_name=function)
self.assertQuerysetEqual(authors, [padded_name], lambda a: a.padded_name, ordered=False)
def get_fyp_notation(relation_name=None):
"""
Generates FYyyyyPpp syntax from submission table. relation_name is the Django ORM
relation name from the foreign key table to the submission table.
"""
prefix = f"{relation_name}__" if relation_name else ""
return Concat(
Value("FY"),
Cast(f"{prefix}reporting_fiscal_year", output_field=CharField()),
Value("P"),
LPad(
Cast(f"{prefix}reporting_fiscal_period", output_field=CharField()),
2, Value("0")),
)
def ANNOTATE_BREW_NUMBER(qs: QuerySet,
annotate_name: str = 'brew_number') -> QuerySet:
"""
Annotates QuerySet `qs` with name `annotate_name` (defaults to "brew_number")
:param qs: BaseBatch QuerySet to annotate
:type qs: QuerySet
:param annotate_name: Name to provide for the annotation. Defaults to `brew_number`
:type annotate_name: str
:return: Annotated QuerySet
:rtype: QuerySet
"""
""" Depreciated by below
annotate_dict = {annotate_name: Cast(Case(
When(__tbatch=1, then=Concat('year', Value('000'), 'batch')),
When(__tbatch=2, then=Concat('year', Value('00'), 'batch')),
When(__tbatch=3, then=Concat('year', Value('0'), 'batch')),
default=Concat('year', 'batch')), output_field=DecimalField(max_digits=6, decimal_places=0)
)}
# Take the 'batch' field on BaseBatch, cast it to a CharField `__tbatch`. The annotate_dict will
# determine how many 0s to insert based on the length of `__tbatch`, and concat that with `year` field
return qs.annotate(__tbatch=Length(Cast('batch', CharField(max_length=4)))).annotate(**annotate_dict)
"""
# The use of `annotate_name` allows the field name to be passed to the queryset. Otherwise, the name would be constant
annotate_dict = {
annotate_name:
Cast(Concat(Right(
Cast(ExtractYear('date'), output_field=CharField(max_length=4)),
2),
LPad(Cast('batch', output_field=CharField(max_length=4)),
4,
Value('0'),
output_field=CharField(max_length=4)),
output_field=DecimalField(max_digits=6, decimal_places=0)),
output_field=DecimalField(max_digits=6, decimal_places=0))
}
return qs.annotate(**annotate_dict)
#!/usr/bin/env python
from django.db.models import Value
from django.db.models.functions import LPad
from .models import MyModel
"""
https://docs.djangoproject.com/en/dev/ref/models/database-functions/#lpad
class LPad(expression, length, fill_text=Value(' '), **extra)
"""
qs = MyModel.objects.annotate(lpad=LPad('name', 8, Value('-')))
for r in qs.all():
print(r.lpad)
def query5():
return core_models.Product.objects.annotate(
code=LPad(Cast('id', output_field=CharField()), 6, Value('0')),
name_upper=Upper('name'),
name_lower=Lower('name')).values('code', 'name_upper', 'name_lower')