[sqlalchemy] Joining queries with column concatenation.

[Filip Zyzniewski - Tefnet]

Hi,

another day and another challenge :).

Somehow SQLAlchemy has a problem determining what to join when using
concatenation of columns.

this: session.query(Locality.name, Street.name).join(Street.locality)
properly joins:
FROM street JOIN locality ON street."localityId" = locality."Id" 

but this: session.query(Locality.name + ' ' +
Street.name).join(Street.locality)

does: FROM street, locality JOIN locality ON street."localityId" =
locality."Id"

The second one can be fixed with joining on the backref of
Stret.locality,
but maybe SQLAlchemy could work it out on its own?

This is the code (copy at http://ideone.com/wWvT5 ):
================================================
# Fails with Python-2.7.0 and SQLAlchemy-0.7.1

import sqlalchemy
import sqlalchemy.ext.declarative
import pprint

Base = sqlalchemy.ext.declarative.declarative_base(mapper =
sqlalchemy.orm.mapper)


engine = sqlalchemy.create_engine('sqlite:///:memory:', echo = True)

session = sqlalchemy.orm.scoped_session(
    sqlalchemy.orm.sessionmaker(
        bind = engine,
        autocommit = False,
    )
)

Base.metadata.bind = engine


class Locality(Base):
    Id = sqlalchemy.Column( sqlalchemy.types.Integer, primary_key =
True, autoincrement = True)
    __tablename__ = 'locality'
    name = sqlalchemy.Column( sqlalchemy.types.String(128), nullable =
False)

class Street(Base):
    Id = sqlalchemy.Column(sqlalchemy.types.Integer, primary_key = True)
    __tablename__ = 'street'
    name = sqlalchemy.Column( sqlalchemy.types.String(128), nullable =
False)
    localityId = sqlalchemy.Column(sqlalchemy.types.Integer,
sqlalchemy.ForeignKey(Locality.Id))
    locality = sqlalchemy.orm.relation(Locality, primaryjoin =
localityId == Locality.Id)
    
Base.metadata.create_all()

sp = Locality(name = 'Southpark')
sv = Locality(name = 'Smallville')

session.add(sp)
session.add(sv)

session.add(Street(name = 'sp1', locality = sp))
session.add(Street(name = 'sp2', locality = sp))

session.add(Street(name = 'sv1', locality = sv))
session.add(Street(name = 'sv2', locality = sv))

#q = session.query(Street.name + ' ' + Locality.name)
# the above works

#q = session.query(Locality.name, Street.name)
# the above works

q = session.query(Locality.name + ' ' + Street.name)
# the above causes:
# sqlite: sqlalchemy.exc.OperationalError: (OperationalError) ambiguous
column name: locality.name u'SELECT locality.name || ? || street.name AS
anon_1 \nFROM street, locality JOIN locality ON street."localityId" =
locality."Id" \nWHERE locality.name = ?' (', ', 'Southpark')
# postgresql: sqlalchemy.exc.ProgrammingError: (ProgrammingError) table
name "locality" specified more than once 'SELECT locality.name ||
%(name_1)s || street.name AS anon_1 \nFROM street, locality JOIN
locality ON street."localityId" = locality."Id" \nWHERE locality.name =
%(name_2)s' {'name_2': 'Southpark', 'name_1': ' '}


q = q.join(
    Street.locality
).filter(
    Locality.name == 'Southpark'
)

pprint.pprint(
    q.all(),
    indent = 2
)
================================================

regards,
Filip Zyzniewski
Tefnet

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.