I written this query in PostgreSQL and I'm confused of conversion of this query to django orm
SELECT count(*),
concat(date_part('month', issue_date), '/', date_part('year', issue_date) ) as date
FROM affiliates_issuelog
WHERE tenant_id = '{tenant_id}'
GROUP BY date_part('month', issue_date),
date_part('year', issue_date)
ORDER BY date_part('year', issue_date) desc,
date_part('month', issue_date) desc
I have this model that records the insertion of new affiliates by date and by institution (tenant), only I need to receive from the query the total amount of records inserted per month in the year, and I was using the listview to make my pages until then but I don't know how to filter this data using orm.
class IssueLog():
tenant = models.ForeignKey("tenants.Federation", on_delete=models.CASCADE)
issue_date = models.DateField(
default=date.today, verbose_name=_("date of issue")
)
class Meta:
verbose_name = _("Entrada de emissão")
verbose_name_plural = _("Entradas de emissão")
def __str__(self):
return f"{self.institution}, {self.tenant}"
My pages that return a list of data I did as the example below, is it possible to pass the data as I want through get_queryset()?, I already managed to solve my problem using the raw query, but the project is being done only with orm so I wanted to keep that pattern for the sake of the team. Ex:
class AffiliateExpiredListView(HasRoleMixin, AffiliateFilterMxin, ListView):
allowed_roles = "federation"
model = Affiliate
ordering = "-created_at"
template_name_suffix = "issued_list_expired"
paginate_by = 20
def get_context_data(self, **kwargs):
context = super().get_context_data(**kwargs)
context["renew_form"] = AffiliateRenewForm()
tenant_t = self.request.user.tenant
context["cancel_presets"] = tenant_t.cancelationreason_set.all()
return context
def get_queryset(self):
return super().get_queryset().filter(is_expired=True).order_by('full_name')
CodePudding user response:
You can query with:
from django.db.models import Count
from django.db.models.functions import ExtractMonth, ExtractYear
IssueLog.objects.values(
year=ExtractYear('issue_date'),
month=ExtractMonth('issue_date')
).annotate(
total=Count('pk')
).order_by('-year', '-month')
This will make a queryset with dictionaries that look like:
<QuerySet [
{'year': 2022, 'month': 2, 'total': 14},
{'year': 2022, 'month': 1, 'total': 25},
{'year': 2021, 'month': 12, 'total': 13}
]>
I would not do string formatting in the database query, but just do this in the template, etc.
But the model can not be abstract = True
[Django-doc]: that means that there is no table, and that it is only used for inheritance purposes to implement logic and reuse it somewhere else.