bojanb wrote: > > Yes, I want to map to a join between two classes which are parts of > joined table inheritance. I don't think it's complex - it fits very > naturally with the problem I am modeling. > > When I said it's efficient, I meant that the generated SQL is optimal, > ie. the same as I would write if I were doing it by hand. "eagerload" > and "with_polymorphic" result in SQL that also queries on fields of > sibling classes (ie. which inherit from the same superclass but are > not in the inheritance path of the final class) which are unnecessary, > and contains a subquery (which I believe is not optimal).
with_polymorphic can be set against any subset of classes, not just '*'. > > My understanding is that performing joins on indexed fields is what > RDBMS do well. However, if the query turned out to be too slow I can > always switch to single-table inheritance - whether I use joined-table > or single-table inheritance is just an implementation detail (as I > understand it). oh that query is going to be pretty slow for sure (though "slow" is a relative term). > > The problem is not that sequence is not firing off, it's that it's > firing for a sequence that doesn't exist. In the code above, it's > trying to get the next value from "supervisor_relations_id" sequence, > but that sequence doesn't exist because of inheritance. It should be > trying to get from "relations_id_sequence" but for some reason it > isn't. If you run the code you can see what's going on exactly in the > SQL echo. I don't have the time most of today to get into it so I can't confirm what's going on. Any chance you could map to a straight join of all four tables instead of a join to two sub-joins ? > > I will play around with MapperExtension and single-table inheritance > and see what I get. However, I just thought that since selects and > updates work so nicely in this setup, create should also work in the > same way. > > On Sep 15, 4:32 pm, "Michael Bayer" <[email protected]> wrote: >> bojanb wrote: >> >> > The problem is when I have an object mapped against two tables, both >> > of which are part of an inheritance hierarchy. I managed to >> > synchronize the foreign key with the primary key (per the >> > documentation link you provided). However, SQLAlchemy doesn't (or I >> > can't instruct it how to) set the polymorphic discrimintaor fields >> > appropriately. I can set them manually, but then insert fails because >> > it looks for the sequence object on the inherited table, which doesn't >> > exist (it exist only on the root table of the inheritance hierarchy). >> >> > Here's example code. In brief, I have a Person->Employee and Relation- >> >>SupervisorRelation as two independent inheritance hierarchies. >> > Relation is defined between two Persons, and SupervisorRelation >> > between two Employees. I want to hide this as an implementation and >> > have a Subordinate class that the programmer down the line will work >> > with. Subordinate contains fields from Employee and >> > SupervisorRelation. Querying on Subordinate works (efficiently, too), >> > and so does attribute modification. I would like to be able to create >> > it also (after populating the required fields and commit, the >> > underlying engine should create both a new Employee and a new >> > SupervisorRelation). >> >> let me get this straight. you want to map to a JOIN, which itself is >> JOINed against two joined-table inheritance subclasses. >> >> That is >> >> +------- join ------+ >> | | >> join join >> >> and each call to Query() would emit a JOIN against two sub-JOINs. >> >> is this correct ? is there a reason this need be so complex ? (and its >> efficient ? really ? a query like that would bring any DB to a halt on >> a >> large dataset, I would think...) >> >> If the issue is just a sequence not firing off, an immediate workaround >> would be to fire the sequence off yourself. you can even do this in a >> MapperExtension.before_insert() (good place for your polymorphic >> identity >> setting too). I don't as yet understand why the normal sequence firing >> wouldn't be working here, is one firing off and the other not ? >> >> >> >> > I hope this makes sense. Here's the code. When run, it throws >> > "ProgrammingError: (ProgrammingError) relation >> > "supervisor_relations_id_seq" does not exist" >> >> > <code> >> >> > from sqlalchemy import create_engine, Table, Column, Integer, String, >> > MetaData, ForeignKey >> > from sqlalchemy.orm import mapper, relation, sessionmaker, eagerload, >> > join >> > 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 Relation(object): >> >> > def __init__(self, person_from, person_to): >> > self.person_from = person_from >> > self.person_to = person_to >> >> > relations = Table('relations', >> > metadata, >> > Column('id', Integer, primary_key=True), >> > Column('type', String(1), nullable=False), >> > Column('person_from_id', Integer, ForeignKey >> > ('persons.id'), nullable=False), >> > Column('person_to_id', Integer, ForeignKey >> > ('persons.id'), nullable=False)) >> >> > class SupervisorRelation(Relation): >> > def __init__(self, person_from, person_to, additional_info): >> > Relation.__ini__(person_from, person_to) >> > self.additional_info = additional_info >> >> > supervisor_relations = Table('supervisor_relations', >> > metadata, >> > Column('id', Integer, ForeignKey >> > ('relations.id'), primary_key=True), >> > Column('additional_info', String(100), >> > nullable=False)) >> >> > class Subordinate(object): #This class represents the business object >> > that we work with >> > pass >> >> > mapper(Person, persons, polymorphic_on=persons.c.type, >> > polymorphic_identity='P') >> > mapper(Employee, employees, inherits=Person, polymorphic_identity='E') >> > mapper(Relation, relations, polymorphic_on=relations.c.type, >> > polymorphic_identity='R', properties={ >> > 'person_from': relation(Person, primaryjoin= >> > (relations.c.person_from_id==persons.c.id)), >> > 'person_to': relation(Person, primaryjoin= >> > (relations.c.person_to_id==persons.c.id)), >> > }) >> > mapper(SupervisorRelation, supervisor_relations, inherits=Relation, >> > polymorphic_identity='S') >> > mapper(Subordinate, join(Employee, SupervisorRelation, >> > onclause=SupervisorRelation.person_from_id==Employee.id), properties={ >> > 'relation_id': supervisor_relations.c.id, #Need to rename as >> > there's also Employee.id >> > 'relation_type': relations.c.type, #Also need to rename >> > 'person_from_id': [relations.c.person_from_id, persons.c.id], >> > #Need to declare them synonymous >> > 'person_from': relation(Person, primaryjoin= >> > (relations.c.person_from_id==persons.c.id)), >> > 'person_to': relation(Person, primaryjoin= >> > (relations.c.person_to_id==persons.c.id)), >> > }) >> >> > if __name__ == '__main__': >> > metadata.create_all(db_engine) >> > s=sessionmaker(bind=db_engine)() >> > try: >> > jack = Employee('Jack', 'manager') >> > s.add(jack) >> > s.commit() >> > #Here we try to create a Subordinate object which should >> > automatically create dependant objects >> > db_engine.echo = True >> > subordinate = Subordinate() >> > subordinate.person_to = jack >> > subordinate.name = 'Peter' >> > subordinate.position = 'clerk' >> > subordinate.additional_info = 'Works for Jack since 2007' >> > subordinate.type = 'E' >> > subordinate.relation_type='S' >> > s.add(subordinate) >> > s.commit() #Fails >> >> > finally: >> > db_engine.echo = False >> > s.close() >> > metadata.drop_all(db_engine) >> >> > </code> >> >> > Here's the code that demonstrates the problem from my original >> > question (the previous code was in response to your reply which >> >> > On Sep 14, 6:52 pm, "Michael Bayer" <[email protected]> wrote: >> >> bojanbwrote: >> >> >> > 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, >> >> athttp://www.sqlalchemy.org/docs/05/mappers.html#mapping-a-class-agains... >> >> . 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, >> >> ... >> >> read more » > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
