How to get a joined latest object in django ORM?

Question

I have two tables 1:N related.

I want to get only one latest deal model on each real_estate model.

class RealEstate(gis_models.Model):
    class Meta:
        db_table = "real_estate"

    id = models.BigAutoField(primary_key=True, auto_created=True)
    name = models.CharField(
        help_text="부동산 이름", null=True, blank=True, max_length=30
    )
    build_year = models.SmallIntegerField(
        help_text="건축년도", null=False, blank=False
    )
    regional_code = models.CharField(
        help_text="지역코드",
        null=False,
        blank=False,
        max_length=6,
    )
    lot_number = models.CharField(
        help_text="지번(구획마다 부여된 땅 번호, 서울특별시 서초구 반포동 1-1)",
        null=False,
        blank=False,
        max_length=50,
    )
    road_name_address = models.CharField(
        help_text="도로명 주소", null=True, blank=True, max_length=50
    )
    address = models.CharField(
        help_text="주소", null=True, blank=True, max_length=50
    )
    real_estate_type = models.CharField(
        help_text="부동산 타입",
        choices=REAL_ESTATE_TYPES,
        max_length=20,
    )
    latitude = models.CharField(
        help_text="위도", null=False, blank=False, max_length=20
    )
    longitude = models.CharField(
        help_text="경도", null=False, blank=False, max_length=20
    )
    point = gis_models.PointField(geography=True, null=False, blank=False)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)


class Deal(models.Model):
    class Meta:
        db_table = "deal"

    id = models.BigAutoField(primary_key=True, auto_created=True)

    deal_price = models.PositiveIntegerField(
        help_text="거래금액(전월세 보증금)", null=False, blank=False
    )
    brokerage_type = models.CharField(
        help_text="중개/직거래(거래유형)",
        null=True,
        blank=True,
        choices=BROKERAGE_TYPES,
        max_length=10,
    )
    deal_year = models.SmallIntegerField(
        help_text="계약년도(년)", null=False, blank=False
    )
    land_area = models.CharField(
        help_text="대지권면적", null=False, blank=False, max_length=10
    )
    deal_month = models.SmallIntegerField(
        help_text="계약 월", null=False, blank=False
    )
    deal_day = models.SmallIntegerField(
        help_text="계약 일", null=False, blank=False
    )
    area_for_exclusive_use = models.CharField(
        help_text="전용면적(제곱미터)", null=False, blank=False, max_length=10
    )
    floor = models.CharField(
        help_text="층", null=False, blank=False, max_length=3
    )
    is_deal_canceled = models.BooleanField(
        help_text="해제여부(거래계약이 무효, 취소, 해제)",
        null=False,
        blank=False,
    )
    deal_canceled_date = models.DateField(
        help_text="해제사유 발생일", null=True, blank=True
    )
    area_for_exclusive_use_pyung = models.CharField(
        help_text="전용면적(평)", null=False, blank=False, max_length=7
    )
    area_for_exclusive_use_price_per_pyung = models.CharField(
        help_text="전용면적 평당가", null=False, blank=False, max_length=8
    )
    deal_type = models.CharField(
        help_text="유형(DEAL(매매), JEONSE(전세), MONTHLY_RENT(월세))",
        null=False,
        blank=False,
        choices=DEAL_TYPES,
        max_length=13,
    )
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    real_estate = models.ForeignKey(
        "RealEstate",
        related_name="deals",
        on_delete=models.DO_NOTHING,
    )

I joined with prefetch_related and result of list(real_estates)[0].latest_deals is [].

            latest_deals = (
                deal_q.filter(real_estate_id=OuterRef("id"))
                .order_by("-deal_year", "-deal_month", "-deal_day")
                .values("id")[:1]
            )

            real_estates = (
                RealEstate.objects.prefetch_related(
                    Prefetch(
                        lookup="deals",
                        queryset=deal_q.filter(id__in=Subquery(latest_deals)),
                        to_attr="latest_deals",
                    )
                )
                .filter(
                    deals__is_deal_canceled=False,
                    latitude__gte=dto.sw_lat,
                    latitude__lte=dto.ne_lat,
                    longitude__gte=dto.sw_lng,
                    longitude__lte=dto.ne_lng,
                )
                .distinct()
            )

            list(real_estates)[0].latest_deals

What is the problem of this approach?

---

tried below.

qs = RealEstate.objects.annotate(
    latest_deal=FilteredRelation(
        "deals",
        condition=Q(
            deals=Subquery(
                Deal.objects.filter(real_estate=OuterRef("id"))
                .order_by("-deal_year", "-deal_month", "-deal_day")
                .values("id")[:1]
            ),
        ),
    ),
).select_related("latest_deal")

list(qs)[0].latest_deal

Answer 1

You can probably even do this in the same query with a FilteredRelation ^[Django-doc]:

from django.db.models import FilteredRelation, OuterRef, Q, Subquery

qs = RealEstate.objects.annotate(
    latest_deal=FilteredRelation(
        'deals',
        condition=Q(
            post=Subquery(
                Deal.objects.filter(real_estate=OuterRef('pk'))
                .order_by('-deal_year', '-deal_month', '-deal_day')
                .values('pk')[:1]
            ),
        ),
    ),
).select_related('latest_deal')

This will then for each RealEstate fetch the latest Deal.

---

Note: Please don't specify a year, month and day field: use a DateField ^[Django-doc]: this makes sure that the month is in range, and the day is in range of the number of that month in that year. It also will be stored more compactly in the database, and ordering will be done more efficiently.