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.
On Tue, Mar 15, 2016 at 2:12 AM, 'Chris Norman' via sqlalchemy <
[email protected]> wrote:
> 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.
>
>
>
> --
> 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 [email protected].
> To post to this group, send email to [email protected].
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
