Multiple many to one relationships to same table

[Chris Norman]

Hi all,
I've tried googling for this, and I get nothing. I have a table to store data about objects. Each object should have a location property which links back to the same table. Conversely, each object should have a contents property which shows all objects which have their location set to this object.

This is the code I have so far:

class DBObject(Base):
 __tablename__ = 'db_objects'
 id = Column(Integer, primary_key = True)
 name = Column(String)
 description = Column(String)
 location_id = Column(Integer, ForeignKey('db_objects.id'))
 owner_id = Column(Integer, ForeignKey('db_objects.id'))
 contents = relationship('DBObject', remote_side = location_id, backref = backref('location', remote_side = [location_id]))
 owned_objects = relationship('DBObject', remote_side = owner_id, backref = backref('owner', remote_side = [owner_id]))
 x = Column(Float)
 y = Column(Float)
 z = Column(Float)
 max_hp = Column(Float)
 damage = Column(Float)
 properties = Column(LargeBinary)

When I issue Base.metadata.create_all I get this:

Traceback (most recent call last):
  File "C:\python35\lib\site-packages\sqlalchemy\orm\relationships.py", line 2055, in _determine_joins
    consider_as_foreign_keys=consider_as_foreign_keys
  File "<string>", line 2, in join_condition
  File "C:\python35\lib\site-packages\sqlalchemy\sql\selectable.py", line 828, in _join_condition
    a, b, constraints, consider_as_foreign_keys)
  File "C:\python35\lib\site-packages\sqlalchemy\sql\selectable.py", line 918, in _joincond_trim_constraints
    "join explicitly." % (a.description, b.description))
sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between 'db_objects' and 'db_objects'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "main.py", line 30, in <module>
    start()
  File "C:\Users\Chrisn Norman\Dropbox\SRC\mindspace_server\server.py", line 15, in start
    db.initialise()
  File "C:\Users\Chrisn Norman\Dropbox\SRC\mindspace_server\db.py", line 57, in initialise
    for row in session.query(DBObject):
  File "C:\python35\lib\site-packages\sqlalchemy\orm\session.py", line 1272, in query
    return self._query_cls(entities, self, **kwargs)
  File "C:\python35\lib\site-packages\sqlalchemy\orm\query.py", line 110, in __init__
    self._set_entities(entities)
  File "C:\python35\lib\site-packages\sqlalchemy\orm\query.py", line 120, in _set_entities
    self._set_entity_selectables(self._entities)
  File "C:\python35\lib\site-packages\sqlalchemy\orm\query.py", line 150, in _set_entity_selectables
    ent.setup_entity(*d[entity])
  File "C:\python35\lib\site-packages\sqlalchemy\orm\query.py", line 3421, in setup_entity
    self._with_polymorphic = ext_info.with_polymorphic_mappers
  File "C:\python35\lib\site-packages\sqlalchemy\util\langhelpers.py", line 747, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "C:\python35\lib\site-packages\sqlalchemy\orm\mapper.py", line 1893, in _with_polymorphic_mappers
    configure_mappers()
  File "C:\python35\lib\site-packages\sqlalchemy\orm\mapper.py", line 2768, in configure_mappers
    mapper._post_configure_properties()
  File "C:\python35\lib\site-packages\sqlalchemy\orm\mapper.py", line 1710, in _post_configure_properties
    prop.init()
  File "C:\python35\lib\site-packages\sqlalchemy\orm\interfaces.py", line 183, in init
    self.do_init()
  File "C:\python35\lib\site-packages\sqlalchemy\orm\relationships.py", line 1629, in do_init
    self._setup_join_conditions()
  File "C:\python35\lib\site-packages\sqlalchemy\orm\relationships.py", line 1704, in _setup_join_conditions
    can_be_synced_fn=self._columns_are_mapped
  File "C:\python35\lib\site-packages\sqlalchemy\orm\relationships.py", line 1972, in __init__
    self._determine_joins()
  File "C:\python35\lib\site-packages\sqlalchemy\orm\relationships.py", line 2099, in _determine_joins
    % self.prop)
sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship DBObject.contents - there are multiple foreign key paths linking the tables.  Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

I'm using latest sqlalchemy from pip and Python 3.5.

Any ideas on how to fix this?

Cheers,

[Mike Bayer]

On 03/14/2016 11:15 AM, 'Chris Norman' via sqlalchemy wrote:
> Hi all,
> I've tried googling for this, and I get nothing. I have a table to store
> data about objects. Each object should have a location property which
> links back to the same table. Conversely, each object should have a
> contents property which shows all objects which have their location set
> to this object.

documentation for multiple relationships to the same table is at:

http://docs.sqlalchemy.org/en/rel_1_0/orm/join_conditions.html#handling-multiple-join-paths

hope this helps

> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Chris Norman]

Hi,

On 14/03/2016 15:19, Mike Bayer wrote:
>
>
> On 03/14/2016 11:15 AM, 'Chris Norman' via sqlalchemy wrote:
>> Hi all,
>> I've tried googling for this, and I get nothing. I have a table to store
>> data about objects. Each object should have a location property which
>> links back to the same table. Conversely, each object should have a
>> contents property which shows all objects which have their location set
>> to this object.
>
> documentation for multiple relationships to the same table is at:
>
> http://docs.sqlalchemy.org/en/rel_1_0/orm/join_conditions.html#handling-multiple-join-paths
>
>
> hope this helps
>
>

Thanks for the docs, however I've read this page loads over the past few
days - since my google searches keep turning it up - and it seemed like
the right place to be.

I can't find anything in there that helps me. This isn't to say it's not
there, but my knowledge of SQL is fairly limited, and my understanding
of things like the relationship function aren't very in depth.

Which bits should I specifically read to help?

Sorry if my questions seem a little stupid - as I say, databases are
something I use rather than understand. Recently I've been using Django
where everything is handled for me, so I'm still struggling to come to
grips with relationships and how they're made up in the real
(Django-free) world.

Thanks again.

[Christopher Lee]

A relationship usually looks at the foreign keys on the tables you specify and constructs the queries appropriately.  The error you are getting happens because there are multiple foreign keys between the tables (in this case, the same table referencing itself... shudder...).

You need to tell each relationship which foreign key to use.

e.g.,:

contents = relationship(DbObject, foreign_keys=['location_id'], ...)

-----

Technical problems aside, your database schema has some pretty serious flaws.  Having a single "objects" table that can relate to itself in all the ways in which things can relate to other things is a logistical nightmare, both in terms of performance and clarity.

For example, your schema looks suspiciously like people, places and things are all DBObjects.  A place has an id, and x, y, z coordinates.  A thing in that location would have a foreign key to the first record.  Well, what does it mean if a thing has a location_id to one set of coordinates, but has another set of coordinates in its x, y, and z values?  Similarly, it looks like things have an owner_id that references another object, but what would it mean for a place to have an owner, or a person?  How can you tell what type of thing something is?  How would you query for all the people, or all the things?

A more sane schema might be something like:

class Person(base):

    person_id = Column(Integer, primary_key=True)

class Place(base):

    place_id = Column(Integer, primary_key=True)

    x = Column(Float)

    y = Column(Float)

    z = Column(Float)

class Thing(base):

    thing_id = Column(Integer, primary_key=True)

    location_id = Column(Integer, ForeignKey(Place.place_id))

    owner_id = Column(Integer, ForeignKey(Person.person_id))

etc. 

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.

[Chris Norman]

Hi,
You're completely correct - it's a bit stupid if all I'm using is SQL.

Actually the rows in the table are just for storing things reliably, and all the magic happens with python classes which are as you describe.

Thank you for the help, I'll give it a go.

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/oXokasM5yI0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.