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]> 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].
> 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.
