Minimizing DB deletions in Django: .values_list () depending on the previous .filter () clause

I'm having a problem with a DB query in Django.

My DB (models.py):

class Food(models.Model):
    name = models.CharField(max_length=200)
    ...

class Combination(models.Model):
    food1 = models.ForeignKey(Food, related_name='food1')
    food2 = models.ForeignKey(Food, related_name='food2')
    ...

      

The order of the products in the combination doesn't matter to my problem.
Also, each combination can exist multiple times (and in both directions).

eg. food1 = "chocolate" and food2 = "fruit" are the same as food2 = "chocolate" and food1 = "fruit"
and both combinations exist multiple times.

If I want to find the whole combination, including a specific food (denoted by food_id)
I currently resort to the following queries:

combinations1 = Combination.objects.filter(food1=food_id).values_list('food2')
combinations2 = Combination.objects.filter(food2=food_id).values_list('food1')

      

It works, but needs additional processing later. I tried using Q objects with OR, but I failed because the extracted column depends on the filter ...

I know this solution is far from elegant. Should I use a custom field or CommaSeparatedIntegerField instead of two separate power fields in combination? Is there a way to get all the requested values โ€‹โ€‹(from both columns) with a single DB Hit?

Thanks for helping me!

Cheers,
Oliver

+3


source to share


2 answers


I think your best bet is to use the Q object, something like



qs = Combination.objects.filter(Q(food1=food_id)|Q(food2=food_id).values_list('food1', 'food2')
combinations = set()
for row in qs:
   if row[0] == food_id:
       combinations.append(row[1])
   else:
       combinations.append(row[0])

      

+2


source


Not

itertools.chain(Food(id=food_id).food1.all(), Food(id=food_id).food2.all())

      



what are you after?

And if the order of the products in the combinations doesn't matter, why not use ManyToManyField(Food)

on Combination

? Then there Food(id=food_id).combination_set.all()

will be all the combinations that have a certain food in them.

+2


source







All Articles