sqlobject-discuss

[SQLObject] How to access intermediate table columns?

[Tomas F. <fu...@ya...>]

Hi all,

i would like to ask you if could be possible to acces
intermediate table column in many to many
relationship.

For example:

CREATE TABLE person (
    id SERIAL,
    username VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE role (
    id SERIAL,
    name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE assigned_roles (
    person INT NOT NULL,
    role INT NOT NULL,
    assigned_date DATE 
);

I would like to access the assigned_date column. Does
anybody have any idea? In the docs there is following
solution, but without using any columns from
intermediate table.

class Person(SQLObject):
    username = StringCol(length=100, alternateID=True)
    roles = RelatedJoin('Role', joinColumn='person',
otherColumn='role', 
                       
intermediateTable='assigned_roles')
class Role(SQLObject):
    name = StringCol(length=50, alternateID=True)
    roles = RelatedJoin('Person', joinColumn='role',
otherColumn='person', 
                       
intermediateTable='assigned_roles')

Thanks for ideas

Tomas


 


		
__________________________________ 
Do you Yahoo!? 
Check out the new Yahoo! Front Page. 
www.yahoo.com 
 

Re: [SQLObject] How to access intermediate table columns?

[Ksenia M. <kse...@gm...>]

On Mon, 15 Nov 2004 05:11:48 -0800 (PST), Tomas Fulajtar
<fu...@ya...> wrote:
> Hi all,
> 
> i would like to ask you if could be possible to acces
> intermediate table column in many to many
> relationship.
> 
> For example:
> 
> CREATE TABLE person (
>     id SERIAL,
>     username VARCHAR(100) NOT NULL UNIQUE
> );
> 
> CREATE TABLE role (
>     id SERIAL,
>     name VARCHAR(50) NOT NULL UNIQUE
> );
> 
> CREATE TABLE assigned_roles (
>     person INT NOT NULL,
>     role INT NOT NULL,
>     assigned_date DATE
> );
> 
> I would like to access the assigned_date column. Does
> anybody have any idea? In the docs there is following
> solution, but without using any columns from
> intermediate table.
> 
> class Person(SQLObject):
>     username = StringCol(length=100, alternateID=True)
>     roles = RelatedJoin('Role', joinColumn='person',
> otherColumn='role',
> 
> intermediateTable='assigned_roles')
> class Role(SQLObject):
>     name = StringCol(length=50, alternateID=True)
>     roles = RelatedJoin('Person', joinColumn='role',
> otherColumn='person',
> 
> intermediateTable='assigned_roles')

I think you can define class for assigned_roles, just like for any
other SQLObject class:

class AssignedRoles:
    person = ForeignKey('Person')
    role = ForeignKey('Role')
    assignedDate = DateTimeCol()


-- 
Ksenia

Re: [SQLObject] How to access intermediate table columns?

[Tomas F. <fu...@ya...>]

--- Ksenia Marasanova <kse...@gm...>
wrote:

> On Mon, 15 Nov 2004 05:11:48 -0800 (PST), Tomas
> Fulajtar
> <fu...@ya...> wrote:
> > Hi all,
> > 
> > i would like to ask you if could be possible to
> acces
> > intermediate table column in many to many
> > relationship.
> > 
> > For example:
> > 
> > CREATE TABLE person (
> >     id SERIAL,
> >     username VARCHAR(100) NOT NULL UNIQUE
> > );
> > 
> > CREATE TABLE role (
> >     id SERIAL,
> >     name VARCHAR(50) NOT NULL UNIQUE
> > );
> > 
> > CREATE TABLE assigned_roles (
> >     person INT NOT NULL,
> >     role INT NOT NULL,
> >     assigned_date DATE
> > );
> > 
> > I would like to access the assigned_date column.
> Does
> > anybody have any idea? In the docs there is
> following
> > solution, but without using any columns from
> > intermediate table.
> > 
> > class Person(SQLObject):
> >     username = StringCol(length=100,
> alternateID=True)
> >     roles = RelatedJoin('Role',
> joinColumn='person',
> > otherColumn='role',
> > 
> > intermediateTable='assigned_roles')
> > class Role(SQLObject):
> >     name = StringCol(length=50, alternateID=True)
> >     roles = RelatedJoin('Person',
> joinColumn='role',
> > otherColumn='person',
> > 
> > intermediateTable='assigned_roles')
> 
> I think you can define class for assigned_roles,
> just like for any
> other SQLObject class:
> 
> class AssignedRoles:
>     person = ForeignKey('Person')
>     role = ForeignKey('Role')
>     assignedDate = DateTimeCol()
> 
> 
> -- 
> Ksenia
> 
> 

The problem here is that intermediate table has
usually no primary key - SQLObject self does not
create any primary key at creation table time.
Therefore you are not able to use this table as class.
 That is the reason why it is not possible to use two
One- to many joins as i tried. Or I am wrong?  I think
in general intermediate table SHOULD not have primary
key!   

Tomas


		
__________________________________ 
Do you Yahoo!? 
The all-new My Yahoo! - Get yours free! 
http://my.yahoo.com 
 

Re: [SQLObject] How to access intermediate table columns?

[Carlos R. <car...@gm...>]

On Tue, 16 Nov 2004 06:11:51 -0800 (PST), Tomas Fulajtar
<fu...@ya...> wrote:
> The problem here is that intermediate table has
> usually no primary key - SQLObject self does not
> create any primary key at creation table time.
> Therefore you are not able to use this table as class.
>  That is the reason why it is not possible to use two
> One- to many joins as i tried. Or I am wrong?  I think
> in general intermediate table SHOULD not have primary
> key!

As far as I know, the reason why its not usual to declare primary keys
for a intermediate table is that, in most situations, such table is
not used to store any data besides the keys needed for the
relationship itself. If you start putting data into it (as it is in
your case), then I assume that you should declare it like a normal
table; in practical terms, declaring a PK should not be a big deal.
You may lose some convenience, but you can model the data correctly.

(btw, I think that there is a way to declare that the PK of the
intermediate table is a combination of two fields. I don't know if it
will work if both fields are FKs though.)

-- 
Carlos Ribeiro
Consultoria em Projetos
blog: http://rascunhosrotos.blogspot.com
blog: http://pythonnotes.blogspot.com
mail: car...@gm...
mail: car...@ya...