Hi, I want to use mysqls `SQL_CALC_FOUND_ROWS` but when I use `query.prefix_with(...)` it fails when the query eager loads a relationship because sqlalchemy puts the prefix not at the beginning.
I'm not sure if I should file a bug report or if it's intended behaviour. If I'm doing something wrong, how can I prefix the query only once in the beginning? Here is a simple script to demonstrate the error: https://gist.github.com/dakra/0424086f5837d722bc58 --- cut --- from sqlalchemy import create_engine, Column, Integer, String, ForeignKey from sqlalchemy.orm import Session, relationship, subqueryload, joinedload from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() e = create_engine("mysql+mysqlconnector://scott:tiger@localhost/test", echo=True) class Address(Base): __tablename__ = 'addresses' id = Column(Integer, primary_key=True) email_address = Column(String(64)) user_id = Column(Integer, ForeignKey('users.id')) class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String(64)) addresses = relationship(Address, backref="user") Base.metadata.drop_all(e) Base.metadata.create_all(e) s = Session(e) u = User(name='test') s.add_all([u, Address(email_address='email1', user=u), Address(email_address='email2', user=u)]) s.commit() # this works like expected users = s.query(User).prefix_with('SQL_CALC_FOUND_ROWS').all() row_count = s.execute('SELECT FOUND_ROWS()').scalar() print(users, row_count) # with eager loading (subqueryload or joinedload) it fails users = s.query(User).prefix_with('SQL_CALC_FOUND_ROWS').options(subqueryload(User.addresses)).all() row_count = s.execute('SELECT FOUND_ROWS()').scalar() print(users, row_count) --- cut --- If I execute, the relevant error message is: sqlalchemy.exc.ProgrammingError: (mysql.connector.errors.ProgrammingError) 1234 (42000): Incorrect usage/placement of 'SQL_CALC_FOUND_ROWS' [SQL: 'SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id, anon_1.users_id AS anon_1_users_id \nFROM (SELECT SQL_CALC_FOUND_ROWS users.id AS users_id \nFROM users) AS anon_1 INNER JOIN addresses ON anon_1.users_id = addresses.user_id ORDER BY anon_1.users_id'] Thanks, Daniel -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
