Re: [SQLObject] Problem with InheritableSQLObject and ForeignKeys/MultipleJoin

[Juan M. S. <vic...@gm...>]

On Friday 29 January 2010 08:53:26 Oleg Broytman wrote:
> On Thu, Jan 28, 2010 at 09:35:50PM -0300, Juan Manuel Santos wrote:
> > class File(InheritableSQLObject):
> >     name = StringCol()
> >     root = ForeignKey("Directory", default=None)
> >
> > class Directory(File):
> >     dirs = MultipleJoin("Directory", joinColumn="root_id")
> >     files = MultipleJoin("File", joinColumn="root_id")
> >
> > sqlobject.dberrors.OperationalError: no such column: root_id
> >
> >  1/QueryR  :  SELECT id FROM directory WHERE root_id = (1)
> 
>    Table 'directory' doesn't have root_id, only 'file' as it.
> 
>    The problem is that InheritableSQLObject is rather limited thing. These
> limitations are documented:
> http://sqlobject.org/Inheritance.html#limitations-and-notes
>    The problem you are stumbled upon is "Inheritance works in two stages -
> first it draws the IDs from the parent table and then it draws the rows
> from the children tables. The first stage could fail if you try to do
> complex things. For example, Children.select(orderBy=Children.q.column,
> distinct=True) could fail because at the first stage inheritance generates
> a SELECT query for the parent table with ORDER BY the column from the
> children table."
>    Your code choked on the first stage while trying to SELECT IDs from the
> child table using a column that only exists in the parent.
> 
>    You can try simple Python inheritance instead:
> 
> class File(SQLObject):
>     name = StringCol()
>     root = ForeignKey("Directory", default=None)
> 
> class Directory(File):
>     dirs = MultipleJoin("Directory", joinColumn="root_id")
>     files = MultipleJoin("File", joinColumn="root_id")
> 
>    This works by copying all parent columns to the child table.
> 
> Oleg.
> 

Yes, that's what I ended up doing, after I thought it might be a limitation. 
But well, it works fine for the rest of the classes.

Thanks :)

Juan Manuel