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

[Oleg B. <ph...@ph...>]

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.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.