Django - join two models

Question

For the following models I want to retrieve all the devices that have an entry in the History table with transition_date between a specified interval:

class History(models.Model):
    device = models.ForeignKey(DeviceModel, to_field='id')
    transition_date = models.DateTimeField()

    class Meta:
        db_table = 'History'

class DeviceModel(models.Model):
    id = models.IntegerField()
    name = models.CharField()

    class Meta:
        db_table = 'Devices'

I have this code that filters for the specified interval:

devices = DeviceModel.objects.filter(history__transition_date__range=(startDate, endDate))

That gives me as many rows as History table has with transition_date in the specified range. The filter function performs an INNER JOIN between DeviceModel and History on device id retrieving only DeviceModel fields. My question is how do I retrieve data from both History and DeviceModel at the same time while joining them as with filter/select_related on device id. I'd rather not write a custom SQL query.

Answer 1

In your models Device and History models are related with a foreign key from History to DeviceModel, this mean when you have a History object you can retrieve the Device model related to it, and viceversa (if you have a Device you can get its History).

Example:

first_history = History.objects.all()[0]
first_history.device  # This return the device object related with first_history
first_history.device.name # This return the name of the device related with first_history

But it works also in the other way, you could do:

first_device = Device.objects.all()[0]
first_device.history  # This return the history object related with device
first_device.history.transition_date  # Exactly as before, can access history fields

So in your query:

devices = DeviceModel.objects.filter(history__transition_date__range=(startDate, endDate))

This return a device list, but you can access to the history related with each device object

Isn't that enough for you ? You have a Device list, and each device can access to its related History object

Info: When you declare a ForeignKey field the models are related by id for default, I say this because you're doing:

device = models.ForeignKey(DeviceModel, to_field='id')

as you can see you're using to_field='id' but this relation is done by default, if you do:

device = models.ForeignKey(DeviceModel)

You'll get same results

---

(EDIT) Using .values() to obtain list [device.name, history.date]

To get a list like you said [device.name, history.date] you can use .values() function of Django QuerySet, official documentation here

You can try something like:

devices = DeviceModel.objects.filter(history__transition_date__range=(startDate, endDate)).values('name','history__transition_date')  
# Notice that it is 'history _ _ transition_date with 2 underscores