Explicit Column Combination vs Implicit?

[Michael P. McDonnell]

Hey team - 

I've went through the docs, and likely because I'm a bit of a hack - I'm finding it hard to find my answers (because I'm not sure what I'm specifically looking to "do" other than "make it work")

So I have the following situation:
I have a GAME table (with a corresponding LANGUAGE table) - because my users might play games.
game_language_table = Table(
    'game_language',
    Base.metadata,
    Column('game_id', UUID(as_uuid=True), ForeignKey('game.id'), primary_key=True, nullable=False),
    Column('language_id', String(length=255), ForeignKey('language.id'), primary_key=True, nullable=False),
    Column('name', String(length=255), nullable=False, unique=True))

game_table = Table(
    'game',
    Base.metadata,
    Column('id', UUID(as_uuid=True), primary_key=True, nullable=False, default=sqlalchemy.text('uuid_generate_v4()'), server_default=sqlalchemy.text('uuid_generate_v4()')),
    Column('join_code', String(length=8), nullable=False, unique=True))

I also have a ROUND table which is effectively just an extension of a game, but with a sequence number. Basically - if I have a collection of games that I want users to play - they play them as "Rounds" in a given order.
round_table = Table(
    'round',
    Base.metadata,
    Column('id', UUID(as_uuid=True), primary_key=True, nullable=False, default=sqlalchemy.text('uuid_generate_v4()'), server_default=sqlalchemy.text('uuid_generate_v4()')),
    Column('tournament_id', UUID(as_uuid=True), ForeignKey('tournament.id'), nullable=False),
    Column('game_id', UUID(as_uuid=True), ForeignKey('game.id'), nullable=False),
    Column('sequence', Integer, nullable=False, default=0),
    UniqueConstraint('tournament_id', 'sequence'))

So my GAME table has an "id" field, and my round table has a game_id field, and an id field.

When I try to join(game_table, round_table) - I get the following error:
Implicitly combining column round.id with column game.id under attribute 'id'.  Please configure one or more attributes for these same-named columns explicitly.

So - because there's multiple points of entry into these tables: Editing a Game is a direct thing, editing a round (which also edits its underlying game) is also a direct thing. 

How do I craft the JOIN so I don't step on toes?

I tried doing the following:
round_join = join(round_table, game_join.alias('game'))
That didn't help, because game_id still squashes between game_language and round.

Is there a smarter way to go about this?  

[Mike Bayer]

join() does not emit that error; that error occurs when you create a mapping to the join using either mapper() or a declarative base, since "attribute" refers to ORM mapped attribute.    Mapping to joins with same-named columns is demonstrated at https://docs.sqlalchemy.org/en/13/orm/nonstandard_mappings.html#mapping-a-class-against-multiple-tables .

So - because there's multiple points of entry into these tables: Editing a Game is a direct thing, editing a round (which also edits its underlying game) is also a direct thing. 

How do I craft the JOIN so I don't step on toes?

I tried doing the following:

round_join = join(round_table, game_join.alias('game'))

That didn't help, because game_id still squashes between game_language and round.

Is there a smarter way to go about this?  

--

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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAHmCLHq8euMKns8i%2BGseAQJE%2Bb3d491%2B8Bk_yMkp3dC3Ae5T%2Bg%40mail.gmail.com.

[Michael P. McDonnell]

Okay - I gotcha! 

That makes perfect sense too. It also means that my (eventual) work around worked:
class Round(Base):
    __table__ = round_join
    game_id = column_property(
        round_table.c.game_id, game_table.c.id, game_language_table.c.game_id)

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/3cf2863a-1d22-4687-be69-1a5cebf3d08c%40www.fastmail.com.