On Jun 28, 2012, at 6:38 AM, tonthon wrote:
> Hi,
>
> I've got a problem understanding how to handle relationships with
> polymorphism
> Sorry for the db design (I took it overs as it is ).
>
> I've got a parent class Task
>
> class Task(DBBASE):
> __tablename__ = 'task'
> id = Column(Integer, primary_key=True)
> amount = Column(Integer)
>
> and two child classes
>
> class Invoice(DBBASE):
> __tablename__ = 'invoice'
> id = Column(Integer, ForeignKey('task.id'), primary_key=True)
> date = Column(Date)
> project_id = Column(Integer, ForeignKey('project.id'))
> project = relationship("Project", backref=backref('invoices',
> order_by='Invoice.date'))
> specific_attr1 = Column(Integer)
>
> class Esimation(DBBASE):
> __tablename__ = 'estimation'
> id = Column(Integer, ForeignKey('task.id'), primary_key=True)
> date = Column(Date)
> project_id = Column(Integer, ForeignKey('project.id'))
> project = relationship("Project", backref=backref('estimations',
> order_by='Invoice.date'))
> specific_attr2 = Column(Integer)
> specific_attr3 = Column(Integer)
>
> I'd like to query something like:
>
> dbession.query(Task).with_polymorphic([Invoice,
> Estimation]).join(Invoice.project).join(Estimation.project).join(Project.client)
>
> I actually get an error :
> (1066, "Not unique table/alias: 'project' ")
OK you need here to think about SQL. Task, Invoice, Estimation are all
different tables. A particular row in Task will either have an Invoice, or
Estimation row - but never both. So with_polymorphic() implies an outer join
from Task to both of these tables.
Once you're outer joining, anything that you join to from those joins, also
needs to be an outer join else you'll get no rows back when the parent is NULL
- so we are talking about Invoice.project as well as Estimation.project, and
also each of those to Project.client - unfortunately you'll have to also join
to client twice if you really need all of these columns present. The "not
unique" error refers to the fact that a particular name can only be mentioned
as a FROM target once in SQL.
So you'll need to alias out those targets, like this:
p1 = aliased(Project)
p2 = aliased(Project)
then use outerjoins and distinct joins to client:
query(Task).with_polymorphic([Invoice, Estimation]).outerjoin(p1,
Invoice.project).outerjoin(p2,
Estimation.Project).outerjoin(p1.client).outerjoin(p2.client)
the above query has a lot of outer joins and is generally not going to perform
very well. Depending on what you're ultimately trying to do, you may want to
join to a union of the estimation/invoice->project->client target, if you're
looking to have a simple path from Task to Client (that is,
query(Task).join(my_union, Task.id=my_union.c.id), and I'd create my_union
using Table/select() objects directly for "select client.* from
invoice_table->project->client UNION select client.* from
estimation_table->project_client". I'd experiment with SQL directly to see
what works best.
(also this is joined inheritance, not concrete, since Invoice/Estimation join
to Task with the "task.id" FK).
--
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.
