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-tables-are-queried > > <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" <mike...@zzzcomputing.com> 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 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 -~----------~----~----~----~------~----~------~--~---
