1

i have a table with 2 foreign keys that map to the same primary key of another table. the problem i'm facing is that these two foreign keys could be independent values, however, they always get set to the same thing when using SQLAlchemy.

tables (shorthanded):

CREATE TABLE table1 (
  userid INT NOT NULL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  UNIQUE KEY(name)
);

CREATE TABLE table2 (
  taskid INT NOT NULL PRIMARY KEY,
  userid INT,
  ownerid INT,
  task VARCHAR(255) NOT NULL,
  FOREIGN KEY (userid) REFERENCES users (userid),
  FOREIGN KEY (ownerid) REFERENCES users (userid)
);

i'm using the classical mapper from sqlalchemy and my class definition is:

class User:
  def __init__( self, name ):
    self.name = name

class Task:
  def __init__( self, task, ownerid ):
    self.task     = task
    self.ownerid  = ownerid

the ownerid and userid could be different i.e. ownerid is the user who owns the task and userid is the user that created the task.

i've created mappings:

users_table = sqlalchemy.Table( 'users', self.metadata, autoload=True )
tasks_table = sqlalchemy.Table( 'tasks', self.metadata, autoload=True )

sqlalchemy.orm.mapper( User, users_table, properties= {
  'tasks': sqlalchemy.orm.relationship(Task) } )

sqlalchemy.orm.mapper( Task, tasks_table, properties {
  'user': sqlalchemy.orm.relationship( User, primaryjoin=tasks_table.c.userid==users_table.c.userid ),
  'owner': sqlalchemy.orm.relationship( User, primaryjoin=tasks_table.c.ownerid==users_table.c.userid ) } )

and the syntax for working with these objects is something akin to:

case 1:

u1 = User('burt')
t1 = Task( 'buy milk', u1.userid )  # this case is that the task is assigned to self

case 2:

u2 = User('kelly')
t2 = Task( 'review code', u1.userid )  # assign to burt, creator is kelly

in case 2, i'm having an issue here as the ownerid always equals the userid, in this case the ownerid and userid are 2 (for kelly) always.

user1198531
  • 31
  • 1
  • 4
  • You should format the code from your question. This way it's very hard for anyone to understand and it makes it less likely you will recieve answers. – Bogdan Feb 09 '12 at 15:40

3 Answers3

2

You have to use primaryjoin.

So your code will be change like

# Connected to owner of the record.
sqlalchemy.orm.mapper( User, users_table, properties= {
  'tasks': sqlalchemy.orm.relationship(Task, primaryjoin="Task.ownerid==User.userid") } )

# Similarly you can create relation ship with creater.

Note: When you have 2 foreign key with same table then you have to mention which relation is refer to which filed in the dependent table.

Might be this will solve your problem

Gringo Suave
  • 29,931
  • 6
  • 88
  • 75
Nilesh
  • 20,521
  • 16
  • 92
  • 148
  • forgot to mention that i had set the primaryjoin: `sqlalchemy.orm.mapper( Task, tasks_table, properties={ 'user': sqlalchemy.orm.relationship( User, primaryjoin= tasks_table.c.userid==users_table.c.userid ), 'owner': sqlalchemy.orm.relationship( User, primaryjoin= tasks_table.c.ownerid==users_table.c.userid ) } )` perhaps i should use the class variables instead of the tasks_table.c.userid will give it a try, thanks for the prompt response – user1198531 Feb 11 '12 at 11:28
  • 1
    Your problem is solve or not. If not then please edit the question and give same example which create problem. – Nilesh Feb 14 '12 at 06:32
1

I have a fix: I just added an owner member variable to Task:

class Task:

  owner  = None

  def __init__( Self, task ):
    self.task = task`

and then:

u1 = User('Burt')
u2 = User('Kelly')
t1 = Task('get newspaper')
u1.task.append(t1) # creator of the task
t1.owner = u2 # owner of the task
Pascal MARTIN
  • 395,085
  • 80
  • 655
  • 663
user1198531
  • 31
  • 1
  • 4
0

When you have more than one relationship to the same table, sqlalchemy need more information to find out how to build the join. You can use either primaryjoin or foreign_keys to achieve that. As Lafada mentioned, the Task relationship is missing this extra bit of information.

My version of your code does not display the problem you mentioned. Maybe you could check and see if this solves your problem?

from sqlalchemy import create_engine, MetaData, Table
from sqlalchemy.orm import relationship, mapper, clear_mappers
engine = create_engine('sqlite:///:memory:', echo=True)
conn = engine.connect()

# create tables manually so simulate question
conn.execute("""
CREATE TABLE users (
  userid INT NOT NULL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
)""")

conn.execute("""
CREATE TABLE tasks (
  taskid INT NOT NULL PRIMARY KEY,
  userid INT,
  ownerid INT,
  task VARCHAR(255) NOT NULL,
  FOREIGN KEY (userid) REFERENCES users (userid),
  FOREIGN KEY (ownerid) REFERENCES users (userid)
)""")

# create classes and mappings
class User:
    def __init__(self, name):
        self.name = name
    def __repr__(self):
        return self.name

class Task:
    def __init__(self, task, owner=None, user=None):
        self.task = task
        self.owner = owner
        self.user = user
    def __repr__(self):
        return self.task

metadata = MetaData(bind=engine)
users_table = Table( 'users', metadata, autoload=True )
tasks_table = Table( 'tasks', metadata, autoload=True )

clear_mappers()

mapper( User, users_table, properties= {
  'tasks': relationship(Task, primaryjoin=tasks_table.c.userid==users_table.c.userid  ) } )

mapper( Task, tasks_table, properties= {
  'user': relationship( User, primaryjoin=tasks_table.c.userid==users_table.c.userid ),
  'owner': relationship( User, primaryjoin=tasks_table.c.ownerid==users_table.c.userid ) } )

# test
u1 = User('burt')
t1 = Task( 'buy milk', u1, u1)
print('%s, user=%s, owner=%s' % (t1, t1.user, t1.owner))

u2 = User('kelly')
t2 = Task( 'review code', u1, u2)
print('%s, user=%s, owner=%s' % (t2, t2.user, t2.owner))
antoniobotelho
  • 313
  • 2
  • 7