Simple(ish) SQL to ORM query

Using Django Using the ORM
1

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.

Thanks

2

It’s going to be a lot easier to show an answer to this if you post your Django models representing these tables.

But in general, joins are automatic in that Django understands the relationship between models as an implicit connection. This means that you don’t need to show any specific “join” function in your queries.

When you want to apply filters across these relationships, see the docs for Lookups that span relationships.

For the “group by” and “order by” clauses, see the docs at Aggregations and other QuerySet clauses.

3

Ken, thanks, I just needed the pointer to the aggregate/annotate Max and Min.

from django.db.models import Max, Min
…
queryset = Member.objects.filter(is_inactive = False).annotate(max_date=Max('m_register__date'), min_date=Min('m_register__date'))