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). 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). 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 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 -~----------~----~----~----~------~----~------~--~---
