ORM query many to many to one, Flask sql-alchemy

I have three flask-sqlalchemy-models. Books – unique entries by admin:

class Book(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    book_name = db.Column(db.String(20), unique=True, nullable=False)
    def __repr__(self):
        return self.book_name

Bookscomp – entries by users, related to above:

class Bookscomp(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    book = db.Column(db.Integer, db.ForeignKey('book.id'))

Company – user, related to above:

class Company(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(100), nullable=False)
    books = db.relationship('Bookscomp', secondary=companybook, lazy='dynamic',
        backref=db.backref('company'))

companybook = db.Table('companybook',
    db.Column('companyid', db.Integer, db.ForeignKey('company.id'), primary_key=True),
    db.Column('bookid', db.Integer, db.ForeignKey('bookscomp.id'), primary_key=True),
                                )

Problem: I am trying to get book_name from Book model, through Company + Bookscomp. So a company has many books and each book has reference to general book info.
Tried like this:

company = OrganizatCompanyion.query.filter_by(id=comp.id).first()
    books = company.books.all()
for item in books:
        print(item.book.book_name)
        #AttributeError: 'int' object has no attribute 
        print(item.book)
        #Gives book id from book model, but I need name

Why I cannot get book_name in the above code snippet directly? And how would it be best to achieve this?

Answer

You haven’t defined a relationship between Book and Bookcomp so when you ask for item.book— it’s getting the book = db.Column(db.Integer... value. Maybe tweak your Bookscomp model to be something like:

class Bookscomp(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    book_id = db.Column(db.Integer, db.ForeignKey('book.id'))
    book = db.relationship('Book')

Advertisement

Answer