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:
<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
-~----------~----~----~----~------~----~------~--~---
