CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
Chet Meinzer
Chet Meinzer

Reputation: 1741

join tables in sqlalchemy to get a many relationship in a query for a flask app

How do i query all of the manys-- 2 tables away from my groupby variable?

My data is structured like this (even though my database allows many-to-many everywhere)

my data is like a bowtie

syntax \......................................../ Whenz
syntax --Clump--Clumpdetail -- Whenz
syntax /..........................................\ Whenz

For each syntax.filename I want all of the Whenz.freq

my model

clump_syntaxs = db.Table('clump_syntaxs',
    db.Column('syntax_id', db.Integer, db.ForeignKey('syntax.id')),
    db.Column('clump_id', db.Integer, db.ForeignKey('clump.id')),
)

clump_deets = db.Table('clump_deets',
    db.Column('clumpdetail_id', db.Integer, db.ForeignKey('clumpdetail.id')),
    db.Column('clump_id', db.Integer, db.ForeignKey('clump.id')),
)
when_deets = db.Table('when_deets',
    db.Column('clumpdetail_id', db.Integer, db.ForeignKey('clumpdetail.id')),
    db.Column('whenz_id', db.Integer, db.ForeignKey('whenz.id')),
)
class Whenz(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(150))
    freq = db.Column(db.String(150))
    frequency = db.relationship('Clumpdetail', secondary=when_deets,
        backref=db.backref('frequency', lazy='dynamic'))
    def __repr__(self):
        return  str(self.title)

class Clumpdetail(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    whenhour = db.Column(db.Time())
    wherevar = db.Column(db.String(150))
    cat = db.Column(db.String(150))
    clumps = db.relationship('Clump', secondary=clump_deets,
        backref=db.backref('deets', lazy='dynamic'))
    def __repr__(self):
        return str(self.cat)

class Clump(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    clumpname = db.Column(db.String(150), unique=True)
    ordervar = db.Column(db.Integer)
    syntaxs = db.relationship('Syntax', secondary=clump_syntaxs,
        backref=db.backref('clumps', lazy='dynamic'),order_by="Syntax.position",
                            collection_class=ordering_list('position'))
    def __repr__(self):
        return   str(self.clumpname)

class Syntax(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    filename = db.Column(db.String(150), unique=True,)
    jobs = db.relationship('Jobs',lazy='dynamic', backref='jobhistory')
    position = db.Column(db.Integer)
    def __repr__(self):
        return str(self.filename)
 .... cut.

my attempt of many

joblist=db.session.query((models.Syntax.filename).label('filename'),\
    (models.Clump.clumpname).label('clumpname'),\
    (models.Clumpdetail.whenhour).label('hour'),\
    (models.Clumpdetail.wherevar).label('where'),\
    (models.Clumpdetail.cat).label('cat'),\
    (models.Whenz.freq).label('freq'))\
.outerjoin((models.Syntax.clumps,models.Clump))\
.outerjoin((models.Clump.deets,models.Clumpdetail))\
.outerjoin((models.Clumpdetail.frequency,models.Whenz))\
.group_by(models.Syntax.filename).all()

I would expect this line

    (models.Whenz.freq).label('freq'))\

to give me all of the whenz, but it only gives me one. Why is that?

I am able to get what i want using

models.Syntax.query.filter_by(filename=models.Syntax.query.first()\
.filename).first()\
.clumps.first()\
.deets.first()\
.frequency.all()

but this is just one by one, and i want it by syntax.filename

Thank you

Upvotes: 0

Views: 246

Answers (1)

univerio
univerio

Reputation: 20508

This is because of the group_by. If you group by syntax.filename, you'll only have one row for each unique filename, so you won't get all of the freqs back.

If you want a SQLAlchemy core solution, remove the group_by and group the results yourself in python.

If you want a SQLAlchemy ORM solution, you can use subqueryload:

Syntax.query.options(subqueryload(Syntax.clumps).subqueryload(Clump.deets).subqueryload(Clumpdetail.frequency)).all()

Note: I believe you'll have to remove the lazy="dynamic" on Syntax.clumps, Clump.deets, and Clumpdetail.frequency.

Upvotes: 2

Related Questions