Re: [SQLObject] Having trouble with one to many joins

[Andy T. <an...@ha...>]

Luke Opperman wrote:
[snip]
> 
> Quoting Andy Todd <an...@ha...>:
> 
> 
>>Hi,
>>
>>This is probably a stupid question, but I couldn't find anything in the
>>mailing list archives.
>>
>>I've got two tables in a parent-child relationship and I thought I had
>>specified the joins correctly but I am getting no joy.
>>
>>Table 1 (the parent) is called PORTFOLIOS, here is my class definition;
>>
>>class Portfolio(SQLObject):
>>     _table='portfolios'
>>     _columns=[Col('portfolioCode'),
>>               Col('portfolioDesc'),
>>              ]
>>     _joins=[MultipleJoin('Holding'),
>>            ]
>>
>>The child table is called HOLDINGS, here is its class definition;
>>
>>class Holding(SQLObject):
>>     _table='holdings'
>>     _columns=[Col('portfolioID', foreignKey='Portfolio'),
>>               Col('stockID', foreignKey='Stock'),
>>               Col('buyDate'),
>>               Col('buyQuantity'),
>>               Col('buyPrice'),
>>               Col('currentQuantity'),
>>              ]
>>
>>The tables are already populated and I can fetch rows from either table
>>successfully, either with index values, select or selectBy methods. But
>>the join is a bit shonky, my Holding objects contain a reference to the
>>correct portfolio object and a portfolioID attribute, but the Portfolio
>>objects do not contain any references to holdings.
>>
>>I suspect the problem lies in my choice of names. Because the table is
>>called portfolios, the class Portfolio and the foreign key column
>>portfolio_id I believe the foreign key is being traversed correctly. Can
>>anyone suggest what I have done wrong and, more importantly, what I can
>>do to get this working properly.
>>
>>Thanks in advance,
>>Andy
>>--
>>
> 
> --------------------------------------------------------------------------------
> 
>> From the desk of Andrew J Todd esq - http://www.halfcooked.com/
>>
>>
>>

Thank you Luke and Edmund,

By following your advice I found my problem and have solved it. By 
turning debug on I discovered that the link from Portfolio to Holding 
was expecting a column called portfolios_id.

I fixed this by changing the _joins attribute of my Portfolio class to;

     _joins=[MultipleJoin('Holding', joinColumn='portfolio_id')]

Which sorted out the SQL that is generated. I had, incorrectly, assumed 
that because the class was called portfolio that was being used to 
generate the foreign key column name in the child table, but it was 
being overriden by my explicit use of _table.

Regards,
Andy
-- 
--------------------------------------------------------------------------------
 From the desk of Andrew J Todd esq - http://www.halfcooked.com/