if you run it with full blown logging on, i.e.: import logging logging.basicConfig() logging.getLogger('sqlalchemy.engine').setLevel(logging.DEBUG) logging.getLogger('sqlalchemy.orm').setLevel(logging.DEBUG)
the issue can be detected when you look at the mapper creating instance keys for "T" (although this is clearly not a novice issue): DEBUG:sqlalchemy.orm.mapper.Mapper:(T|ts) _instance(): identity key (<class '__main__.T'>, (1,), None) not in session[] DEBUG:sqlalchemy.orm.mapper.Mapper:(T|ts) _instance(): identity key (<class '__main__.T'>, (None,), None) not in session[] DEBUG:sqlalchemy.orm.mapper.Mapper:(T|ts) _instance(): identity key (<class '__main__.T'>, (3,), None) not in session[] DEBUG:sqlalchemy.orm.mapper.Mapper:(T|ts) _instance(): identity key (<class '__main__.T'>, (None,), None) not in session[] DEBUG:sqlalchemy.orm.mapper.Mapper:(T|ts) _instance(): identity key (<class '__main__.T'>, (5,), None) not in session[] so its not getting an identity key for every other row, which indicates its looking at the wrong column in the result set. (on each of those "None"s, its going to skip that entity) looking at the query: SELECT ts.id, ts.dat, other.ts_id, other.other_dat FROM ts LEFT OUTER JOIN other ON ts.id = other.ts_id we can see that "other" has a column called "ts_id", which looks exactly like the label that would be made for "id" in table "ts". so thats whats happening here. so throwing on a "use_labels=True" to the query (or changing the name of "ts_id") produces the query: SELECT ts.id AS ts_id, ts.dat AS ts_dat, other.ts_id AS other_ts_id, other.other_dat AS other_other_dat FROM ts LEFT OUTER JOIN other ON ts.id = other.ts_id that gives the correct results. not sure what SA can really do here to make this kind of issue easier to catch, since the resultproxy itself is where its looking for "col label, col name, ", etc. the generated labels are generally more accurate. i tried playing around with ResultProxy to make it detect an ambiguity of this nature, but i think it might not be possible unless more flags/switches get passed from the statement to the result (which id rather not do since it further marginalizes straight textual queries), since if the select statement uses table/col labels for each column, there still could be conflicts which dont matter, such as the column names the normal eager loader generates: 'ts_id', 'ts_dat', 'other_4966_ts_id', 'other_4966_other_dat', that result is from column "ts_id" attached to an Alias "other_4966". if we said "dont allow any Column to be found twice in the row", then that breaks (since it will match other_4966_ts_id on its _label, ts_id on its name). On Feb 27, 2007, at 12:09 PM, Dennis Muhlestein wrote: > from sqlalchemy import * > > > e=create_engine('sqlite://memory') > ts=Table('ts',e, > Column ( 'id',Integer,primary_key=True), > Column ( 'dat',Integer,nullable=False)) > ts.create() > > to_oneornone=Table('other',e, > Column ( 'ts_id', Integer,ForeignKey('ts.id'), primary_key=True, > nullable=False ), > Column ( 'other_dat', Integer, nullable=False ) ) > to_oneornone.create() > > class T(object): pass > T.mapper=mapper(T,ts) > > class To(object):pass > To.mapper=mapper(To,to_oneornone,properties={'ts':relation > (T,backref=backref('other',uselist=False))}) > > > s=create_session() > for x in range(10): > t=T() > t.dat=x > s.save(t) > > if x % 2 == 0: # test every other T has an optional data > o=To() > o.other_dat=x > t.other=o > > s.save(t) > s.flush() > > s.clear() > > somedata=s.query(T).options(eagerload('other')).select() > print 'Number results should be 10: ', len(somedata) > > s.clear() > > > sel=select([ts,to_oneornone], > from_obj=[ts.outerjoin(to_oneornone)]) > > print "Raw select also is 10: " , len(sel.execute().fetchall() ) > > > print "Instances should also be 10: ", len(s.query(T).options > (contains_eager('other')).instances(sel.execute())) --~--~---------~--~----~------------~-------~--~----~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---