How to query foreignkey and return only one object (prefetch)

Using Django Using the ORM
1

Hi,
I am having a headache writing a query :slight_smile:
That’s my models:

class Mission(models.Model):
    machine = models.ForeignKey(Machine, on_delete=models.CASCADE, related_name="missions")
    start_dt = models.DateTimeField(null=False)
    longitude = models.DecimalField(max_digits=9, decimal_places=6, null=True)
    latitude = models.DecimalField(max_digits=9, decimal_places=6, null=True)

    @cached_property
    def timezone(self) -> datetime.tzinfo:
        timezone_str = get_tz(float(self.longitude), float(self.latitude))
        return pytz.timezone(timezone_str)

and

class Machine(models.Model):
    name = models.CharField(max_length=12, null=False, unique=True)
    created_at = models.DateTimeField(_("created at"), auto_now_add=True)
    updated_at = models.DateTimeField(_("updated at"), auto_now=True)

I want to make a query with all the machines and for each machine a last_mission field where I have my last mission.

like:

machine.last_mission.timezone
...

.I am completely stuck doing it with the django ORM.

that’s my postgres query that doesn’t do exactly what I want as there is no last_mission key and not the model property.

Thanks a lot for you help :slight_smile:

2

It sounds to me like you are looking for the Mission related to a specific Machine having the largest start_dt.

Assuming you have an instance of Machine named machine, then the Missions related to machine would be machine.missions. That’s the name of the related object manager for that relationship.

Since it’s a manager, you can then apply query functions to it:
machine.missions.order_by('-start_dt').first()
This will give you the instance of Mission with the largest start_dt related to machine.

3

Hi Ken thanks for your reply:
In fact what I would like is a query composed of each machine (where I can access the name, the id, …) with machine.* and in addition of that I want that the field machine.last_mission contains all of the value of machine.missions.order_by('-start_dt').first() object so I can retrieve these informations with machine.last_mission.timezone Do you understand what I mean ?

4

You can’t do that directly, because of the reverse foreign key relationship.

Can you provide a little more detail as to why you’re asking for this to be done in exactly that way? (I’m guessing the reason is because you’re trying to reference what you’re calling last_mission in a template? Is that the case?)