This is probably a simple answer but I cant find a solid "yes" or "no" on the subject. I am using Django simple-history and I am trying to filter the base model objects by information in the history table. Because simple-history generates the history table automatically, and I didn't create a history model for it per-se I dont know if the django ORM lookup syntax works here. My table relations look like this. Here's the model:
class RepairForm(models.Model):
user_id = models.ForeignKey(User, on_delete=models.DO_NOTHING,)
return_number = models.CharField(max_length=200, unique=True)
incident = models.CharField(max_length=100, blank=True)
...
# Simple-history
history = HistoricalRecords()
And I am trying to filter RepairForm objects by their history. As an example; there is a field in only the history table called "history_change_reason." It's just a field for saving a string that (ideally) describes why the update happened. Referencing the table image I linked, I thought I could filter down RepairForm objects using the RepairFormHistory table by traversing the relationship they both had with the user table. Something like:
RepairForm.objects.filter(user_id__repairformhistory__history_change_reason='Custom Repair form page')
"repairformhistory" was my best guess as to what the history model (if there is one) is called. The error:
Related Field got invalid lookup: repairformhistory
Am I way off base here? Can I traverse a relationship like that? even though there is no model for "repairformhistory," just a table linked to the original through a user?
The foreign key from the historical model to the base model exists by default since the historical table includes all of the fields that are included on the base model. However, since the key is only copied as an integer or a uuid, the db does not know that this is a foreign key. We currently have the instance attribute on a historical model that returns the base model instance version of a historical instance – but that should only work for a particular instance. What I suggest doing here is query the historical table for the ids that you want, and then filter the base model using those keys like this:
ids = list(HistoricalRepairForm.filter(incident='abc').order_by('id').distinct('id').values_list('id', flat=True))
RepairForm.filter(id__in=ids)