Holy crap, thank you so much!
I really can't thank you enough for this. As I said, I've never used the
joined inheritance before, and honestly? Can't believe it's as simple/good
as that.
Only weirdness I'm seeing now is in the init, if I do a db.session.commit()
just after `db.session.add(ssport)`, an odd null Sport gets added. Full
output -
INFO:sqlalchemy.engine.base.Engine:INSERT INTO source ("key") VALUES (?)
INFO:sqlalchemy.engine.base.Engine:('main',)
INFO:sqlalchemy.engine.base.Engine:INSERT INTO sport (name) VALUES (?)
INFO:sqlalchemy.engine.base.Engine:('American Football',)
INFO:sqlalchemy.engine.base.Engine:INSERT INTO sport (name) VALUES (?)
INFO:sqlalchemy.engine.base.Engine:(None,)
INFO:sqlalchemy.engine.base.Engine:INSERT INTO source_sport (sport_id,
source_sport_id, source_id) VALUES (?, ?, ?)
INFO:sqlalchemy.engine.base.Engine:(2, 'sAF', 2)
>From the code,
db.session.add(Source(key='secondary'))
source = Source(key='main')
sport = Sport(name='American Football')
ssport = SourceSport(sport=sport, source=source,
source_sport_id='sAF')
comp = Competition(key='NFL', sport=sport)
scomp = SourceCompetition(
competition=comp, source=source, source_competition_id='sNFL')
db.session.add(ssport)
db.session.commit()
Not sure what's going on here at all?
On Monday, July 23, 2018 at 7:35:40 PM UTC+1, Mike Bayer wrote:
>
> Here's a simple joined inheritance model and you can see the
> relationships are quite simple:
>
>
> from sqlalchemy import *
> from sqlalchemy.orm import *
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.ext.declarative import declared_attr
>
> from sqlalchemy import Column, Integer, String, ForeignKey
>
>
> Model = declarative_base()
>
>
> class Source(Model):
> __tablename__ = 'source'
>
> id = Column(Integer, primary_key=True)
>
> key = Column(String(10), unique=True)
>
>
> class Sport(Model):
> __tablename__ = 'sport'
>
> id = Column(Integer, primary_key=True)
>
> name = Column(String(50), unique=True)
>
>
> class Competition(Model):
> __tablename__ = 'competition'
>
> id = Column(Integer, primary_key=True)
>
> key = Column(String(6), unique=True)
>
> sport_id = Column(Integer, ForeignKey(Sport.id))
> sport = relationship(Sport, foreign_keys=(sport_id,))
>
>
> # mixin
> class _Sourcer:
>
> @declared_attr
> def source_id(self):
> return Column(Integer, ForeignKey(Source.id),
> primary_key=True)
>
> @declared_attr
> def source(self):
> return relationship(Source, foreign_keys=(self.source_id,))
>
>
> class SourceSport(_Sourcer, Sport):
> __tablename__ = 'source_sport'
>
> sport_id = Column(Integer, ForeignKey(Sport.id),
> primary_key=True)
> competitions = relationship(
> Competition,
> primaryjoin=sport_id == foreign(Competition.sport_id),
> uselist=True)
>
> source_sport_id = Column(String(20))
>
> source_competitions = relationship(
> "SourceCompetition",
> back_populates="source_sport", innerjoin=True)
>
>
> class SourceCompetition(_Sourcer, Competition):
> __tablename__ = 'source_competition'
>
> competition_id = Column(Integer, ForeignKey(Competition.id),
> primary_key=True)
> source_competition_id = Column(String(20))
>
> sport = relationship(Sport, enable_typechecks=False)
>
> source_sport = relationship(
> "SourceSport", back_populates="source_competitions")
>
>
> session = Session()
>
> q1 = session.query(SourceSport).\
> join(SourceCompetition).filter(Competition.key == 'NBA')
>
> q2 = session.query(SourceSport).\
> join(SourceSport.source_competitions).filter(Competition.key == "NBA")
>
>
> q3 = session.query(SourceSport).join(SourceCompetition).options(
> contains_eager(SourceSport.source_competitions)
> ).filter(Competition.key == "NFL")
>
>
> print("------------------------------")
> print(q1)
> print("------------------------------")
> print(q2)
> print("------------------------------")
> print(q3)
>
> the first two queries come out the same. The third query, wasn't sure
> what you were trying to do with the joinedload() + filter so I turned
> those into contains_eager which seems to be what you mean to do, that
> also gives you the same query except you get more columns in the
> columns clause.
>
> however it means you deal with SourceCompetition and Competition as a
> single entity even though they correspond to two tables.
>
>
> On Mon, Jul 23, 2018 at 2:13 PM, seaders <[email protected] <javascript:>>
> wrote:
> > Hi Mike, thanks for replying. I've attached the full runner py file.
> >
> >> the relationship here names "Competiton" as a "secondary" table which
> > means it will be aliased (here as competition_1) and you cannot filter
> > on it in terms of the Competition entity
> >
> > Getcha, I wasn't aware of that.
> >
> >> If you want to make a
> > relationship from SourceSport to SourceCompetition that also uses
> > "Competition" and you also want to filter on Competition ...
> >
> > Preferably, if possible, I'd like to have all the options available to
> me
> > like with the query. So 1 time, it's filtering on `Competition.key`,
> > another, it's `SourceCompetition.source_competition_id`, basically like
> > you'd do with a manual SQL query. The JOIN between the tables
> > (relationship) should be unchanging, but if I want to do a filter on it,
> the
> > data from the JOIN'd tables would be available to me.
> >
> >> It looks mostly like SourceCompetition as a joined table
> > subclass of Competition would be the most natural
> >
> > I've not yet created a single subclass, so have no experience with them
> (If
> > I had a full handle on them, I might not have ended up here!).
> >
> > ----
> >
> > P.S. I've just realised I've mistakenly posted this in
> sqlalchemy-alembic,
> > instead of sqlalchemy. Should I delete, and repost there, or are we ok
> > here?
> >
> >
> > On Monday, July 23, 2018 at 5:30:53 PM UTC+1, seaders wrote:
> >>
> >> I've these 5 tables,
> >>
> >> Source
> >>
> >> Sport 1----* Competition
> >>
> >> ________________________________
> >>
> >> Sport 1----1 SourceSport
> >>
> >> Competition 1----1 SourceCompetition
> >>
> >> ________________________________
> >>
> >> Source 1----* SourceSport
> >>
> >> Source 1----* SourceCompetition
> >>
> >> ________________________________
> >>
> >>
> >>
> >> import logging
> >> from flask import Flask
> >> from flask_sqlalchemy import SQLAlchemy
> >> from sqlalchemy import Column, Integer, String, ForeignKey
> >> from sqlalchemy.ext.declarative import declared_attr
> >> from sqlalchemy.orm import relationship, foreign, joinedload, aliased
> >> from sqlalchemy.sql.elements import and_
> >> from typing import List, cast
> >>
> >> app = Flask(__name__)
> >> app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///./test.db'
> >> app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
> >> db = SQLAlchemy(app)
> >> Model = db.Model
> >>
> >>
> >> class Source(Model):
> >> id = Column(Integer, primary_key=True)
> >> key = Column(String(10), unique=True)
> >>
> >>
> >> class Sport(Model):
> >> id = Column(Integer, primary_key=True)
> >> name = Column(String(50), unique=True)
> >>
> >>
> >> class Competition(Model):
> >> id = Column(Integer, primary_key=True)
> >> key = Column(String(6), unique=True)
> >>
> >> sport_id = Column(Integer, ForeignKey(Sport.id))
> >> sport = relationship(Sport)
> >>
> >>
> >> class _Sourcer(Model):
> >> __abstract__ = True
> >>
> >> @declared_attr
> >> def source_id(self):
> >> return Column(Integer, ForeignKey(Source.id),
> >> primary_key=True)
> >>
> >> @declared_attr
> >> def source(self):
> >> return relationship(Source)
> >>
> >>
> >> class SourceSport(_Sourcer):
> >> sport_id = Column(Integer, ForeignKey(Sport.id),
> >> primary_key=True)
> >> sport = relationship(Sport)
> >> source_sport_id = Column(String(20))
> >>
> >>
> >> class SourceCompetition(_Sourcer):
> >> competition_id = Column(Integer, ForeignKey(Competition.id),
> >> primary_key=True)
> >> competition = relationship(Competition)
> >> source_competition_id = Column(String(20))
> >>
> >>
> >> def create_all():
> >> db.create_all()
> >>
> >> source = Source(key='main')
> >>
> >> sport = Sport(name='American Football')
> >> ssport = SourceSport(sport=sport, source=source,
> >> source_sport_id='sAF')
> >>
> >> comp = Competition(key='NFL', sport=sport)
> >> scomp = SourceCompetition(
> >> competition=comp, source=source, source_competition_id='sNFL')
> >>
> >> db.session.add(ssport)
> >> db.session.add(scomp)
> >>
> >> comp = Competition(key='NCAAF', sport=sport)
> >> scomp = SourceCompetition(
> >> competition=comp, source=source,
> source_competition_id='sNCAAF')
> >>
> >> db.session.add(scomp)
> >>
> >> sport = Sport(name='Basketball')
> >> ssport = SourceSport(sport=sport, source=source,
> >> source_sport_id='s2')
> >>
> >> comp = Competition(key='NBA', sport=sport)
> >> scomp = SourceCompetition(
> >> competition=comp, source=source, source_competition_id='sNBA')
> >>
> >> db.session.add(ssport)
> >> db.session.add(scomp)
> >>
> >> comp = Competition(key='NCAAB', sport=sport)
> >> scomp = SourceCompetition(
> >> competition=comp, source=source,
> source_competition_id='sNCAAB')
> >>
> >> db.session.add(scomp)
> >>
> >>
> >> And one of the queries I find myself doing often, is getting all the
> >> SourceCompetion's for SourceSport's.
> >>
> >> The "manual" query of this is fairly straight forward,
> >>
> >> SourceSport.query
> >>
> >> .join(Sport)
> >> .join(Competition)
> >> .join(SourceCompetition,
> >> ((SourceCompetition.source_id == SourceSport.source_id) &
> >> (SourceCompetition.competition_id == Competition.id))
> >> )
> >>
> >>
> >> Every relationship is set up correctly to join easily enough, until
> >> SourceCompetition, and then you've to specify the onclause, but again,
> >> because everything's set up ok, it's easy and straightforward enough.
> >>
> >> I can also easily add filters/options to that,
> >>
> >> ...
> >> .contains_eager(SourceSport.sport)
> >> .filter(Competition.key == 'NFL')
> >>
> >> ...
> >> .filter(Sport.name == 'Basketball')
> >>
> >> Example output of the first one,
> >>
> >> INFO:sqlalchemy.engine.base.Engine:SELECT sport.id AS sport_id,
> sport.name
> >> AS sport_name, 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
> >> competition ON sport.id = competition.sport_id JOIN source_competition
> ON
> >> source_competition.source_id = source_sport.source_id AND
> >> source_competition.competition_id = competition.id
> >> WHERE competition."key" = ?
> >> INFO:sqlalchemy.engine.base.Engine:('NBA',)
> >>
> >> {'source_id': 1, 'sport_id': 2, 'source_sport_id': 's2', 'sport':
> {'name':
> >> 'Basketball', 'id': 2}}
> >>
> >> Perfect, now all I want to do, is have the exact same "base" join in a
> >> relationship, in SourceSport. I have a version working,
> >>
> >> class SourceSport(_Sourcer):
> >>
> >> ...
> >>
> >> source_competitions: List['SourceCompetition'] = relationship(
> >> lambda: SourceCompetition,
> >> primaryjoin=lambda: and_(
> >> SourceSport.sport_id == foreign(Competition.sport_id),
> >> ),
> >> secondary=Competition.__table__,
> >> secondaryjoin=lambda: and_(
> >> SourceSport.source_id == foreign(SourceCompetition.source_id),
> >> Competition.id == SourceCompetition.competition_id,
> >> ),
> >> innerjoin=True,
> >> uselist=True)
> >>
> >>
> >> Now, this works correctly (although tbh, I think the complexity of it
> >> seems too high for what I'm trying to achieve), so I can do queries
> like,
> >>
> >> SourceSport.query
> >> .options(joinedload(SourceSport.source_competitions))
> >>
> >> ...
> >>
> >> SourceSport.query
> >> .join(SourceCompetition, SourceSport.source_competitions)
> >>
> >>
> >> But the problems come when I try to filter those queries with something
> >> simple like above,
> >>
> >> ...
> >>
> >> .filter(Competition.key == 'NFL')
> >>
> >>
> >> Now, when I look at the logs,
> >>
> >> INFO:sqlalchemy.engine.base.Engine: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, source_competition_1.competition_id AS
> >> source_competition_1_competition_id,
> >> source_competition_1.source_competition_id AS
> >> source_competition_1_source_competition_id,
> source_competition_1.source_id
> >> AS source_competition_1_source_id
> >> FROM competition, source_sport JOIN competition AS competition_1 ON
> >> source_sport.sport_id = competition_1.sport_id JOIN source_competition
> AS
> >> source_competition_1 ON source_sport.source_id =
> >> source_competition_1.source_id AND competition_1.id =
> >> source_competition_1.competition_id
> >> WHERE competition."key" = ?
> >> INFO:sqlalchemy.engine.base.Engine:('NFL',)
> >>
> >> {'source_id': 1, 'sport_id': 1, 'source_sport_id': 'sAF',
> >> 'source_competitions': [{'source_competition_id': 'sNFL', 'source_id':
> 1,
> >> 'competition_id': 1}, {'source_competition_id': 'sNCAAF', 'source_id':
> 1,
> >> 'competition_id': 2}]}
> >>
> >> {'source_id': 1, 'sport_id': 2, 'source_sport_id': 's2', 'sport':
> {'name':
> >> 'Basketball', 'id': 2}, 'source_competitions':
> [{'source_competition_id':
> >> 'sNBA', 'source_id': 1, 'competition_id': 3}, {'source_competition_id':
> >> 'sNCAAB', 'source_id': 1, 'competition_id': 4}]}
> >>
> >> You can see that the JOIN for Competition is using "competition as
> >> competition_1", but the WHERE is just using "competition" from the FROM
> >> table list. This then gives a weird mish-mash of a returned object -
> not at
> >> all correct.
> >>
> >> I think I've tried just about everything, including a standalone join
> and
> >> mapper, but that seemed to be completely wasted work and right back to
> where
> >> I started here, I've read the docs, and stepped through a ton of the
> source,
> >> but still can't figure what to do. I feel like I'm just throwing
> spaghetti
> >> at the wall now, and trying to see will anything stick, and would
> really
> >> appreciate some help to figure this out and get it working.
> >>
> >> Many, many thanks.
> >
> > --
> > You received this message because you are subscribed to the Google
> Groups
> > "sqlalchemy-alembic" group.
> > To unsubscribe from this group and stop receiving emails from it, send
> an
> > email to [email protected] <javascript:>.
> > For more options, visit https://groups.google.com/d/optout.
>
--
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.
