An ordered many-to-many relationship cannot be eagerly loaded

This example is an ''ordered many-to-many relationship''. That is, a contract has many associated projects, and vice versa. The order of the projects associated with a contract is set by the contract_project_ordinal column in the joining table, so that each contract can have projects in a different order.

However, trying to make the relationship on the contract load the projects ''eagerly'' causes this failure:

sqlalchemy.exceptions.InvalidRequestError: Given column 'contract_project.contract_project_ordinal', attached to table 'contract_project', failed to locate a corresponding column from table 'project_51e5'

The following is a working example. To cause the failure, uncomment the lazy=False in contract_mapper on line 44.

from sqlalchemy import *

import logging
logging.basicConfig()

uri = 'sqlite:///:memory:'

contract = Table('contract',
    Column('contract_id', Integer,
           Sequence('metadata_seq'), primary_key=True)
    )

project = Table('project',
    Column('project_id', Integer,
           Sequence('metadata_seq'), nullable=False, primary_key=True)
    )

contract_project = Table('contract_project',
    Column('contract_project_contract_id', Integer,
           ForeignKey(contract.c.contract_id),
           primary_key=True, autoincrement=False,
           nullable=False),
    Column('contract_project_project_id', Integer,
           ForeignKey(project.c.project_id),
           primary_key=True, autoincrement=False,
           nullable=False),
    Column('contract_project_ordinal', Integer,
           nullable=False),
    )

class Contract(object):
    def __init__(self, contract_comment):
        self.contract_comment = contract_comment

class Project(object): pass

class ContractProject(object): pass

project_mapper = mapper(Project, project)

contractproject_mapper = (ContractProject, contract_project)

contract_mapper = mapper(Contract, contract, properties = dict(
    projects = relation(Project, secondary = contract_project, #lazy = False,
                        order_by=contract_project.c.contract_project_ordinal)
))

global_connect(uri)
engine = default_metadata.engine
session = create_session()

default_metadata.create_all()
contract.insert().execute({'contract_id': 1})
project.insert().execute({'project_id': 1})
contract_project.insert().execute({'contract_project_contract_id': 1,
                                  'contract_project_project_id': 1,
                                  'contract_project_ordinal': 0})
project.insert().execute({'project_id': 2})
contract_project.insert().execute({'contract_project_contract_id': 1,
                                  'contract_project_project_id': 2,
                                  'contract_project_ordinal': 1})


logging.getLogger('sqlalchemy').setLevel(logging.INFO)

#
# where the action is
#

print '************* query ***********'
c = session.query(Contract).get_by(contract_id=1)

print '************ contract ***********'
print str(c)

print '************* projects ************'
print str(c.projects)

Comments (1)