Django -- treating many-to-many object set as one entity while filtering

Say I have three models -- Person, Food, Flavor.

Person and Flavor share a many-to-many relationship (a person can like many flavors).

Food and Flavor share a many-to-many relationship (a food can have many flavors).

For any given person, I would like to return all foods that have a set of flavors that is a subset of the person's flavors.

For example,

personA.flavor.all() --> ['spicy', 'sweet', 'bitter']

foodA.flavor.all() --> ['spicy', 'sweet']

foodB.flavor.all() --> ['spicy, 'bitter']

foodC.flavor.all() --> ['bitter', 'greasy']

I'd like to use Django's filter on Food.objects so that it would return a QuerySet containing foodA and foodB.

I understand I can achieve a similar result if I convert the user's flavors into a set, and call issuperset on each of the food's list of flavors (and add such food to a pre-defined list), but I'm wondering (and hoping) if there's a more graceful solution involving django model's filter function, something that will return me a QuerySet instead of开发者_StackOverflow中文版 a list.

Thank you very much in advance for your help.

I don't think you can achieve what you're after just using the django orm. It'd somehow involve generating SQL which was capable of checking whether a result set is a superset of another result set. This would be possible (in sql) if you could rotate a result set. SQL Server has the PIVOT and UNPIVOT operators which can do this (other DBMS packages probably have similar operators), but django isn't capable of generating such SQL. I doubt any general purpose ORM would be capable of this.

The solution you're already aware of is quite elegant enough already. Two queries are enough to get all the data required, and then a general purpose language can much more easily determine set membership. Doing so in SQL would be a bit of a nightmare (I think).

Good question though. I started writing out SQL to achieve the result until I realised it was going to be a lot more trouble than I have time for without getting paid (and probably way out of my depth!). It'd be very interesting to see a pure sql solution if anyone posts it though.