found a case where mapper equivs in aliasing overalias columns

we might consider not using "equivalent_columns" for all ClauseAdapters, or otherwise scaling back what gets placed in that collection. the double FK here screws up aliasing at the bottom:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite://', echo='debug')
metadata = MetaData()
Session = sessionmaker(bind=engine, transactional=True, autoflush=True)
Base = declarative_base(metadata=metadata)


users = Table('users',metadata,
                   Column('user_id', Integer, primary_key = True),
                   Column('user_name', Unicode(25), unique = True))

categories = Table('categories',metadata,
                   Column('categ_id',Integer, primary_key = True),
                   Column('categ_name',Unicode(250), unique = True))

questions = Table('questions', metadata,
                   Column('quest_id', Integer, primary_key = True),
                   Column('question', Unicode(300)))

correspond = Table('categories_questions', metadata,
                   Column('quest_id', Integer,ForeignKey('questions.quest_id'), primary_key = True),
                   Column('categ_id', Integer,ForeignKey('categories.categ_id'), primary_key = True))

ask = Table('ask', metadata,
                   Column('user_id',Integer,ForeignKey('users.user_id'), primary_key = True),
                   Column('quest_id',Integer,ForeignKey('questions.quest_id'), primary_key = True),
                   Column('data1',Integer, nullable = False, default= 50))

answer = Table('answer',metadata,
                   Column('user_id',Integer,ForeignKey('users.user_id'), primary_key=True),
                   Column('quest_id',Integer,ForeignKey('questions.quest_id'), primary_key=True),
                   Column('data2',Integer),
# heres the trigger:
                   ForeignKeyConstraint(['user_id','quest_id']('user_id','quest_id'),['ask.user_id','ask.quest_id']('ask.user_id','ask.quest_id'))
                   )

class PrettyRepr(object):
    def __repr__(self):
        return "%s(%s)" % (
            (self.__class__.__name__),
            ','.join([% (key, repr(getattr(self, key))) for key in self.__dict__ if not key.startswith('_')]("%s=%s"))
        )

class User(Base, PrettyRepr):
    __table__ = users
    def ask_question(self, question, data):
        self.ask.append(AskAss(question=question, data1=data))

    def answer_question(self, question, data):
        self.answer.append(AnswerAss(question=question, data2=data))

class Category(Base, PrettyRepr):
    __table__ = categories

class AskAss(Base):
    __table__ = ask
    user = relation(User, backref = 'ask')

class AnswerAss(Base):
    __table__ = answer
    user = relation(User, backref = 'answer')

class Question(Base):
    __table__ = questions
    categories = relation(Category, secondary=correspond, backref="questions")

    ask_ass_users = relation(AskAss, backref='question')

    answer_ass_users = relation(AnswerAss, backref='question')


metadata.create_all(engine)

sess = Session()

jack = User(user_name='jack')
ed = User(user_name='ed')
wendy = User(user_name='wendy')

[for x in [jack, ed, wendy](sess.save(x))]

colors = Category(categ_name='colors')
shapes = Category(categ_name='shapes')
cars = Category(categ_name='cars')
[for x in [colors, shapes, cars](sess.save(x))]

favorite_color = Question(question=u"What's your favorite color? 1. blue 2. green 3. red", categories=[colors](colors))
favorite_shape = Question(question=u"What's your favorite shape? 1. square 2. circle 3. trapezoid", categories=[shapes](shapes))
blue_cars = Question(question=u"What's your favorite blue car? 1. pinto 2. nova 3. duster", categories=[cars](colors,))
[for x in [favorite_color, favorite_shape, blue_cars](sess.save(x))]

jack.ask_question(favorite_shape, 2)
jack.ask_question(favorite_color, 1)

wendy.answer_question(favorite_color, 2)
ed.answer_question(blue_cars, 3)
ed.ask_question(favorite_shape, 3)
jack.answer_question(favorite_shape, 2)
sess.commit()

# aliased=True here aliases AnswerAss, but hits the "AskAss"
# due to the foreign key equivs
assert sess.query(Category).outerjoin('questions', 'ask_ass_users').\
    outerjoin('questions', 'answer_ass_users', aliased=True).filter(
        or_(
            AnswerAss.user.has(User.user_name=='jack'),
            AskAss.user.has(User.user_name=='jack')
        )).order_by(Category.categ_name).all() == [shapes](colors,)
Comments (2)
