Yeah, implementing this a bit more, I'm fairly sure inheritance isn't what
I want for my use. I want the intelligent / easy linkage between the two
sets of tables - Sport and SourceSport, and Competition and
SourceCompetition - but none of the rest of what inheritance gives.
After pulling out the inheritance stuff, I think the closest I can get to
what I want is "source_competitions" set up like,
class SourceSport(_Sourcer):
source_competitions = relationship(
lambda: SourceCompetition,
primaryjoin=lambda: and_(
foreign(SourceSport.source_id) == SourceCompetition.source_id,
Competition.id == SourceCompetition.competition_id),
back_populates="source_sport",
uselist=True
)
Without a secondary/secondaryjoin. Without those, a "straight" query fails,
SourceSport.query
.join(SourceSport.source_competitions)
> no such column: competition.id [SQL: 'SELECT source_sport.sport_id AS
source_sport_sport_id, source_sport.source_sport_id AS
source_sport_source_sport_id, source_sport.source_id AS
source_sport_source_id \nFROM source_sport JOIN source_competition ON
source_sport.source_id = source_competition.source_id AND competition.id =
source_competition.competition_id
But if I join Competition,
SourceSport.query
.join(SourceSport.source_competitions)
.join(Competition)
SELECT source_sport.sport_id AS source_sport_sport_id,
source_sport.source_sport_id AS source_sport_source_sport_id,
source_sport.source_id AS source_sport_source_id
FROM source_sport JOIN source_competition ON source_sport.source_id =
source_competition.source_id AND competition.id =
source_competition.competition_id JOIN competition ON competition.id =
source_competition.competition_id
The query is perfect.
If I use anything from a table *not* joined, we hit the previous problem,
about that table being added to the FROM list,
SourceSport.query
.join(SourceSport.source_competitions)
.join(Competition)
.filter(
Source.key == 'main',
Sport.name == 'American Football',
Competition.key == 'NFL')
SELECT source_sport.sport_id AS source_sport_sport_id,
source_sport.source_sport_id AS source_sport_source_sport_id,
source_sport.source_id AS source_sport_source_id
FROM source, sport, source_sport JOIN source_competition ON
source_sport.source_id = source_competition.source_id AND competition.id =
source_competition.competition_id JOIN competition ON competition.id =
source_competition.competition_id
WHERE source."key" = ? AND sport.name = ? AND competition."key" = ?
INFO:sqlalchemy.engine.base.Engine:('main', 'American Football', 'NFL')
But if I ensure to remember to join any table I want to filter on, or any
table I want to have filled when it's returned from sqlalchemy, everything
works right,
SourceSport.query
.join(Sport)
.join(Source)
.join(SourceSport.source_competitions)
.join(Competition)
.options(
contains_eager(SourceSport.source_competitions)
.contains_eager(SourceCompetition.competition)
.contains_eager(Competition.sport),
contains_eager(SourceSport.source))
.filter(
Source.key == 'main',
Sport.name == 'American Football',
Competition.key == 'NFL')
SELECT sport.id AS sport_id, sport.name AS sport_name, competition.id AS
competition_id, competition."key" AS competition_key, competition.sport_id AS
competition_sport_id, source_competition.competition_id AS
source_competition_competition_id, source_competition.source_competition_id AS
source_competition_source_competition_id, source_competition.source_id AS
source_competition_source_id, source.id AS source_id, source."key" AS
source_key, source_sport.sport_id AS source_sport_sport_id,
source_sport.source_sport_id AS source_sport_source_sport_id,
source_sport.source_id AS source_sport_source_id
FROM source_sport JOIN sport ON sport.id = source_sport.sport_id JOIN source ON
source.id = source_sport.source_id JOIN source_competition ON
source_sport.source_id = source_competition.source_id AND competition.id =
source_competition.competition_id JOIN competition ON competition.id =
source_competition.competition_id
WHERE source."key" = ? AND sport.name = ? AND competition."key" = ?
INFO:sqlalchemy.engine.base.Engine:('main', 'American Football', 'NFL')
I *still* think that seems unnecessary given you've set all that connection
up in the relationship, but c'est la vie, I've spent way too much time on
this now.
On Tuesday, July 24, 2018 at 3:04:07 AM UTC+1, seaders wrote:
>
> Actually I see why that's happening now. Because of the inheritance (I'm
> assuming), sqlalchemy is creating a Sport record as a "parent" of every
> SourceSport created, and it's also creating a Competition record as a
> parent of every SourceCompetition created.
>
> Just looking back at my original post, in case I've caused confusion, my
> description of the class connection was wrong, for both Sport and
> Competition, and SourceSport and SourceCompetition respectively, they're
> 1-to-many relationships, not 1-to-1 as I'd indicated,
>
> Sport 1----* SourceSport
>
> Competition 1----* SourceCompetition
>
> The class structure was fine, but yeah Sport "has many" SourceSport's, and
> Competition has many SourceCompetition's. (Same as Source has many
> SourceSports, and also has many SourceCompetitions).
>
> Is this the default behaviour with this inheritance setup? I can't find
> any clear description of this in the documentation -
> http://docs.sqlalchemy.org/en/latest/orm/inheritance.html#joined-table-inheritance
>
> Obviously this is completely unwanted, and incorrect for my setup. The
> "parent" Sport for a SourceSport isn't a new item in the dB, it's the sport
> that's already created and referred to by SourceSport.sport.
>
--
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 [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.
