def for_user(self, user):
if user.is_anonymous():
return self.annotate(public_groupings=Count(
'groupings', only=Q(groupings__isprivate=False))).filter(
Q(status=STATUS.active) &
Q(isprivate=False, public_groupings__gte=1)
).distinct()
else:
projects = self.annotate(public_groupings=Count(
'groupings', only=Q(groupings__isprivate=False))).filter(
Q(admins=user) |
(
Q(status=STATUS.active) &
(
Q(isprivate=False, public_groupings__gte=1) |
Q(usergroups__can_contribute=True,
usergroups__users=user) |
Q(usergroups__can_moderate=True,
usergroups__users=user) |
Q(usergroups__users=user,
usergroups__viewgroups__isnull=False)
)
)
).distinct()
return projects
def _counts(self):
_qs = self.queryset.values('campaign_stage').annotate(
Count('campaign_stage'),
unread_count=Count(
'campaign_stage',
only=(Q(mailbox__has_been_read_by_brand=False))))
_counts = defaultdict(int)
_unread_counts = defaultdict(int)
for stage_data in _qs:
key = min(IJM.SANDBOX_STAGES) if\
stage_data['campaign_stage'] in\
IJM.SANDBOX_STAGES else\
stage_data['campaign_stage']
_counts[key] += stage_data['campaign_stage__count']
_unread_counts[key] += stage_data['unread_count']
_counts.update({
IJM.CAMPAIGN_STAGE_ALL:
sum(_counts.values()),
# IJM.CAMPAIGN_STAGE_APPROVAL: self._context.get(
# 'view').pre_outreach_bloggers_count,
IJM.CAMPAIGN_STAGE_APPROVAL:
self.child_switchers.get(
'approve_status_switcher').wrapper.counts.get(
IA.APPROVE_STATUS_PENDING, 0),
IJM.CAMPAIGN_STAGE_LOAD_INFLUENCERS:
-1,
})
_unread_counts.update({
IJM.CAMPAIGN_STAGE_ALL:
sum(_unread_counts.values()),
})
return {
'counts': _counts,
'unread_counts': _unread_counts,
}
def relatorio_visitas(cls, shopping_id, date=None):
if not date:
return Loja.objects.annotate(vistas=Count(
'pk', only=Q(ofertas__logs__acao=1,
shopping=shopping_id))).order_by('-vistas')
else:
return Loja.objects.annotate(vistas=Count('pk',
only=Q(ofertas__logs__acao=1,
shopping=shopping_id,
ofertas__logs__data_criacao__gte=date)))\
.order_by('-vistas')
def relatorio_solicitacoes(cls, shopping_id, date=None):
if not date:
return Loja.objects.annotate(
pedidos=Count('solicitacoes', only=Q(
shopping=shopping_id))).order_by('-pedidos')
else:
return Loja.objects.annotate(
pedidos=Count('solicitacoes',
only=Q(shopping=shopping_id,
solicitacoes__data_criacao__gte=date)
)).order_by('-pedidos')
def for_user(self, user):
"""
Returns the projects for the user.
For anonymous users:
Returns all projects that are public and have at least one public
data grouping.
For authenticated users:
Returns all projects that
- are public and have at least one public data grouping
- all projects the user can contribute or moderate or can
access one data grouping
Parameter
---------
user : geokey.users.models.User
User projects are queried for
Return
------
django.db.models.query.QuerySet
List of geokey.projects.models.Project
"""
if user.is_anonymous():
return self.annotate(public_groupings=Count(
'groupings',
only=Q(groupings__isprivate=False, groupings__status='active')
)).filter(
Q(status=STATUS.active) &
Q(isprivate=False, public_groupings__gte=1)
).distinct()
else:
projects = self.annotate(public_groupings=Count(
'groupings',
only=Q(groupings__isprivate=False, groupings__status='active')
)).filter(
Q(admins=user) |
(
Q(status=STATUS.active) &
(
Q(isprivate=False, public_groupings__gte=1) |
Q(usergroups__can_contribute=True,
usergroups__users=user) |
Q(usergroups__can_moderate=True,
usergroups__users=user) |
Q(usergroups__users=user,
usergroups__viewgroups__isnull=False)
)
)
).distinct()
return projects
def relatorio_filtrado(cls, shopping_id, acao, tipo, inicio, fim):
if tipo == cls.EVENTO:
return cls.objects.annotate(vistas=Count('pk', only=Q(shopping=shopping_id,
logs__acao=acao,
logs__data_criacao__gte=inicio,
logs__data_criacao__lte=fim + timedelta(days=1),
tipo=tipo))).order_by('-vistas')
else:
return cls.objects.annotate(vistas=Count('pk', only=Q(loja__shopping=shopping_id,
logs__acao=acao,
logs__data_criacao__gte=inicio,
logs__data_criacao__lte=fim + timedelta(days=1),
tipo=tipo))).order_by('-vistas')
def relatorios(request, shopping_id):
lojas_mais_vistas_query = Loja.objects.annotate(vistas=Count('pk',
only=Q(ofertas__logs__acao=1,shopping=shopping_id)))\
.order_by('-vistas')[:10]
lojas_mais_vistas = [{'nome': l.nome, 'numero': l.vistas} for l in lojas_mais_vistas_query if l.vistas]
lojas_mais_pedidas_query = Loja.objects.annotate(pedidos=Count('pk', only=Q(shopping=shopping_id)))\
.order_by('-pedidos','-data_criacao')[:10]
lojas_mais_pedidas = [{'nome': l.nome, 'numero': l.pedidos} for l in lojas_mais_pedidas_query if l.pedidos]
contexto = {'nome_shopping': Shopping.objects.get(id=shopping_id).nome,
'shopping_id': shopping_id,
'lojas_mais_vistas': lojas_mais_vistas,
'lojas_mais_pedidas': lojas_mais_pedidas}
return render(request, "relatorios/shopping.html", contexto)
def get_aggregates(tipologia=None, **kwargs):
if not tipologia:
aggregation_struct = {
'sum': Sum('importo'),
'count': Count('importo')
}
else:
tipologia_id, tipologia_shortname = tipologia
aggregation_struct = {
'sum': Sum('importo', only=Q(tipologia_cedente=tipologia_id)),
'count': Count('importo', only=Q(tipologia_cedente=tipologia_id))
}
return Donazione.objects.filter(**kwargs).aggregate(**aggregation_struct)
def test_annotate_values_list(self):
books = Book.objects.filter(pk=1).annotate(
mean_age=Avg("authors__age")).values_list("pk", "isbn", "mean_age")
self.assertEqual(list(books), [
(1, "159059725", 34.5),
])
books = Book.objects.filter(pk=1).annotate(
mean_age=Avg("authors__age")).values_list("isbn")
self.assertEqual(list(books), [('159059725', )])
books = Book.objects.filter(pk=1).annotate(
mean_age=Avg("authors__age")).values_list("mean_age")
self.assertEqual(list(books), [(34.5, )])
books = Book.objects.filter(pk=1).annotate(
mean_age=Avg("authors__age")).values_list("mean_age", flat=True)
self.assertEqual(list(books), [34.5])
books = Book.objects.values_list("price").annotate(
count=Count("price")).order_by("-count", "price")
self.assertEqual(list(books), [
(Decimal("29.69"), 2),
(Decimal('23.09'), 1),
(Decimal('30'), 1),
(Decimal('75'), 1),
(Decimal('82.8'), 1),
])
def approval_report_selection_counts(self, request, id):
from aggregate_if import Count
campaign = self.get_object()
collection = campaign.influencer_collection
_counts_qs = collection.influenceranalytics_set.exclude(
archived=True).values('tmp_approve_status').annotate(
Count('tmp_approve_status'))
_statuses = [
IA.APPROVE_STATUS_YES,
IA.APPROVE_STATUS_NO,
IA.APPROVE_STATUS_MAYBE,
IA.APPROVE_STATUS_PENDING,
]
_counts = {
x['tmp_approve_status']: x['tmp_approve_status__count']
for x in _counts_qs if x['tmp_approve_status'] in _statuses
}
data = {
'list': [{
'text': "{}'s".format(dict(IA.APPROVE_STATUS).get(status)),
'count': _counts.get(status, 0),
} for status in _statuses],
'pending_count':
_counts.get(IA.APPROVE_STATUS_PENDING, 0),
}
return Response(data)
def _roll_submission(self):
user = User.objects.get(id=self.scope_ids.user_id)
submissions = Submission.objects.filter(
~Q(user=user),
module=unicode(self.location),
course=unicode(self.course_id),
approved=True,
).annotate(num_scores=Count('score')).order_by('num_scores')
if not submissions.exists():
return None
for i in submissions:
# Make in random?
rolled_submission = i
# We are okay only with submission that is not scored with current user.
# Probably this can be optimized.
if not Score.objects.filter(user=user,
submission=rolled_submission).exists():
return rolled_submission
return None
def get_aggregates(tipologia=None, **kwargs):
importo_field = 'donazione__importo'
if not tipologia:
aggregation_struct = {
'sum': Sum(importo_field),
'count': Count(importo_field)
}
else:
tipologia_id, tipologia_shortname = tipologia
aggregation_struct = {
'sum': Sum(importo_field, only=Q(donazione__tipologia_cedente=tipologia_id)),
'count': Count(importo_field, only=Q(donazione__tipologia_cedente=tipologia_id))
}
return DonazioneInterventoProgramma.objects.filter(**kwargs).aggregate(**aggregation_struct)
def lojas_mais_solicitadas(request, shopping_id):
inicio_str = inicio = fim_str = fim = None
if request.method == "POST":
inicio_str = request.POST.get('inicio', None)
inicio = datetime.strptime(inicio_str, '%d/%m/%Y')
fim_str = request.POST.get('fim', None)
fim = datetime.strptime(fim_str, '%d/%m/%Y')
contexto = {'nome_shopping': Shopping.objects.get(id=shopping_id).nome}
if inicio and fim:
query_filtro = Loja.objects.annotate(pedidos=Count('pk', only=Q(shopping=shopping_id,
solicitacoes__data_criacao__gte=inicio,
solicitacoes__data_criacao__lte=fim))) \
.order_by('-pedidos')
filtradas, total_filtradas = listas_e_totais(query_filtro, 'pedidos')
contexto.update({'filtradas': filtradas, 'total_filtradas': total_filtradas,
'inicio': inicio_str, 'fim': fim_str})
else:
hoje = date.today()
mes = hoje + timedelta(days=-30)
semana = hoje + timedelta(days=-7)
solicitadas_query = Loja.relatorio_solicitacoes(shopping_id)
mais_solicitadas, total_solicitadas = listas_e_totais(solicitadas_query, 'pedidos')
mes_query = Loja.relatorio_solicitacoes(shopping_id, date=mes)
mais_do_mes, total_mes = listas_e_totais(mes_query, 'pedidos')
semana_query = Loja.relatorio_solicitacoes(shopping_id, date=semana)
mais_da_semana, total_semana = listas_e_totais(semana_query, 'pedidos')
contexto.update({'mais_solicitadas': mais_solicitadas, 'total_solicitadas': total_solicitadas,
'mais_do_mes': mais_do_mes, 'total_mes': total_mes,
'mais_da_semana': mais_da_semana, 'total_semana': total_semana})
return render(request, "relatorios/lojas_mais_solicitadas.html", contexto)
def select_undecided_emails_count(self):
u"""
Use to select ``Inforequest.undecided_emails_count``. Redundant if
``prefetch_related(Inforequest.prefetch_undecided_emails())`` is already used.
"""
return self.annotate(undecided_emails_count=Count(
u'inforequestemail',
only=Q(inforequestemail__type=InforequestEmail.TYPES.UNDECIDED)))
def categorias_mais_vistas(request, shopping_id):
inicio_str = inicio = fim_str = fim = None
if request.method == "POST":
inicio_str = request.POST.get('inicio', None)
inicio = datetime.strptime(inicio_str, '%d/%m/%Y')
fim_str = request.POST.get('fim', None)
fim = datetime.strptime(fim_str, '%d/%m/%Y')
contexto = {'tipo': 'categoria', 'modalidade': None,
'nome_shopping': Shopping.objects.get(id=shopping_id).nome}
if inicio and fim:
query_filtro = Categoria.objects.annotate(vistas=Count('pk', only=Q(ofertas__loja__shopping=shopping_id,
ofertas__logs__acao=1,
ofertas__logs__data_criacao__gte=inicio,
ofertas__logs__data_criacao__lte=fim+timedelta(days=1)))) \
.order_by('-vistas')
filtradas, total_filtradas = listas_e_totais(query_filtro, 'vistas')
contexto.update({'filtradas': filtradas, 'total_filtradas': total_filtradas,
'inicio': inicio_str, 'fim': fim_str})
else:
hoje = date.today()
mes = hoje + timedelta(days=-30)
semana = hoje + timedelta(days=-7)
mais_query = Categoria.objects.annotate(vistas=Count('pk', only=Q(ofertas__loja__shopping=shopping_id,
ofertas__logs__acao=1))).order_by('-vistas')
mes_query = Categoria.objects.annotate(vistas=Count('pk', only=Q(ofertas__loja__shopping=shopping_id,
ofertas__logs__acao=1,
ofertas__logs__data_criacao__gte=mes)))\
.order_by('-vistas')
semana_query = Categoria.objects.annotate(vistas=Count('pk', only=Q(ofertas__loja__shopping=shopping_id,
ofertas__logs__acao=1,
ofertas__logs__data_criacao__gte=semana)))\
.order_by('-vistas')
mais_vistas, total_vistas = listas_e_totais(mais_query, 'vistas')
mais_do_mes, total_mes = listas_e_totais(mes_query, 'vistas')
mais_da_semana, total_semana = listas_e_totais(semana_query, 'vistas')
contexto.update({'mais_vistas': mais_vistas, 'total_vistas': total_vistas,
'mais_do_mes': mais_do_mes, 'total_mes': total_mes,
'mais_da_semana': mais_da_semana, 'total_semana': total_semana})
return render(request, "relatorios/mais_vistas.html", contexto)
def select_undecided_emails_count(self):
u"""
Use to select ``Profile.undecided_emails_count``.
"""
return self.annotate(undecided_emails_count=Count(
u'user__inforequest__inforequestemail',
only=Q(user__inforequest__inforequestemail__type=InforequestEmail.
TYPES.UNDECIDED)))
def get_score(self):
# We need to make two aggregate-select to calculate score. Need to find out how to cache it.
default_score = {
'score': 0,
'total': self.weight,
'grades': 0,
'own_grades': 0,
'need_grades': self.grades_required,
'passed': False
}
if self.submission is None:
return default_score
own_submission = self._get_submission()
# Score over user's submission
aggregated_score = Score.objects.filter(submission=own_submission) \
.aggregate(avg=Avg('score'), count=Count('submission'))
# Check whether user has graded several submissions himself to get his score
aggregated_score_own = Score.objects.filter(
user_id=self.scope_ids.user_id,
submission__module=own_submission.module,
submission__course=own_submission.course).aggregate(
count=Count('submission'))
# Update data
default_score['grades'] = aggregated_score['count']
default_score['own_grades'] = aggregated_score_own['count']
# Submission must be graded several times before score can be published
if aggregated_score['count'] < self.grades_required:
return default_score
# User need to grade at least several others' submission before score is published
if aggregated_score_own['count'] < self.grades_required:
return default_score
default_score['score'] = (aggregated_score['avg'] /
self.points) * self.weight
default_score['passed'] = True
return default_score
def staff_info(self, request, suffix=''):
assert self.is_course_staff()
submissions_all = Submission.objects.filter(module=self.location)
# We need 2 separated annotations due to bug
# https://code.djangoproject.com/ticket/10060
# Number of scores received by this submission
submissions_num_scores = submissions_all.annotate(
num_scores=Count('score')).values('id', 'user__username',
'user__id', 'num_scores',
'approved')
# Number of scores done by this submission author
submissions_num_scores_by_user = submissions_all.annotate(
num_scores_by_user=Count(
'user__score',
only=Q(
user__score__submission__module=self.location))).values()
# Create map of submissions from all submissions: id => row
submission_map = dict()
for submission in submissions_num_scores:
submission_map[submission['id']] = submission
# Update map with num_scores_by_user (second annotate)
for submission in submissions_num_scores_by_user:
submission_map[submission['id']][
'num_scores_by_user'] = submission['num_scores_by_user']
response = {
'location': unicode(self.location),
'summary': {
'total': submissions_all.count(),
'approved': submissions_all.filter(approved=True).count()
},
'submissions': submission_map.values(),
'score': {
'need_grades': self.grades_required
}
}
return Response(json_body=response)
def query_relatorio(cls, shopping_id, acao, tipo, date=None):
if tipo == cls.EVENTO and not date:
return Oferta.objects.annotate(vistas=Count('pk', only=Q(shopping=shopping_id,
logs__acao=acao,
tipo=tipo))).order_by('-vistas')
elif tipo == cls.EVENTO and date:
return Oferta.objects.annotate(vistas=Count('pk', only=Q(shopping=shopping_id,
logs__acao=acao,
logs__data_criacao__gte=date,
tipo=tipo))).order_by('-vistas')
elif not date:
return Oferta.objects.annotate(vistas=Count('pk', only=Q(loja__shopping=shopping_id,
logs__acao=acao,
tipo=tipo))).order_by('-vistas')
else:
return Oferta.objects.annotate(vistas=Count('pk', only=Q(loja__shopping=shopping_id,
logs__acao=acao,
logs__data_criacao__gte=date,
tipo=tipo))).order_by('-vistas')
def test_more_aggregation(self):
a = Author.objects.get(name__contains='Norvig')
b = Book.objects.get(name__contains='Done Right')
b.authors.add(a)
b.save()
vals = Book.objects.annotate(num_authors=Count("authors__id")).filter(
authors__name__contains="Norvig",
num_authors__gt=1).aggregate(Avg("rating"))
self.assertEqual(vals, {"rating__avg": 4.25})
def proposals():
q = Proposal.objects.aggregate(
proposals=Count('pk'),
proposal_elab=Count('pk', only=Q(status='elab')),
proposal_pending=Count('pk', only=Q(status='p')),
proposal_concluded=Count('pk', only=Q(status='co')),
proposal_approved=Count('pk', only=Q(status='a')),
proposal_canceled=Count('pk', only=Q(status='c')),
)
return q
def proposals(self):
return Proposal.objects.all().count()
def proposal_elab(self):
return Proposal.objects.filter(status='elab').count()
def proposal_pending(self):
return Proposal.objects.filter(status='p').count()
def proposal_concluded(self):
return Proposal.objects.filter(status='co').count()
def proposal_approved(self):
return Proposal.objects.filter(status='a').count()
def proposal_canceled(self):
return Proposal.objects.filter(status='c').count()
def test_only_requires_extra_join(self):
publishers = Publisher.objects.annotate(jeff_books=Count(
'book', only=Q(
book__contact__name__icontains='Jeff'))).order_by('id')
self.assertQuerysetEqual(
publishers,
[('Apress', 0), ('Sams', 0), ('Prentice Hall', 1),
('Morgan Kaufmann', 0), ('Jonno\'s House of Books', 0)],
lambda b: (b.name, b.jeff_books),
)
# Test with compound Q object
# Get publishers annotated with a count of books that are in stores with coffee or named Books.com
q = Q(book__store__has_coffee=True) | Q(
book__store__name__icontains="Books.com")
publishers = Publisher.objects.annotate(
coffee_books=Count('book', distinct=True, only=q)).order_by('id')
self.assertQuerysetEqual(
publishers,
[('Apress', 2), ('Sams', 0), ('Prentice Hall', 2),
('Morgan Kaufmann', 1), ('Jonno\'s House of Books', 0)],
lambda b: (b.name, b.coffee_books),
)
def run(self, campaign_ids=None):
from aggregate_if import Count
from debra.models import (
BrandJobPost, InfluencerJobMapping, MailProxyMessage)
if campaign_ids:
campaigns = BrandJobPost.objects.filter(id__in=campaign_ids)
else:
campaigns = BrandJobPost.objects.exclude(archived=True)
total_campaigns = campaigns.count()
for n, campaign in enumerate(campaigns, start=1):
print '* {}/{} campaign (id={}) processing'.format(
n, total_campaigns, campaign.id)
ijms = list(campaign.candidates.filter(campaign_stage__in=[
InfluencerJobMapping.CAMPAIGN_STAGE_PRE_OUTREACH,
InfluencerJobMapping.CAMPAIGN_STAGE_WAITING_ON_RESPONSE,
InfluencerJobMapping.CAMPAIGN_STAGE_NEGOTIATION,
]).annotate(
agr_messages_count=Count('mailbox__threads', only=(
Q(mailbox__threads__type=MailProxyMessage.TYPE_EMAIL)
)),
agr_blogger_messages_count=Count('mailbox__threads', only=(
Q(mailbox__threads__type=MailProxyMessage.TYPE_EMAIL) &
Q(mailbox__threads__direction=\
MailProxyMessage.DIRECTION_INFLUENCER_2_BRAND)
))
).values('id', 'agr_messages_count', 'agr_blogger_messages_count',
'campaign_stage'))
_wrong_count = 0
for ijm in ijms:
if not self.check_instance(ijm):
self._wrong_ids.append(ijm['id'])
_wrong_count += 1
print '** {} out of {} ids are wrong'.format(_wrong_count,
len(ijms))
print 'Total: {} wrong ids'.format(len(self._wrong_ids))
def get_platform_counts(self):
_t0 = time.time()
qs = self.post_analytics_queryset
counts = {
item['post__platform__platform_name']: item['count']
for item in qs.values('post__platform__platform_name').annotate(
count=Count('post'))
}
counts['Blog'] = sum(cnt for pl, cnt in counts.items()
if pl in Platform.BLOG_PLATFORMS)
for pl, _ in counts.items():
if pl in Platform.BLOG_PLATFORMS:
del counts[pl]
total = sum(counts.values())
print '* get_platform_counts took {}'.format(time.time() - _t0)
return counts, total
def datachecks(superficial, autofix):
u"""
Checks that every ``Message`` has exactly one ``InforequestEmail`` relation to
``Inforequest``.
"""
emails = (Message.objects
.annotate(Count(u'inforequest'))
.filter(inforequest__count__gt=1)
)
if superficial:
emails = emails[:5+1]
issues = [u'{} is assigned to {} inforequests'.format(m, m.inforequest__count) for m in emails]
if superficial and issues:
if len(issues) > 5:
issues[-1] = u'More messages are assigned to multiple inforequests'
issues = [u'; '.join(issues)]
for issue in issues:
yield datacheck.Error(issue + u'.')
def datachecks(superficial, autofix):
u"""
Checks that every ``Action.email`` is assigned to ``Action.branch.inforequest``.
"""
actions = (Action.objects
.filter(email__isnull=False)
.annotate(Count(u'branch__inforequest__email_set',
only=Q(branch__inforequest__email_set=F(u'email'))))
.filter(branch__inforequest__email_set__count=0)
)
if superficial:
actions = actions[:5+1]
issues = [u'{} email is assigned to another inforequest'.format(a) for a in actions]
if superficial and issues:
if len(issues) > 5:
issues[-1] = u'More action emails are assigned to other inforequests'
issues = [u'; '.join(issues)]
for issue in issues:
yield datacheck.Error(issue + u'.')
def datachecks(superficial, autofix):
u"""
Checks that every ``Inforequest`` instance has exactly one main branch.
"""
inforequests = (Inforequest.objects.annotate(
Count(u'branch',
only=Q(branch__advanced_by=None))).filter(~Q(branch__count=1)))
if superficial:
inforequests = inforequests[:5 + 1]
issues = [
u'%r has %d main branches' % (r, r.branch__count) for r in inforequests
]
if superficial and issues:
if len(issues) > 5:
issues[
-1] = u'More inforequests have invalid number of main branches'
issues = [u'; '.join(issues)]
for issue in issues:
yield datacheck.Error(issue + u'.')
def test_backwards_m2m_annotate(self):
authors = Author.objects.filter(name__contains="a").annotate(
Avg("book__rating")).order_by("name")
self.assertQuerysetEqual(authors, [('Adrian Holovaty', 4.5),
('Brad Dayley', 3.0),
('Jacob Kaplan-Moss', 4.5),
('James Bennett', 4.0),
('Paul Bissex', 4.0),
('Stuart Russell', 4.0)], lambda a:
(a.name, a.book__rating__avg))
authors = Author.objects.annotate(
num_books=Count("book")).order_by("name")
self.assertQuerysetEqual(authors, [('Adrian Holovaty', 1),
('Brad Dayley', 1),
('Jacob Kaplan-Moss', 1),
('James Bennett', 1),
('Jeffrey Forcier', 1),
('Paul Bissex', 1),
('Peter Norvig', 2),
('Stuart Russell', 1),
('Wesley J. Chun', 1)], lambda a:
(a.name, a.num_books))
def test_annotate_m2m(self):
books = Book.objects.filter(rating__lt=4.5).annotate(
Avg("authors__age")).order_by("name")
self.assertQuerysetEqual(
books,
[('Artificial Intelligence: A Modern Approach', 51.5),
('Practical Django Projects', 29.0),
('Python Web Development with Django', Approximate(30.3,
places=1)),
('Sams Teach Yourself Django in 24 Hours', 45.0)],
lambda b: (b.name, b.authors__age__avg),
)
books = Book.objects.annotate(
num_authors=Count("authors")).order_by("name")
self.assertQuerysetEqual(books, [
('Artificial Intelligence: A Modern Approach', 2),
('Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp',
1), ('Practical Django Projects', 1),
('Python Web Development with Django', 3),
('Sams Teach Yourself Django in 24 Hours', 1),
('The Definitive Guide to Django: Web Development Done Right', 2)
], lambda b: (b.name, b.num_authors))
def test_annotation(self):
vals = Author.objects.filter(pk=1).aggregate(Count("friends__id"))
self.assertEqual(vals, {"friends__id__count": 2})
books = Book.objects.annotate(
num_authors=Count("authors__name")).filter(
num_authors__ge=2).order_by("pk")
self.assertQuerysetEqual(books, [
"The Definitive Guide to Django: Web Development Done Right",
"Artificial Intelligence: A Modern Approach",
], lambda b: b.name)
authors = Author.objects.annotate(
num_friends=Count("friends__id", distinct=True)).filter(
num_friends=0).order_by("pk")
self.assertQuerysetEqual(authors, [
"Brad Dayley",
], lambda a: a.name)
publishers = Publisher.objects.annotate(
num_books=Count("book__id")).filter(num_books__gt=1).order_by("pk")
self.assertQuerysetEqual(publishers, [
"Apress",
"Prentice Hall",
], lambda p: p.name)
publishers = Publisher.objects.filter(
book__price__lt=Decimal("40.0")).annotate(
num_books=Count("book__id")).filter(num_books__gt=1)
self.assertQuerysetEqual(publishers, [
"Apress",
], lambda p: p.name)
books = Book.objects.annotate(num_authors=Count("authors__id")).filter(
authors__name__contains="Norvig", num_authors__gt=1)
self.assertQuerysetEqual(books, [
"Artificial Intelligence: A Modern Approach",
], lambda b: b.name)
