Filtering one model with the id column of a different model in Django

I want to display a table in HTML which contains columns from two different tables/models.

The models look something like this:

class Vor(models.Model):
    id_vv = models.BigIntegerField(db_column='ID_VV', blank=True, null=False, primary_key=True)
    vdat = models.DateField(db_column='VDat', blank=True, null=True)
    v_li = models.TextField(db_column='V_Li', blank=True, null=True)    
    ... # more columns/objects exist

and

class VorDet(models.Model):
    id_det = models.BigIntegerField(db_column='ID_Det', blank=True, null=False, primary_key=True)
    v_li = models.TextField(db_column='V_Li', blank=True, null=True)    
    v_twg = models.TextField(db_column='v_twg', blank=True, null=True)    
    ... # more columns/objects exist
    vor = models.ForeignKey(Vor, on_delete=models.CASCADE, blank=True, null=True) #empty as it  was not part of the legacy data table

The data in the DB was imported from csv files (legacy data base from access) using ./ manage.py migrate --fake -initial. So models.ForeignKey() is empty - I'm not quite sure if that matters or not.

I need to display vdat, v_li and v_twg in the same list. id_vv has a one-to-many connection to id_det and only becomes unique if the v_li-columns also match.

Right now I can solve the problem by:

  1. filtering the first model Vor by vdat
  2. Using the the output of 1. I input id_vv values into VorDet.objects.filter(id_det__in=...)
  3. then merge both outputs via pd.merge()
vor_list = Vor.objects.filter(vdat__startswith="2022").values() 
det_list = VorDet.objects.filter(id_det__in=[x['id_vv'] for x in vor_list]).values()

comb_list = pd.merge(pd.DataFrame(vor_list), 
                     pd.DataFrame(det_list), 
                     left_on  = ['id_vv',  'v_li'], 
                     right_on = ['id_det', 'v_li'])

comb_list=comb_list.to_dict('records')

This works but it is cumbersome and, the way I see it, not at all how this should be done.

I'd like to get this right and would be glad about any kind of hint/help.

This question sounds very much like my problem and there are quite a few others that seem like they should work but I can't seem to make it work.

Back to Top