CODEWITHSUNDEEP
pythonsqlalchemy
Cobble
Cobble

Reputation: 122

SQLAlchemy many to one many times in the same table?

I have the classes "Bot" and "Bit", every Bot is going to consist of multiple bits (right now just a left and right arm). I want to do this by having a reference to every Bit a Bot consists of in the table itself, so no association table needed since every bot will have the same number and type of bits.

However, I also want to be able to get all bots that have a certain Bit, and thought I could just back ref the bots to the Bit as a parent but I am then getting this error:

Could not determine join condition between parent/child tables on relationship Bit.bots - there are multiple foreign key paths linking the tables. Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

I tried adding the foreign_keys=[Bot.left_arm, Bot.right_arm] to the bots relationship but that just changed the error to:

Could not determine join condition between parent/child tables on relationship Bit.bots - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

Below are my current classes:

class Bot(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    level = db.Column(db.Integer, default=1)

    right_arm_id = db.Column(db.Integer, ForeignKey('bit.id'))
    right_arm = relationship('Bit', foreign_keys=[right_arm_id])

    left_arm_id = db.Column(db.Integer, ForeignKey('bit.id'))
    left_arm = relationship('Bit', foreign_keys=[left_arm_id])

    image_file = db.Column(db.String(30))



class Bit(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    part = db.Column(db.Integer)
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'))
    category = relationship('Category')
    name = db.Column(db.String(30))
    coordinate = db.Column(db.String(16))
    image_file = db.Column(db.String(30))
    bots = relationship("Bot", foreign_keys=[Bot.left_arm, Bot.right_arm])

Any ideas on how to fix it or a better way to do it is much appreciated!

Upvotes: 0

Views: 64

Answers (1)

van
van

Reputation: 76972

The bots = relationship("Bot", foreign_keys=[Bot.left_arm, Bot.right_arm]) will not work for several reasons:

  • Bot.left_arm and Bot.right_arm are not foreign keys, and
  • even if they were, the plural in foreign_keys is to cover the case of composite foreign keys and not multiple.

What you could do instead is to add the backrefs to your relationships from Bit to Bot:

class Bot(db.Model):
    # ...
    right_arm = relationship('Bit', foreign_keys=[right_arm_id], backref="bot_left")
    # ...
    left_arm = relationship('Bit', foreign_keys=[left_arm_id], backref="bot_right")
    # ...

class Bit(db.Model):
    # ...
    def bots(self):
        return [bot_left, bot_right]

However, you might consider using Hybrid attributes, and for this i would point you to another question: Why am I getting AmbiguousForeignKeysError? which is very similar to yours, and i think that the one of the solutions will work for you too.

Upvotes: 1

Related Questions