title: Trouble Mapping Recursive Relationship - SQLAlchemy
body: <p>I wanted to use the classical mappings (Data Mapper Pattern) in SQLAlchemy to take an existing data model and map it to an existing database. I've read the documentation pertaining to what I'm trying to do and feel lost, though I haven't had the time to read it as deeply as I want. My code is throwing the following error:</p>
  
  <blockquote>
    <p>sqlalchemy.exc.NoForeignKeysError: Could not determine join condition
    between parent/child tables on relationship Person.neighbors - there
    are no foreign keys linking these tables.  Ensure that referencing
    columns are associated with a ForeignKey or ForeignKeyConstraint, or
    specify a 'primaryjoin' expression.</p>
  </blockquote>
  
  <p>I realize the issue is from having two foreign keys pointing to the the same field and SA doesn't know how to choice. I've tried to follow the advice the warning message offers, as well as emulating different ways to map based on examples in the documentation and nothing I try works. I'm dealing with a recursive relationship (i.e. in an ER diagram this would be a many-to-many relationship that relates to a single entity, not two).</p>
  
  <p>Below is some example code. The Person/Neighbor relationship is kind of weird and isn't my actual code but it's similar in structure and represents the issue I'm having.</p>
  
  <pre><code>from sqlalchemy.orm import mapper, relation
  from sqlalchemy import Table, MetaData, Column, Integer, String, ForeignKey
  from unittest import TestCase
  
  
  # Domain Model:
  class Person():
      def __init__(self, name, neighbors=[]):
          self.name = name
          self.neighbors = neighbors
  
  
  class Neighbor():
      def __init__(self, person, distance):
          self.person = person
          self.distance = distance
  
  
  # Database Schema / ORM objects:
  metadata = MetaData()
  
  person = Table(
      'person', metadata,
      Column('name', String(20), primary_key=True))
  
  neighbor = Table(
      'neighbor', metadata,
      Column('name', String(20), ForeignKey(person.c.name), primary_key=True),
      Column('neighbor', String(20), ForeignKey(person.c.name), primary_key=True),
      Column('distance', Integer))
  
  
  # Data Mappings
  def datamap():
      properties = {'neighbors': relation(Neighbor, backref=person)}
      mapper(Person, person, properties=properties)
  
      properties = {'person': relation(Neighbor, backref=neighbor)}
      mapper(Neighbor, person, properties=properties)
  
  
  # Unit Test (just to see if we don't throw errors in this case)
  class TestMaps(TestCase):
      def test_map(self):
          # Setup
          session = self.get_session()
  
          # Exercise
          datamap()
          model = self.build_model()
          session.add(model)
          session.flush()
  
      def build_model(self):
          bill = Person('Bill')
          jack = Person('Jack')
          person.neighbors = [Neighbor(jack, 10)]
          return bill
  
      def get_session(self):
          from sqlalchemy.orm import sessionmaker
          from sqlalchemy import create_engine
          engine = create_engine('sqlite:///:memory:', echo=False)
          metadata.create_all(engine, tables=[person, neighbor])
          Session = sessionmaker(bind=engine)
          return Session()
  </code></pre>