Those kind of more complex joins I would do as a property on the Department
class, a property which uses a Query() and returns the results, and not try
to put everything into a relation.
If relations become too complex, they can fail to be useful as insert
mechanisms.
It's better to think of relations as lower-level building blocks to be
re-used in Query() constructs, not as the only available fetch mechanism.
...and I wouldn't worry too much about the performance of an association
table. Assuming it's indexed properly, the performance hit will be
negligible.
Another option would be to consider "view only" relations (see the mapper
documentation)
Yet another option would be to prepare two relations, one each for your two
specialty FK, and construct a UNION query using both relations.
All in all, I think the simple many-to-many on the relation plus building up
more complex queries in your mapped object is the most straightforward way
to go.
Rick
2008/4/4 Michał Sulik <[EMAIL PROTECTED]>:
>
> Thanks for being engrossed and for fast reply :-)
>
> My real problem is more complex and my previous post was only a simple
> example. I just need to go into some details about working with SA in
> my master's thesis about ORMs and automatic generation of many-to-many
> relations.
>
> My child table will have exactly two (not more) foreign keys to parent
> table, and making "many-to-many" relation with association table
> between them may reduce performance.
>
>
> Interesting thing is, that this construction works very nice with
> secondaryjoin, but I understand, that this is for something completely
> different ;)
>
> -- Let's assume that we have some more tables:
> create table departments(id int primary key, name varchar(255));
> alter table employees add column department_id int references
> departments;
> insert into departments (id,name) values(301,'Management');
> insert into departments (id,name) values(302,'Public relations');
> insert into departments (id,name) values(303,'Developing');
> update employees set department_id = 303 where id = 201;
> update employees set department_id = 303 where id = 202;
> update employees set department_id = 301 where id = 203;
>
> And now Python:
>
> table_employees.append_column(sa.Column('department_id',
> pg.PGInteger(), sa.ForeignKey('departments.id')),)
>
> class Department(object):
> def __repr__(self): return self.name
>
> # Here, employees will act as an association table
> # between departments and specialities:
> orm.mapper(Department, table_departments, properties={
> "all_specialities":orm.relation(Speciality,
> secondary=table_employees,
> secondaryjoin=sa.or_(
> table_employees.c.first_speciality_id ==
> table_specialities.c.id,
> table_employees.c.second_speciality_id==
> table_specialities.c.id
> )
> ),
> })
>
> # We want to know, what specialists work in each department:
> for dept in session.query(Department):
> print dept,
> # This operates as expected:
> print dept.all_specialities
>
>
> Thanks for attention :)
> Michał.
> >
>
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
