SQLAlchemy - Eager loading with primary join relationship

Question

I'm trying to eager load a relationship that has a primaryjoin condition and it keeps telling me Unknown column 'tags.type' in 'on clause' . I believe it is because the table name is hard coded into the join, but the query is trying to use an AS name for the table. Full example below:

#!/usr/bin/env python
# -*- coding: utf-8 -*-

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

def create_app():
    app = Flask(__name__)
    app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://test:test@localhost/JoinTest'
    db.init_app(app)
    return app

class PostToTags(db.Model):
    __tablename__ ='post_to_tags'
    post_id = db.Column(db.Integer, db.ForeignKey('posts.id'), primary_key=True)
    tag_id = db.Column(db.Integer, db.ForeignKey('tags.id'), primary_key=True)

class Tag(db.Model):
    __tablename__ = 'tags'
    id = db.Column(db.Integer, primary_key=True)
    key = db.Column(db.Unicode(1024), nullable=False)
    value = db.Column(db.Unicode(1024))
    type = db.Column(db.Enum('post'), default=None)

class Post(db.Model):
    __tablename__ = 'posts'

    id = db.Column(db.Integer, primary_key=True)

    tags = db.relationship(
        "Tag",
        primaryjoin="and_(Post.id == PostToTags.post_id, Tag.type=='post')",
        secondary='post_to_tags',
    )

if __name__ == "__main__":
    app = create_app()
    with app.app_context():
        db.drop_all()
        db.create_all()
        tag = Tag(key='foo',value='bar', type='post')
        post = Post()
        post.tags.append(tag)
        db.session.add_all((post, tag))
        db.session.commit()

        q = db.session.query(Post).options(db.joinedload('tags'))

        print q
        print q.all()

The actual query it emits looks like this:

SELECT posts.id AS posts_id, tags_1.id AS tags_1_id, tags_1.`key` AS tags_1_key, tags_1.value AS tags_1_value, tags_1.type AS tags_1_type 
FROM posts LEFT OUTER JOIN (post_to_tags AS post_to_tags_1 INNER JOIN tags AS tags_1 ON tags_1.id = post_to_tags_1.tag_id) ON posts.id = post_to_tags_1.post_id AND tags.type = %(type_1)s

Notice that before the last ON statement it names the tag table to tags_1, but then the last reference to the table tries to access the attribute via tags.type

Whats the best way to rethink this?

Thanks in advance!

Answer 1

You have almost the same problem as in the post you linked, but instead of simply removing the secondary join from the primary and letting SQLAlchemy figure it out you need to define the secondary instead of the primary – and let SQLA figure the primary out for you:

tags = db.relationship(
    "Tag",
    secondaryjoin="and_(Tag.id == PostToTags.tag_id, Tag.type == 'post')",
    secondary='post_to_tags',
)