Subquery annotation across a m2m field

Using Django Using the ORM
1

I find myself trying to do a somewhat difficult annotation involving a ManyToManyField. I’m modeling genealogy and need to annotate the most recent spouse that someone has. The models are fairly simple:

class Person(models.Model):
    name = models.CharField(max_length=100)
    ....

class Marriage(models.Model):
    people = models.ManyToManyField("Person")
    date_of_marriage = models.DateField(blank=True, null=True)
    ...

What I would like to do is to annotate the name of the most recent person that each person in a query set is married to. Each time I try to write this it seems to get tricky however since each marriage has two people (discriminating against polyamory I know) and we want to return the name field of the other person.

So if Tom was married to Sally for a while, then Tom married Sue, I would like to annotate Sue’s name since her marriage will to Tom will have a more recent date than Sally’s marriage to Tom.

If seems like there should be some way to do this with nested subqueries and maybe using .through but I can’t figure out how. Anybody have ideas?

2

It should be easier to reason about if you break the problem in two

  1. Start by retrieving the latest marriage
  2. Then retrieve the people associated to it by excluding the current person

The following should also support poly-amorous marriage :slight_smile:

Person.objects.annotate(
    latest_marriage_id=Marriage.objects.filter(
        peoples__in=OuterRef("pk"),
    ).order_by("-date_of_marriage").values("id")[:1],
    latest_union=(
        Marriage.objects
        .filter(
            id=OuterRef("latest_marriage_id"),
        ).values(
            names=ArrayAgg(
                "people__name",
                filter=~Q(people__id=OuterRef("pk"))
            )
        )
    )
)

If you don’t use Postgres write your own implementation of STRING_AGG aka GROUP_CONCAT or replace the second subquery with

Marriage.objects
.filter(
    id=OuterRef("latest_marriage_id"),
).order_by(
   OrderBy(Q(people__id=OuterRef("id")))
).values("people__name")[:1]

This should also take only a single member of the union.

3

Thanks, I’m not sure why I was making it harder than it needed to be. That works well.