title: SQLAlchemy: querying all objects with no direct relationship
body: <p>I am new to ORM, so any comment are appreciated. Using the following code, I am trying to get all <strong>Contacts</strong> that are related to certain <strong>Classification</strong>. </p>
  
  <pre><code>Base = declarative_base()
  
  contact_category_association_table = Table('contact_category_association', Base.metadata,
      Column('contact_id', Integer, ForeignKey('contacts.id')),
      Column('category_id', Integer, ForeignKey('contact_categories.id'))
  )
  
  class Contact(Base):
      __tablename__ = 'contacts'
  
      id = Column(Integer, primary_key=True)
      name = Column(String)
      categories = relationship('ContactCategory', secondary=contact_category_association_table)
  
  
  class ContactCategory(Base):
      __tablename__ = 'contact_categories'
  
      id = Column(Integer, primary_key=True)
      name = Column(String)
      contacts = relationship('Contact', secondary=contact_category_association_table)
      classification_id = Column(Integer, ForeignKey('contact_classification.id'))
      classification = relationship('ContactClassification', back_populates='categories')
  
  
  class ContactClassification(Base):
      __tablename__ = 'contact_classification'
  
      id = Column(Integer, primary_key=True)
      name = Column(String)
      categories = relationship('ContactCategory', back_populates='classification')
  </code></pre>
  
  <p>Using the following SQL I managed to get a result close to my need. However, I could not translate that using sqlalchemy ORM. </p>
  
  <pre><code>select * from contacts c
  join contact_categories cc,  contact_category_association cca, contact_classification ccl
  where c.id = cca.contact_id and cc.id = cca.category_id and ccl.id = cc.classification_id;
  </code></pre>
  
  <p>The result I am seeking is a dict object. The key will be the id or name of the classification. The value will be a list of contacts.</p>
  
  <h2><strong>Update:</strong></h2>
  
  <p>To elaborate more. Each contact is categorized by their profession (ContactCategory). The relation between contact and category is many-to-many. Categories are being classified (or group) into classes. Each category can belong to 1 class (one-to-many). The following are an example data:</p>
  
  <pre><code>Contacts
  1|Alpha
  2|Bear
  3|Cel
  4|Deer
  5|Elk
  
  ContactCategory
  1|Builder|2
  2|Carpenter|2
  3|Metal Works|2
  4|Networking &amp; Cabling|1
  5|Gypsum|2
  
  ContactClassification
  1|Electronics
  2|Building &amp; Decor
  3|Plumping &amp; Piping
  
  contact_category_association
  1|1
  1|2
  2|2
  3|4
  4|2
  4|5
  5|5
  </code></pre>
  
  <p>Using ORM, I want to get the following result it possible (as python dict object):</p>
  
  <pre><code>result = {
  'Electronics': [&lt;Cel&gt;],
  'Building &amp; Decor': [&lt;Alpha&gt;, &lt;Bear&gt;, &lt;Deer&gt;, &lt;Elk&gt;],
  'Plumping &amp; Piping': []
  }
  </code></pre>
  
  <p>Where the key is the class name and the value is a list of contacts that belong to each class (through the category). </p>
  
  <p>I am sorry if the table names are confusing. I am in refactoring process of a side-project and got no time to rename the tables yet. </p>
  
  <p>You help is appreciated. </p>