For simplicity sake, I will make an example to illustrate my problem.
I have a database that contains a table for baskets (primary keys basket_1, basket_2,..) and a table for fruits (apple_1, apple_2, pear_1, banana_1,...). Each fruit instance has an attribute that describes its type (apple_1, and apple_2 have an attribute type = 'apple', pear_1 has an attribute type='pear' and so on). Each basket has a one to many relationship with the fruits (for example basket_1 has an apple_1, an apple_2 and a pear_1).
My question is, given a series of inputs such as [2x elements of type apple and 1 element of type pear], is there a straightforward way to query/find which baskets do indeed contain all those fruits?
I tried something along the lines of:
from sqlalchemy import (
Table, Column, String, ForeignKey, Boolean
)
from sqlalchemy.orm import relationship, declarative_base
from sqlalchemy import (
Table, Column, String, ForeignKey, Boolean
)
from sqlalchemy.orm import relationship, declarative_base
from sqlalchemy.orm import sessionmaker
# Create session
database_path = "C:/Data/my_database.db"
engine = create_engine(database_path)
session = sessionmaker()
session.configure(bind=engine)
# Model
class Basket(Base):
__tablename__ = "baskets"
id = Column(String, primary_key=True)
fruits = relationship("Fruit",backref='baskets')
class Fruit(Base):
__tablename__ = "fruits"
id = Column(String, primary_key=True)
type = Column(String)
parent_basket = Column(String, ForeignKey('basket.id'))
# List of fruits
fruit_list = ['apple', 'apple', 'pear']
# Query baskets that contain all those elements (I currently have no idea on how to set up the condition or should I use a 'join' in this query)
CONDITION_TO_FILTER_ON = "basket should contain as many fruits of each type as specified in the fruit list"
baskets = session.query(Basket).filter(CONDITION_TO_FILTER_ON)
Sorry if the phrasing/explanation is not clear enough. I've been playing around with filters but it still isn't clear enough to me how to approach this.