Generic Associations - table_per_association: parent attribute

[Sven]

Hello,

Today I have questions regarding generic associations and more specifically the table_per_association example:

http://docs.sqlalchemy.org/en/latest/_modules/examples/generic_associations/table_per_association.html

I am trying to adapt it to the following case:

• A class Object which represents objects in the game (like potions or weapons)
• A class ObjectContainer which represents a container with a list of objects contained in it (a chest for example)
• A class Hands which represents the hands of a player. These hands can hold objects. However, the class Hands must not directly contain the list of the objects held by the players. The class Hands finds them in another way

Here is my code:

from sqlalchemy.ext.declarative import as_declarative, declared_attr
from sqlalchemy import create_engine, Integer, Column, \
                    String, ForeignKey, Table
from sqlalchemy.orm import Session, relationship

@as_declarative()

class Base(object):
    """Base class which provides automated table name
    and surrogate primary key column.
    """

    @declared_attr
    def __tablename__(cls):
        return cls.__name__.lower()
    id = Column(Integer, primary_key=True)

class Object(Base):
    name = Column(String, nullable=False)

class HasObjects(object):

    @declared_attr
    def objects(cls):
        object_association = Table(
            "%s_objects" % cls.__tablename__,
            cls.metadata,
            Column("object_id", ForeignKey("object.id"),
                                primary_key=True),
            Column("%s_id" % cls.__tablename__,
                                ForeignKey("%s.id" % cls.__tablename__),
                                primary_key=True),
        )
        return relationship(Object, secondary=object_association)

        # The following line doesn't works :
        #
        #   return relationship(Object, secondary=object_association, backref="parent")
        #
        # Error :
        #
        # sqlalchemy.exc.ArgumentError: Error creating backref 'parent' on
        # relationship 'ObjectContainer.objects': property of that name exists on mapper
        # 'Mapper|Object|object'

class ObjectContainer(HasObjects, Base):
    name = Column(String)

class Hands(HasObjects, Base):
    name = Column(String)

engine = create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine)

session = Session(engine)

session.add_all([
    ObjectContainer(
        name='Chest 1',
        objects=[
            Object(name="potion 1"),
            Object(name="potion 2")
        ]
    ),

    Hands(
        name="Hands player 1",
        objects=[
            Object(name="potion 3"),
            Object(name="potion 4")
        ]
    ),
])

session.commit()

I have two questions:

1. How could I have a parent attribute in Object linked to ObjectContainer or Hands? I tried with backref but it doesn’t seems to work (see comments in the code)
2. How could I avoid the fact that Hands, with this HasObjects mixin, automatically get a list of objects? I only need to have the parent attribute of Object linked to Hands but I don’t need to have any list of the objects in Hands. Of course, I could ignore that and let the list be created but it’s a bit dirty

I assume that the answers are pretty simple but I think my comprehension of the “secondary” parameter of relationship is not good enough to find a solution.

Thank you!

Sven

[Sven]

Hello,

has sometone an answer or an idea which I can study ?

I also tried the table_per_related example but It will not works since my "Object" instances have to be able to be stored by hands and by ObjectContainers and to be able to go from Hands to ObjectContainers (and vice versa). Indeed, a player can get objects from a chest and hold them in Hands. I can't store Hands.Object into ObjectContainer.objects which would be only supposed to contain ObjectContainer.Object instances...

Thank you very much.

Sven

[Sven]

Hello,

Any idea regarding my problems ?

Thank you !

Sven

[Mike Bayer]

I think noone is responding to this because the request is extremely
vague. I have no idea what "stored by hands" means nor what a Hands
/ ObjectContainers is. To get help with an issue you need to share
extremely specific information including the table schemas you are
using (as SQLAlchemy declarative models is fine), how you expect them
to work, and how they are not working now.

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Sven]

Hi Mike,

Ok, let's forget everything I said before, it is too confusing.

I propose to start from the table_per_association example:

http://docs.sqlalchemy.org/en/latest/_modules/examples/generic_associations/table_per_association.html

Would it be possible to have an attribute address.parent linked to Customer/Supplier? Exactly like in the table_per_related example?

for customer in session.query(Customer):
    for address in customer.addresses:
        print(address)
        print(address.parent) # this attribute is what I need

Thank you!

[Simon King]

You could build this, but it's going to be messy. At the SQL level, if
you look at an Address row, there's not enough information to know
which association table to look in. You'd have to query all of the
association tables (perhaps using a UNION) to find the one that
contains the parent, and then query the appropriate parent table.

Simon

[Mike Bayer]

the discriminator_on_association example
http://docs.sqlalchemy.org/en/latest/_modules/examples/generic_associations/discriminator_on_association.html
illustrates how to have a .parent link.