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
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
- Using the the output of 1. I input
- 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.