Over at https://docs.djangoproject.com/en/dev/topics/db/models/#many-to-many-relationships, the Django team describes many-to-many relationships in the context of pizzas and toppings. I have taken their example to try to find a solution to a conceptual problem I am having with modelling my own data.
For the purpose of explaining exactly what I am tying to accomplish as a result of this Stack Overflow post, consider the following (hypothetical) grammar:
TOPPING = {'a'..'z' | 'A'..'Z'};
FLOAT = {'0'..'9'}, ['.', {'0'..'9'}]; (* Non-negative numbers only *)
QUERY = "Hey Django, find me all of the pizzas in the database such that:",
{ "(", (TOPPING), (">=" | "<=" | "=="), FLOAT, ") &&" },
"(", (TOPPING, (">=" | "<=" | "=="), FLOAT, ")";
It is the QuerySet returned from issuing something along the lines of QUERY that I seek to observe. As its contents suggest, I want Django to return to me a QuerySet with all of the Pizza objects which satisfy my screening criteria. I am not confident on how the models.py should look, or how the actual queries should look. I only have enough experience to explain the pseudo query above and the results that I wish to be returned.
Thank you in advance and I am looking forward to reading your suggestions.
Extra info
Note that in my particular situation, the database is never written to after the initial data has been loaded. Reads will always be quite frequent.
I am open to using raw SQL but my preference is for use of the Django QuerySet API
Attempt 1 at solution:
I added a ToppingAmount(models.Model) class to the model. I gave it a float for the amount, and two foreign keys to associate a pizza and a topping with an amount.
from django.db import models
class Topping(models.Model):
name = models.CharField(max_length=128)
# On Python 3: def __str__(self):
def __unicode__(self):
return self.name
class Pizza(models.Model):
name = models.CharField(max_length=128)
toppings = models.ManyToManyField(Topping, through='ToppingAmount')
# On Python 3: def __str__(self):
def __unicode__(self):
return self.name
class ToppingAmount(models.Model):
topping = models.ForeignKey(Topping)
pizza = models.ForeignKey(Pizza)
amount = models.FloatField()
units = models.CharField(max_length=4) # e.g. 'g' | 'kg' | 'lb' | 'oz' ...
The above model will enable one to (for example) perform a ToppingAmount.objects.filter(topping__name='foo').filter(amount__gt=bar) and find all of the ToppingAmount objects such that there is at least bar grams of foo on its pizza.
Unfortunately, it also allows for the perverse ToppingAmount.objects.filter(pizza__name='pippo').filter(amount__gt=bar) which will find all of the ToppingAmount objects such that there is at least bar grams of 'pippo' on its topping.
That is as far as I have been able to get with this model and simple filters. We are unable to filter the resulting QuerySet with respect to other toppings (or pizzas ;) ) and amounts because this QuerySet contains only ToppingAmount objects. Every ToppingAmount object in this QuerySet will have either the same topping or the same pizza. All of their amounts (whether interpreted to describe the topping or the pizza) will also conform to the filter(amount...).
Furthermore, I observe no tangible benefits from using the many-to-many relationship here. Eliminating toppings from the Pizza class while keeping everything else the same results in a database with an identical json dump and identical utility from possible queries.
Attempt 2:
Create a Pizza table with columns for all 146 possible toppings.
from django.db import models
class Topping(models.Model):
name= modles.CharField(max_length=128)
units = models.CharField(max_length=4) # e.g. 'g' | 'kg' | 'lb' | 'oz' ...
class Pizza(models.Model):
name = models.CharField(max_length=128)
topping1_amount = models.FloatField()
.
.
.
topping146_amount = models.FloatField()
# On Python 3: def __str__(self):
def __unicode__(self):
return self.name
I am cautious to trust this solution because it creates a large and sparse table that I do not believe is necessary or desirable (comments appreciated). On the other hand, it is very simple to write my queries for this model: Pizza.objects.filter(topping1_amount__gt=20).filter(topping2_amount__lt=15)...
