I have the following SQL query (MySQL), which works perfectly and is quick, but I’d like to get it into an ORM form, to make manipulation of the resulting queryset easier (ie dynamic sorting, keyword searches…):
select project_member.id, project_member.first_name, project_member.last_name, max(project_register.date) as max_date, min(project_register.date) as min_date, count(*) as attendances from project_member join project_member_register on project_member.id = project_member_register.member_id join project_register on project_member_register.register_id = project_register.id join project_member_group on project_member.id = project_member_group.member_id join project_group on project_member_group.group_id = project_group.id where project_member.is_inactive = 0 and project_member_register.present = 1 and project_group.is_participation = 1 group by id order by max_date ;
I know I can use
model.objects.raw('select…'), but with that method you cannot do something like:
qs = model.objects.raw('select…') … qs.order_by('…')
Or integrate with other Django modules that modify a queryset.