forwarding to sqlalchemy
On Mon, Jul 23, 2018 at 2:13 PM, seaders <seader...@gmail.com> 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 sqlalchemy-alembic+unsubscr...@googlegroups.com.
> 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
