Why this is needed

Sometimes the thing you need to join is a query result: the latest exchange rate for each invoice, or sales totals grouped by product and warehouse. Django’s relationship joins start from model tables. FilteredRelation restricts which related rows match by adding an ON condition. It can even select the latest related row using a scalar Subquery in that condition. However, it cannot directly replace the joined table with a queryset that applies ORDER BY ... LIMIT, grouping, or a custom projection.

A scalar Subquery works when you need one column. If you need several columns from the same selected row, you often end up repeating that subquery. DerivedRelation lets you define the source once, join its result, and use its columns in the outer query. LATERAL joins allow that source to depend on each outer row; ordinary derived joins handle independent results.

This gives the ORM another way to express the query. Whether it is faster depends on the data and execution plan.

This backport targets Django 5.2 and later releases before the core feature is available. PostgreSQL is required.

Installation

Install from PyPI with python -m pip install django-derived-relation.

Import DerivedRelation and DerivedQuerySet from django_derived_relation.

The outer model’s manager needs the derived-query adapter. Use DerivedQuerySet.as_manager() for a new queryset. Source querysets can use ordinary Django managers.

If you already have a custom queryset, add DerivedQuerySetMixin before its existing base so the adapter’s methods run before delegating through super():

from django_derived_relation import DerivedQuerySetMixin


class InvoiceQuerySet(DerivedQuerySetMixin, ExistingInvoiceQuerySet):
    pass


class Invoice(models.Model):
    objects = InvoiceQuerySet.as_manager()

Manager.from_queryset(InvoiceQuerySet) also works. Existing queryset overrides must cooperate with super(). DerivedQuerySet is just the convenience class combining the mixin with Django’s QuerySet.

The descriptor alone cannot integrate with older Django’s alias registration, column selection, aggregation, and resolution paths. The module therefore includes query and compiler subclasses attached to the opt-in queryset. It does not monkey-patch Django classes or backend features globally. If/when you upgrade to a version of django that includes DerivedRelation, you must uninstall this package and use the django supplied one.

Choosing between FilteredRelation and DerivedRelation

Use Django’s FilteredRelation when you want an ON condition for a declared model relationship. The joined source remains the related model’s table; each matching model row can produce a result row.

Use DerivedRelation when a queryset needs to define the joined rows and columns. The source can use values() to select columns, values(...).annotate(...) to group and calculate aggregates, and order_by(...)[:1] to select its first row. FilteredRelation does not accept such a queryset as its joined source; it adds an ON condition to a model-table join. Ordering and slicing the outer queryset sort and limit the complete result, not the related rows separately for every outer row.

Some results can be obtained either way. For example, a scalar Subquery in a FilteredRelation condition can choose the newest child’s primary key, and the relationship join can retrieve that child’s fields. A LATERAL derived query expresses the ordered selection directly and can expose several columns from it. For a single value, a scalar Subquery may be sufficient.

The following examples show two other reasons to use a derived source: a match computed without a declared relationship, and summaries produced by grouping. Neither form guarantees a faster execution plan.

Historical rates without a declared relationship

An invoice stores its currency and issue date. A separate feed supplies historical rates identified by currency codes. The reporting currency is chosen by the caller, so the appropriate rate for an invoice can change between reports. There is no fixed invoice-to-rate foreign key:

from django.db import models
from django.db.models import F, OuterRef

from django_derived_relation import DerivedQuerySet, DerivedRelation


class Invoice(models.Model):
    objects = DerivedQuerySet.as_manager()

    currency = models.CharField(max_length=3)
    issued_at = models.DateTimeField()
    amount = models.DecimalField(max_digits=12, decimal_places=2)


class ExchangeRate(models.Model):
    base_currency = models.CharField(max_length=3)
    quote_currency = models.CharField(max_length=3)
    effective_at = models.DateTimeField()
    rate = models.DecimalField(max_digits=18, decimal_places=8)


reporting_currency = "USD"
rates = (
    ExchangeRate.objects.filter(
        base_currency=OuterRef("currency"),
        quote_currency=reporting_currency,
        effective_at__lte=OuterRef("issued_at"),
    )
    .order_by("-effective_at", "-pk")
    .values("rate", "effective_at")[:1]
)
invoices = Invoice.objects.alias(
    conversion=DerivedRelation(rates, lateral=True),
).annotate(
    reporting_amount=F("amount") * F("conversion__rate"),
    rate_effective_at=F("conversion__effective_at"),
)

The source selects the latest rate effective on or before each invoice’s issue date. Ordering by primary key breaks ties in effective dates. Both projected columns come from that selected rate. FilteredRelation requires a declared relationship path; these models store scalar codes and have no such path.

The default left join preserves invoices without suitable rates and returns None for their calculated amount and rate timestamp. Use join_type="inner" to exclude invoices without a match. Only Invoice needs the backport manager here; ExchangeRate can use its ordinary manager.

Joining grouped results

Suppose an inventory report joins a stock snapshot with sales totals for a caller-selected period. Each total combines several sales lines for one product and warehouse; it is a query result, not a sales-line model instance:

from datetime import date

from django.db import models
from django.db.models import F, Q, Sum

from django_derived_relation import DerivedQuerySet, DerivedRelation


class StockSnapshot(models.Model):
    objects = DerivedQuerySet.as_manager()

    product_code = models.CharField(max_length=40)
    warehouse_code = models.CharField(max_length=40)
    as_of = models.DateField()
    quantity = models.IntegerField()


class SalesLine(models.Model):
    product_code = models.CharField(max_length=40)
    warehouse_code = models.CharField(max_length=40)
    sold_on = models.DateField()
    quantity = models.IntegerField()
    line_total = models.DecimalField(max_digits=12, decimal_places=2)


period_start = date(2026, 10, 5)
period_end = date(2026, 10, 12)
totals = (
    SalesLine.objects.filter(sold_on__gte=period_start, sold_on__lt=period_end)
    .order_by()
    .values("product_code", "warehouse_code")
    .annotate(units=Sum("quantity"), revenue=Sum("line_total"))
)
snapshots = StockSnapshot.objects.filter(as_of=period_end).alias(
    sales=DerivedRelation(
        totals,
        condition=Q(
            sales__product_code=F("product_code"),
            sales__warehouse_code=F("warehouse_code"),
        ),
    ),
)
report = snapshots.values(
    "product_code",
    "warehouse_code",
    "quantity",
    "sales__units",
    "sales__revenue",
)

Use values(...).annotate(...) to keep a grouped queryset as the source. aggregate() returns a dictionary and cannot be used here. Clearing ordering prevents default ordering from changing the grouping.

There is no persisted summary model or row for a foreign key to reference. Relationships to product or warehouse models would not define the grouped query result either. FilteredRelation adds conditions to a model-table join; it cannot replace that table with this grouping and projection.

This source is independent of the outer snapshot and therefore uses the default lateral=False. The ON condition matches its two projected grouping keys. Grouping by those keys produces at most one matching source row per snapshot. Snapshots without sales remain present with None for the totals; use Coalesce if your report needs zero. Later joins can repeat those summary rows, so aggregating their values over an expanded result can count a summary more than once.

Geographic matches without a foreign key

An address might need its nearest service centre and the regions that cover it. Neither match needs a stored foreign key. These examples require GeoDjango, PostGIS, and django.contrib.gis.db.backends.postgis.

from django.contrib.gis.db import models
from django.contrib.gis.db.models.functions import Distance
from django.db.models import ExpressionWrapper, F, OuterRef, Q

from django_derived_relation import DerivedQuerySet, DerivedRelation


class Address(models.Model):
    objects = DerivedQuerySet.as_manager()

    location = models.PointField(srid=28354)


class ServiceCentre(models.Model):
    name = models.CharField(max_length=100)
    active = models.BooleanField(default=True)
    location = models.PointField(srid=28354)


class Region(models.Model):
    name = models.CharField(max_length=100)
    active = models.BooleanField(default=True)
    boundary = models.MultiPolygonField(srid=28354)

These models use a projected coordinate system suitable for the Adelaide area, with distances in metres. Choose a coordinate system appropriate for your data; longitude/latitude geometry coordinates are not metres.

address_location = ExpressionWrapper(
    OuterRef("location"),
    output_field=models.PointField(srid=28354),
)
nearest = (
    ServiceCentre.objects.filter(
        active=True,
        location__dwithin=(OuterRef("location"), 5000),
    )
    .annotate(distance=Distance("location", address_location))
    .order_by("distance", "pk")
    .values("id", "name", "distance")[:1]
)
addresses = Address.objects.alias(
    nearest=DerivedRelation(nearest, lateral=True),
).annotate(
    service_centre_id=F("nearest__id"),
    service_centre_name=F("nearest__name"),
    service_centre_distance=F("nearest__distance"),
)

regions = Region.objects.filter(active=True).values("id", "name", "boundary")
matches = Address.objects.alias(
    region=DerivedRelation(
        regions,
        condition=Q(region__boundary__covers=F("location")),
    ),
).values("id", "region__id", "region__name")

ON conditions and result rows

DerivedRelation(queryset, condition=None, join_type="left", lateral=False) accepts a Q tree or boolean expression as its condition. It preserves that condition for both ordinary and LATERAL joins. No condition means ON TRUE.

A condition can use the outer model’s fields, this source’s columns, or columns from a previously registered derived relation. Register dependencies first; forward and circular references are unsupported. Conditions can combine Q objects using & and |.

An ON condition on a left join retains outer rows when no source row satisfies it. A subsequent filter() on a projected column can remove those rows. A filter inside an ordered, sliced source changes which rows are eligible before the slice; an ON condition tests the rows chosen by that source.

Several matching source rows multiply each outer row. Registration retains the join even when none of its columns are selected, so count() and aggregates still reflect that multiplication. The alias does not make the join optional, and joined rows are not collected into a related-object list.

An ordinary source with no condition joins every source row to each outer row. The source’s model does not supply automatic joining keys. Use a condition to match projected keys, as in the sales-totals example.

Source columns can be used in F expressions, filters, values(), values_list(), ordering, and aggregates. An explicit projection is recommended, although bare model querysets are accepted. Registration clones the source without evaluating it. It does not change scalar Subquery semantics.

Restrictions

Moving to Django’s core implementation

The backport raises ImportError at import time when Django exposes django.db.models.DerivedRelation, so applications must migrate to the core implementation. Once your installed Django provides DerivedRelation, import it from django.db.models. For an application queryset with custom methods, change its base from the backport’s DerivedQuerySet to Django’s QuerySet and retain those methods and manager configuration. If you used DerivedQuerySetMixin, remove it from the bases of your custom queryset. An outer model using only DerivedQuerySet.as_manager() can return to its ordinary Django manager. The relation definitions and alias__column references use the same public API.

Development and verification

Install development dependencies with python -m pip install -e '.[test]'. Run python tests/runtests.py against a local PostgreSQL server. Connection settings use the standard PGDATABASE, PGUSER, PGPASSWORD, PGHOST, and PGPORT environment variables. The database user needs permission to create the isolated test database. SQLite validation can be run with python tests/runtests.py --sqlite; execution tests require PostgreSQL.

The 53 regression tests cover ordinary and LATERAL joins, typed projections, aggregation, null semantics, nesting, cloning, serialization, and validation. The backport has been verified on Django 5.2.0, 5.2.17, 6.0.0, 6.0.9, 6.1.0, and 6.1.2. CI tests Django 5.2 on Python 3.10–3.14, and Django 6.0, 6.1, and development main on Python 3.12–3.14. The same matrix runs weekly to catch upstream changes. Each environment installs the built wheel and runs PostgreSQL and SQLite tests.

Run the matrix locally with python -m pip install tox followed by tox. All listed Python interpreters must be installed. To run one combination, use tox -e py313-django52. PostgreSQL connection settings are passed through from the PG* environment variables. Tox runs environments sequentially; avoid tox -p because the runner uses a shared test database. Support is intended to continue from Django 5.2 until the feature reaches core; future versions require compatibility testing and may need adapter updates.

This implementation originated in an experimental Django core implementation. It uses internal query/compiler APIs, with adapters scoped to DerivedQuerySet. This project is licensed by Matthew Schinckel under the BSD 3-Clause license. The copyright notice for code derived from Django is retained in LICENSE.

Publishing releases

The release.yml workflow runs the regression matrix, verifies the version tag, builds a wheel and source distribution, validates their metadata, and publishes through PyPI Trusted Publishing when a v<version> tag is pushed. Routine branch pushes only run tests.

PyPI Trusted Publishing is configured for this repository’s release.yml workflow and the GitHub pypi environment. No PyPI API token is needed.

Record user-visible changes under Unreleased in CHANGES.rst as they are made. Before releasing, move those entries under a heading such as 0.1.0 (2026-10-13), using the actual release date, and leave an empty Unreleased section for the next changes.

Update project.version in pyproject.toml in the same commit. Version bumps are manual for now; there is only one version value to change. Check the release metadata before committing and pushing:

RELEASE_TAG=v0.1.0 python tools/check_release.py

The check requires a matching version tag and a dated changelog heading. It also runs before publication. Once that passes, commit and push the changes, then create and push the corresponding tag:

git tag v0.1.0
git push origin v0.1.0

The tag must match the package version. PyPI versions cannot be replaced; use a new version for subsequent uploads. Download the built distributions from the workflow artifacts if publication needs investigation.