Error ordering by a translated attribute

[pmarti]

Hi there, I'm getting this traceback when ordering by a translated attribute:

ProgrammingError: (ProgrammingError) invalid reference to FROM-clause entry for table "foo_translation"
LINE 2: ...locale = 'en' ORDER BY foo_tra...
                                                             ^
                                                             HINT:  Perhaps you meant to reference the table alias "foo_translation_1".
                                                              'SELECT foo.id AS foo_id, foo.created_at AS foo_created_at, foo.updated_at AS foo_updated_at, foo_translation_1.id AS foo_translation_1_id, foo_translation_1.locale AS foo_translation_1_locale, foo_translation_1.name AS foo_translation_1_name, foo_translation_1.slug AS foo_translation_1_slug, foo_translation_1.introduction AS foo_translation_1_introduction, foo_translation_2.id AS foo_translation_2_id, foo_translation_2.locale AS foo_translation_2_locale, foo_translation_2.name AS foo_translation_2_name, foo_translation_2.slug AS foo_translation_2_slug, foo_translation_2.introduction AS foo_translation_2_introduction \nFROM foo LEFT OUTER JOIN foo_translation AS foo_translation_1 ON foo.id = foo_translation_1.id AND foo_translation_1.locale = %(current_locale)s LEFT OUTER JOIN foo_translation AS foo_translation_2 ON foo.id = foo_translation_2.id AND foo_translation_2.locale = %(locale_1)s ORDER BY foo_translation.name \n LIMIT %(param_1)s OFFSET %(param_2)s' {'param_1': 25, 'current_locale': 'en', 'locale_1': 'en', 'param_2': 0}

I've edited it to get to the meaty stuff. The error is clear, when ordering it's trying to order by the table name rather than the JOIN alias. My sqalchemy-internals-fu can't cut it. Would you mind having a look @kvesteri ?

Thanks!

[kvesteri]

Could you paste the relevant SQLAlchemy query construct here also?

[pmarti]

Thanks for the fast reply @kvesteri

I'm using flask-sqlalchemy btw. it's a dynamic query, debugging the test case gives something like:

options = [
    joinedload(model.current_translation),
    joinedload(model.fallback_translation),
]
order_by = [getattr(model, 'name')]
model.query.options(*options).order_by(*order_by)

There's a bit of pagination going on as well with Flask-SQLAlchemy's Paginate class but is not relevant

[kvesteri]

This is clearly wrong and you can't use order by like that. Joinedload uses aliases where as the argument passed to order by does not.

It should be something like this:

current_translation = db.aliased(model.current_translation)
fallback_translation = db.aliased(model.fallback_translation)

query = (
    model.query
    .join(current_translation)
    .join(fallback_translation)
    .options(
        db.contains_eager(model.current_translation, alias=current_translation)
    )
    .options(
        db.contains_eager(model.fallback_translation, alias=fallback_translation)
    )
    .order_by(current_translation.name)
)

Note:
Query like this will be quite slow with many entries because even if models translation's name is an indexed column PostgreSQL can't use this index when fetching the models.

[pmarti]

@kvesteri thanks for the reply and code, I've learned a couple of things along the way about sqlalchemy. I'll close this for now and will submit when I can a pull request with improved docs around ordering