Flask SQLAlchemy querying beetwen 2 tables

Question

I'm trying to create query like:

SELECT Book.title, Opinion.body  
FROM Book,Opinion  
WHERE Opinion.book_id = Book.id

but in SQLAlchemy. I want to show all opinions for book and display it on web page.

models.py:

class Opinion(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    body = db.Column(db.String(140))
    timestamp = db.Column(db.DateTime, index=True, default=datetime.utcnow)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    book_id = db.Column(db.Integer, db.ForeignKey('book.id'))

    def __repr__(self):
        return '<Post {}'.format(self.body)

class Book(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(255), index=True)
    description = db.Column(db.String(255))
    author = db.Column(db.String(255))
    pages = db.Column(db.Integer)
    opinions = db.relationship('Opinion', lazy='dynamic')

I can create query where is only return for 1 book, but not for all. I tried this:

result = Opinion.query(Book.id, Opinion.body).filter_by(Opinion.book_id = Book.id).all()

But it still doesnt work. Can someone help me with this?

The query you've presented should have raised an exception. In such a case you should include that exception in the post, instead of just noting that "it doesn't work". You should read ["What's the difference between filter and filter_by in SQLAlchemy?"](https://stackoverflow.com/questions/2128505/whats-the-difference-between-filter-and-filter-by-in-sqlalchemy) — Ilja Everilä, Nov 08 '18 at 15:19

score 0 · Answer 1 · answered Nov 08 '18 at 15:07

0

@app.route('/detail_book/<title>')
def detail_book(title):
    book = Book.query.filter_by(title=title).first_or_404()
    result = db.session.query(Book.id,Opinion.body).filter(Opinion.book_id==book.id).filter(and_(Book.title == title)).all()
    return render_template('detail_book.html', book=book, result=result)

It works as it should

answered Nov 08 '18 at 15:07

Frendom

508
6
24

I doubt it. `result` is again the result of a cross join, which looks more likely than not accidental. – Ilja Everilä Nov 08 '18 at 15:14
I want to get back opinions for specific books. Before I add and_ statement, I got multiple opinions which fit to the book, but for example: I had 2 opinions in db, but 6 was displayed. After adding add_ it works – Frendom Nov 08 '18 at 16:02
The `and_()` is redundant, since calls to `filter()` are combined with AND anyway, and think what will happen, if 2 books happen to share a title (which your model allows) — you'll again display excess amounts of opinions, multiplied by the amount of books sharing the title. – Ilja Everilä Nov 08 '18 at 18:41

simanacci · Accepted Answer · 2018-11-08T15:58:49.203

-1

Alternatively you can query and render the results as shown below.

book = Book.query.get(book_id)
opinions = book.opinions.all()

In your templates render them as follows:

{{ book.title }}
{% for item in opinions %}
    {{ item.body }}
{% endfor %}

edited Nov 08 '18 at 15:58

answered Nov 08 '18 at 13:47

simanacci

2,197
3
26
35

Ok, it works and i can display opinion on my webpage, but it display all opinions in all books. And i want to display only opinions which belong to this book. – Frendom Nov 08 '18 at 14:34
`book_id` has to be a variable i.e `book_id=1` to get opinions for a specific book. I had forgotten to edit `Opinion.book_id==Book.id` which returns all opinions. – simanacci Nov 08 '18 at 14:46
@IljaEverilä I don't understand. The first query? – simanacci Nov 08 '18 at 15:06
After trying my first solution, I updated with an alternative solution which works. – simanacci Nov 08 '18 at 15:56

Flask SQLAlchemy querying beetwen 2 tables

2 Answers2