# Simple(ish) SQL to ORM query

**URL:** <https://forum.djangoproject.com/t/simple-ish-sql-to-orm-query/19747>\
**Category:** Using the ORM\
**Created:** [March 23, 2023, 5:41pm UTC](https://forum.djangoproject.com/t/simple-ish-sql-to-orm-query/19747 "2023-03-23T17:41:22Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![typonaut](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/typonaut/32/2316_2.png) [@typonaut](https://forum.djangoproject.com/u/typonaut)\
**Post date:** [March 23, 2023, 5:41pm UTC](https://forum.djangoproject.com/t/simple-ish-sql-to-orm-query/19747/1 "2023-03-23T17:41:22Z")

</div>

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…):

```auto
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:

```auto
qs = model.objects.raw('select…')
…
qs.order_by('…')

```

Or integrate with other Django modules that modify a queryset.

Thanks

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [March 23, 2023, 6:09pm UTC](https://forum.djangoproject.com/t/simple-ish-sql-to-orm-query/19747/2 "2023-03-23T18:09:59Z")

</div>

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.](https://docs.djangoproject.com/en/4.1/topics/db/queries/#lookups-that-span-relationships)

For the “group by” and “order by” clauses, see the docs at [Aggregations and other QuerySet clauses](https://docs.djangoproject.com/en/4.1/topics/db/aggregation/#aggregations-and-other-queryset-clauses).

---

<div class="post-metadata">

**Author:** ![typonaut](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/typonaut/32/2316_2.png) [@typonaut](https://forum.djangoproject.com/u/typonaut)\
**Post date:** [March 23, 2023, 7:02pm UTC](https://forum.djangoproject.com/t/simple-ish-sql-to-orm-query/19747/3 "2023-03-23T19:02:42Z")

</div>

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

```auto
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'))

```