1

I'm running a query and filtering results on 3 tables.

I wrote the function below to perform this query, it looked like this:

def bookshelf():
     your_user = db.session.query(User).filter_by(email=session['email']).first().uid
     your_books = db.session.query(user_book).filter_by(uid=your_user).all()
     name_books = db.session.query(Book).filter_by(bid=your_books.bid).all()
     return name_books
  • The variable your_user gets from the table user the id of the user who is logged in;
  • The variable your_books gets from the table user_books all the books added by the logged in user;
  • The variable name_books should get from the table books all the data of the books filtered by the id of the books (bid).

The problem occurs when I try to filter using your_books.bid, the console returns:

AttributeError: 'list' object has no attribute 'bid'

These are the tables cited above:

user_book = db.Table('user_book',
                     db.Column('uid', db.Integer, db.ForeignKey('user.uid'), primary_key=True),
                     db.Column('bid', db.Text, db.ForeignKey('book.bid'), primary_key=True),
                     db.Column('date_added', db.DateTime(timezone=True), server_default=db.func.now())
                     )


class User(db.Model):
    __tablename__ = 'user'

    uid = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(25), nullable=False)
    hash = db.Column(db.String(), nullable=False)
    first_name = db.Column(db.String(30), nullable=True)
    last_name = db.Column(db.String(80), nullable=True)
    books = db.relationship('Book', secondary=user_book)


class Book(db.Model):
    __tablename__ = 'book'

    bid = db.Column(db.Text, primary_key=True)
    title = db.Column(db.Text, nullable=False)
    authors = db.Column(db.Text, nullable=False)
    thumbnail = db.Column(db.Text, nullable=True)
    users = db.relationship('User', secondary=user_book)

I also tried to do something like your_books = db.session.query(user_book.bid) but I got the same error message.

I also found this answer: Sqlalchemy single query for multiple rows from one column in one table, but I couldn't apply it.

What am I missing?

ARNON
  • 1,097
  • 1
  • 15
  • 33
  • 2
    While `your_user` actually gets a single record, and so you can access its fields, `your_books` gets a _list_ of records. You will need to either loop on the list items or, better, to make a query which joins `Book` and `user_book` on `bid` and filters by `user_book.uid` – gimix Jul 08 '21 at 14:25

1 Answers1

1

As I understand it, and following some of the logic, you want to show the books, right? So instead of query(User), use query(Book). Then you can do the .join()as instructed in the comments.

def bookshelf():
    your_user = db.session.query(User).filter_by(email=session['email']).first().uid
    your_books = db.session.query(Book).join(user_book).filter_by(uid=your_user).all()
    return your_books

If you have any questions, let me know and I'll clarify.

Adão Gama
  • 122
  • 1
  • 13