Filtering data from joining table in Django by foreign key-CodePudding

I have model classes that look like:

class Wine(models.Model):
    wine_id = models.IntegerField(blank=True, null=False, primary_key=True)
    wine_name = models.TextField(blank=True, null=True)
    wine_type = models.TextField(blank=True, null=True)
    wine_year = models.IntegerField(blank=True, null=True)
    wine_alcohol = models.FloatField(blank=True, null=True)
    wine_country = models.TextField(blank=True, null=True)
    wine_price = models.FloatField(blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'wine'

class Flavor(models.Model):
    flavor_id = models.IntegerField(primary_key=False)
    flavor_name = models.TextField(blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'flavor'

and one joining table between these two:

class FlavorWine(models.Model):
    flavor_wine_id = models.IntegerField(blank=True, null=False, primary_key=True)
    flavor_group = models.TextField(blank=True, null=True)
    flavor_count = models.IntegerField(blank=True, null=True)
    wine_id = models.ForeignKey('Wine', on_delete=models.DO_NOTHING)
    flavor_id = models.ForeignKey('Flavor', on_delete=models.DO_NOTHING)

    class Meta:
        managed = False
        db_table = 'flavor_wine'

Now, whenever I try to retrieve the data I get errors. I tried exampled used in: Django Filter by Foreign Key and Django: join two tables, but to no success.

I tried:

wines = Wine.objects.filter(wine_id=wine_id)
wine_flavor = FlavorWine.objects.filter(wine_id__in=wines.values('wine_id'))

return HttpResponse(serializers.serialize('json', wine_flavor, fields=('wine_id', 'flavor_group', 'flavor_count', 'flavor_id')))

and

wine_flavor = serializers.serialize('json', FlavorWine.objects.filter(wine_id_id__gt=wine_id), fields=('wine_id', 'flavor_group', 'flavor_count', 'flavor_id'))

and

wine_flavor = serializers.serialize('json', FlavorWine.objects.filter(wine_id__flavorwine__exact=wine_id), fields=('wine_id', 'flavor_group', 'flavor_count', 'flavor_id'))

And different combinations that were offerred, but none of them work, either it fails when joining tables or it cannot find the required field. I always get the hint:

HINT: Perhaps you meant to reference the column "flavor_wine.wine_id".

I mean, that's the exact column I'm trying to reference, but I cannot find the proper way of doing so.

CodePudding user response：

To filter from ForeignKey you can simply pass that model's instance

In you first method:

wine_flavor = FlavorWine.objects.filter(wine_id__in=wines.values('wine_id'))

if wine_id is an instance of Wine model then you can simply write

 wine_flavor = FlavorWine.objects.filter(wine_id=wine_id)

and if wine_id is a list of ids from Wine model then you can just right the following:

wine_flavor = FlavorWine.objects.filter(wine_id__id__in=wine_id)

Let me explain what above line does,

suppose wine_id = ['1', '2', '3',....] where '1' represent id from Wine model

then filter those from FlavorWine(FlavorWine.objects.filter)

Where id from Wine model(wine_id__id) is in list(wine_id)

(if you need any more help then comment below, I can update my answer accordingly)

CodePudding user response：

So in order to filter between related models by foreignkey, it's pretty straight forward, by using the prefetct_ralated function which takes the argument of the related name of the models. As in my example below. Here is a link to read more to help you understand querying with prefetch related. https://docs.djangoproject.com/en/4.0/ref/models/querysets/#prefetch-related

wines = Wine.objects.prefetch_related('flavorwine_set').filter(wine_id=wine_id)
wine_flavor = FlavorWine.objects.prefetch_related('flavorwine_set').filter(wine_id__in=wines.values('wine_id'))

CodePudding user response：

Try this article out, https://www.django-antipatterns.com/antipattern/foreign-key-with-id-suffix.html and see if it fixes your issue.

The main reason why it is a problem is because the .other_model itself does not store the id. Indeed, Django makes an implicit twin-field with an _id suffix that stores the primary key