# Trying to map a Postgres SQL Query to an ORM queryset

**URL:** <https://forum.djangoproject.com/t/trying-to-map-a-postgres-sql-query-to-an-orm-queryset/31482>\
**Category:** Using the ORM\
**Created:** [May 24, 2024, 8:33am UTC](https://forum.djangoproject.com/t/trying-to-map-a-postgres-sql-query-to-an-orm-queryset/31482 "2024-05-24T08:33:03Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![ipl31](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/ipl31/32/21507_2.png) [@ipl31](https://forum.djangoproject.com/u/ipl31)\
**Post date:** [May 24, 2024, 8:33am UTC](https://forum.djangoproject.com/t/trying-to-map-a-postgres-sql-query-to-an-orm-queryset/31482/1 "2024-05-24T08:33:03Z")

</div>

I have an the following queryset in my Django app:

```auto
queryset = (
            (
                models.Activity.objects.filter(organization=organization_pk)
                .annotate(
                    period=Trunc("timestamp", period, output_field=DateTimeField()),
                )
                .values(
                    "data_source __uuid", "event_type__ uuid", "identity__uuid", "period"
                )
                .distinct()
                .annotate(count=Count("id"))
            )
            .order_by("-period")
)

```

Which produuces queries similar to this when being used through my DRF views:

```auto
SELECT "integrations_datasource"."uuid",
                "core_eventtype"."uuid",
                "core_identity"."uuid",
                Date_trunc('month', "core_activity"."timestamp" at time zone 'UTC') AS "period",
                Count("core_activity"."id") AS "count"
FROM "core_activity"
INNER JOIN "integrations_datasource"
ON (
                                "core_activity"."data_source_id" = "integrations_datasource"."id")
LEFT OUTER JOIN "core_eventtype"
ON (
                                "core_activity"."event_type_id" = "core_eventtype"."id")
LEFT OUTER JOIN "core_identity"
ON (
                                "core_activity"."identity_id" = "core_identity"."id")
WHERE "core_activity"."organization_id" = 1
GROUP BY "integrations_datasource"."uuid",
                "core_eventtype"."uuid",
                "core_identity"."uuid",
                4
ORDER BY 4 DESC limit 500 offset 500

```

I have found the following refactor of the above query to be much more performant in my environment:

```auto
WITH filtered_activity AS (
    SELECT DISTINCT
        data_source_id,
        event_type_id,
        identity_id,
        DATE_TRUNC('month', timestamp AT TIME ZONE 'UTC') AS period,
        COUNT(id) AS count
    FROM
        core_activity
    WHERE
        organization_id = 1
    GROUP BY
        data_source_id,
        event_type_id,
        identity_id,
        period
)
SELECT
    ds.uuid AS datasource_uuid,
    et.uuid AS eventtype_uuid,
    idn.uuid AS identity_uuid,
    fa.period,
    fa.count
FROM
    filtered_activity fa
INNER JOIN
    integrations_datasource ds ON fa.data_source_id = ds.id
LEFT JOIN
    core_eventtype et ON fa.event_type_id = et.id
LEFT JOIN
    core_identity idn ON fa.identity_id = idn.id
ORDER BY
    fa.period DESC
LIMIT 500 OFFSET 500;

```

But I am struggling to figure out how to create ORM queryset and subquery that does this. Any ideas? I am using Postgres as the RDMS.

---

<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:** [May 25, 2024, 1:56am UTC](https://forum.djangoproject.com/t/trying-to-map-a-postgres-sql-query-to-an-orm-queryset/31482/2 "2024-05-25T01:56:24Z")

</div>

It’s acknowledged that [the ORM does not provide the ability to create every possible SQL query](https://docs.djangoproject.com/en/5.0/topics/db/queries/#falling-back-to-raw-sql). It’s possible that the query you’re needing to use can’t be built using it.  
This may be a situation where you’re better off using raw SQL if you need that degree of control.

---

<div class="post-metadata">

**Author:** ![ipl31](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/ipl31/32/21507_2.png) [@ipl31](https://forum.djangoproject.com/u/ipl31)\
**Post date:** [May 25, 2024, 5:13am UTC](https://forum.djangoproject.com/t/trying-to-map-a-postgres-sql-query-to-an-orm-queryset/31482/3 "2024-05-25T05:13:14Z")

</div>

Ok thanks for the feedback @KenWhitesell appreciate it.