Le 28/06/2012 16:40, Michael Bayer a écrit :
> 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).
>
Thanks a lot, you made the things far clearer for me, I'll give it a try
and I'll give some feedback.
My goal is to query both estimations and invoices (and their related
objects) and being able to filter and order them, I'll see if I succeed.
Cheers,
Gaston
--
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.
