title: Querying based on related element attributes in SQLAlchemy
body: <p>For simplicity sake, I will make an example to illustrate my problem.</p>
  <p>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).</p>
  <p>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?</p>
  <p>I tried something along the lines of:</p>
  <pre><code>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 = &quot;C:/Data/my_database.db&quot;
  engine = create_engine(database_path)
  session = sessionmaker()
  session.configure(bind=engine)
  
  
  # Model
  
  class Basket(Base):
  __tablename__ = &quot;baskets&quot;
  id = Column(String, primary_key=True)
  fruits = relationship(&quot;Fruit&quot;,backref='baskets')
  
  class Fruit(Base):
  __tablename__ = &quot;fruits&quot;
  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 = &quot;basket should contain as many fruits of each type as specified in the fruit list&quot;
  
  baskets = session.query(Basket).filter(CONDITION_TO_FILTER_ON)
  </code></pre>
  <p>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.</p>