Actually you can't use with_polymorphic() in the query because Meeting is not an inherited object (one would get an InvalidRequestError if one tried). But plugging:
with_polymorphic='*' in the mapper for Person makes the eagerload work in the code above. However, we're off on a tangent. I still don't know how to instantiate objects of a class mapped against two tables when they contain both an autogenerated primary key from the first table and a mandatory foreign key from the second... On Sep 14, 4:31 pm, "Michael Bayer" <[email protected]> wrote: > bojanb wrote: > > > The root of the problem is inheritance. Let's say that I have a Person > > class and an Employee class that inherits from it. I also have a > > Meeting class that records meetings between two persons. > > > A query on Meeting will always lazy load Employee's attributes, > > regardless of any lazy/eagerload settings. E.g. if I want to print the > > list of names of all persons somebody had meetings with and also their > > position if they are employees (null if they're not), it will always > > be done lazily. This is bad when I have, let's say, 100.000 Meetings. > > > I guess I can build a custom join and work from that, but if I have > > two levels of inheritance on one side and three levels on the other > > side, I will have to write a six-way join, and this, I'm sure you'll > > agree, sort of defeats the purpose of an object-relational mapper. > > > Using classes mapped against multiple tables would elegantly solve > > this problem, if I could only instantiate them (see my original post). > > > Here's the code that shows attributes of inherited objects are loaded > > lazily: > > oh. you want with_polymorphic() for this. > > http://www.sqlalchemy.org/docs/05/mappers.html#controlling-which-tabl... > > > > > <code> > > > from sqlalchemy import create_engine, Table, Column, Integer, String, > > MetaData, ForeignKey > > from sqlalchemy.orm import mapper, relation, sessionmaker, eagerload > > from sqlalchemy.orm.mapper import validates > > > DB_URI='postgres://postg...@localhost/postgres' #Replace this > > accordingly > > db_engine=create_engine(DB_URI, echo=False) > > metadata = MetaData() > > > class Person(object): > > > def __init__(self, name): > > self.name = name > > > persons = Table('persons', > > metadata, > > Column('id', Integer, primary_key=True), > > Column('type', String(1), nullable=False), > > Column('name', String(100), nullable=False)) > > > class Employee(Person): > > > def __init__(self, name, position): > > Person.__init__(self, name) > > self.position = position > > > employees = Table('employees', > > metadata, > > Column('id', Integer, ForeignKey('persons.id'), > > primary_key=True), > > Column('position', String(50), nullable=False)) > > > class Meeting(object): > > > def __init__(self, date, person_from, person_to): > > self.date = date > > self.person_from = person_from > > self.person_to = person_to > > > meetings = Table('meetings', > > metadata, > > Column('id', Integer, primary_key=True), > > Column('date', String(8), nullable=False), > > Column('person_from_id', Integer, ForeignKey > > ('persons.id'), nullable=False), > > Column('person_to_id', Integer, ForeignKey > > ('persons.id'), nullable=False)) > > > mapper(Person, persons, polymorphic_on=persons.c.type, > > polymorphic_identity='P') > > mapper(Employee, employees, inherits=Person, polymorphic_identity='E') > > mapper(Meeting, meetings, properties={ > > 'person_from': relation(Person, primaryjoin= > > (meetings.c.person_from_id==persons.c.id)), > > 'person_to': relation(Person, primaryjoin= > > (meetings.c.person_to_id==persons.c.id)), > > }) > > > if __name__ == '__main__': > > metadata.create_all(db_engine) > > s=sessionmaker(bind=db_engine)() > > try: > > john = Person('John') > > peter = Employee('Peter', 'clerk') > > jack = Employee('Jack', 'manager') > > m1 = Meeting('20090914', peter, john) > > m2 = Meeting('20090915', peter, jack) > > s.add_all([john, peter, jack, m1, m2]) > > s.commit() > > > db_engine.echo = True > > #We now want to print the names and positions of everyone > > Peter has ever met with > > peters_meetings = s.query(Meeting).options(eagerload > > ('person_to')).filter_by(person_from=peter).all() > > for meeting in peters_meetings: > > if meeting.person_to.type == 'P': > > print meeting.date, meeting.person_to.name, None > > else: > > #Each print statement here will emit an SQL SELECT on > > the employees table > > print meeting.date, meeting.person_to.name, > > meeting.person_to.position > > > finally: > > db_engine.echo = False > > s.close() > > metadata.drop_all(db_engine) > > > </code> > > > On Sep 11, 7:52 pm, "Michael Bayer" <[email protected]> wrote: > >> have you tried using query + join() + contains_eager() ? any query you > >> like can be used to build the object graph of your choice along > >> relations(). > > >> bojanbwrote: > > >> > Here's something I've been struggling with recently. I'll include the > >> > description of steps that got me here, as I believe the context will > >> > make the question clearer. > > >> > It all started because I needed to show data (eg. in a list form) from > >> > two related tables (classes). However, SQLAlchemy would emit one SQL > >> > query for getting the objects of the first class, then one query each > >> > for each access to attributes of the other class. It obviously loads > >> > the attributes lazily, which is fine most of the time but grossly > >> > inefficient in this case (as there can be thousands of records in the > >> > first table). > > >> > "Aha, I'll use eagerload!" I thought. Alas, it doesn't seem to work > >> > for inherited classes. A message in this group suggests that it > >> > doesn't work for self-referential inherited classes, but in this case > >> > it didn't work for plain inherited classes that don't contain > >> > references to self. I'll write a test case that shows this later. > > >> > OK, I then decided to create a new class mapped against the two > >> > tables, using join() in a mapper. This worked great regarding the > >> > emitted SQL - session.query on new object generates the correct SQL > >> > even with the (deep) inheritance tree that we have. Modifying the > >> > attributes on of this object also writes them to correct respective > >> > tables on commit. Great! The new class even conceptually fits nicely > >> > with the rest of the application (e.g. I realized it's more of a > >> > business object while the two underlying classes/tables are more of an > >> > implementation detail; I'm not sure I even need the other two classes, > >> > just the tables may be enough). Fantastic! > > >> > However, I can't figure how to create new instances of this (composite > >> > as I call it) class. Since it contains fields both for autogenerated > >> > primary key from the first class and foreign key from the second > >> > class, I cannot set the foreign key because I don't know the primary > >> > key before I commit, and commit fails because the foreign key is still > >> > null. > > >> > Am I just missing something or am I attempting a no-no? I would think > >> > that since I've defined the attributes to be identical in the > >> > "oncluase", SQLAlchemy would now that the two are dependent and would > >> > not complain about the null value. > > >> > Of course, I can always create the two other objects, but being able > >> > to do it this way fits much more nicely with the rest of the > >> > application. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected] 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 -~----------~----~----~----~------~----~------~--~---
