Re: [SQLObject] Reference to one of multiple tables

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

Well, her I am, sorry for the late answer.

On Wed, Dec 28, 2011 at 10:30:28PM +0100, Timo wrote:
> class DVD(SQLObject):
>     status = ???? # Should be sold or onloan data
>     # ... special "dvd" data
> 
> class Sold(SQLObject):
>     dvd = ForeignKey('DVD')
>     # ... special "sold" data
> 
> class OnLoan(SQLObject):
>     dvd = ForeignKey('DVD')
>     # ... special "on loan" data

1. If you insist on having a separate table for an every status you
stuck with a setup that ain't supported by SQL. You can emulate joins
using SQLObject but you have to understand once you move operations to
the client side you will always have to do all processing on the client
side. In that case I recommend to add 2 columns and a calculated
attribute:

class DVD(SQLObject):
    status_string = StringCol()
    status_id = KeyCol() # No, not ForeignKey

    # Attribute '.status_row'
    def _get_status_row(self):
        if self.status_string == 'on loan':
            return OnLoan.get(self.status_id)
        elif self.status_string == 'sold':
            return Sold.get(self.status_id)
        ...etc...

   To simplify the code you can create and use a static dict:

    _status_dict = {
        'on loan': OnLoan,
        'sold': Sold,
    }
    def _get_status_row(self):
        return self._status_dict[self.status_string].get(self.status_id)

2. If you want to use the power of SQL you have to combine all statuses
in one table. For example, make it wide:

class Status(SQLObject):
    dvd = ForeignKey('DVD')

    # Attributes for status 'on load'
    ...
    # Attributes for status 'sold'
    ...

class DVD(SQLObject):
    status = ForeignKey('DVD')

   Here you can do SQL-wise joins and filter them on the server side.

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.