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:
- filtering the first model
Vor
byvdat
- Using the the output of 1. I input
id_vv
values intoVorDet.objects.filter(id_det__in=...)
- 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.