bojanb wrote: > > 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...
Just to clarify, the mapper on Employee with the "with_polymorphic='*'" *is* a mapper that is mapped against two tables, in pretty much the same way as a map against a plain join is represented. So I'm assuming this is unsuitable only because it's your observation that the joined tables in your particular system are more of an "implementation detail" and you don't really need to represent inheritance. So, as far as synchronizing the foreign key with the primary key of the two tables in a mapper that is mapped to a plain join, you just map two columns to one attribute. This is also in the docs, at http://www.sqlalchemy.org/docs/05/mappers.html#mapping-a-class-against-multiple-tables . The tables are populated in order of foreign key dependency, and after each primary key generation the value is synchronized to the mapped attribute, where its then available for the insert into the second table. Since I didn't read your initial (very long) email carefully enough, here is your sample program using that style. 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_engine=create_engine('sqlite://', echo=True) metadata = MetaData() persons = Table('persons', metadata, Column('id', Integer, primary_key=True), Column('name', String(100), nullable=False)) class Employee(object): def __init__(self, name, position): self.name = 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(Employee, employees.join(persons), properties={ 'id':[persons.c.id, employees.c.id] }) mapper(Meeting, meetings, properties={ 'person_from': relation(Employee, primaryjoin=(meetings.c.person_from_id==persons.c.id)), 'person_to': relation(Employee, primaryjoin=(meetings.c.person_to_id==persons.c.id)), }) if __name__ == '__main__': metadata.create_all(db_engine) s=sessionmaker(bind=db_engine)() john = Employee('John', 'person') 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() #We now want to print the names and positions of everyonePeter has ever met with peters_meetings = s.query(Meeting).options(eagerload('person_to')).filter_by(person_from=peter).all() for meeting in peters_meetings: print meeting.date, meeting.person_to.name,meeting.person_to.position > > On Sep 14, 4:31 pm, "Michael Bayer" <mike...@zzzcomputing.com> 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" <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 -~----------~----~----~----~------~----~------~--~---
