Adding related column to query results?

Question

I have a setup in SQLAlchemy ORM (using Flask-SQLAlchemy) with Book and BookSubject classes, the latter being a many-to-many relationship (there is, of course, a Subject class, but it's not relevant to this question). I have a working query to return all books based on the date the subject was added to the database (there's a reason for this):

 records = Book.query.\
        filter(BookSubject.book_id == Book.id).\
        filter(BookSubject.subject_id.in_([1, 12, 17])).\
        filter(BookSubject.created > '2021-01-01').\
        order_by(db.desc(BookSubject.created)).\
        paginate(page, 50, True)

I then pass records to a Jinja2 template and do assorted stuff to display it; it works perfectly.

I'd now like to do the obvious thing and actually display the creation date (i.e. BookSubject.created, from the order_by clause), but I can't figure out how to add this to the query. Putting in add_columns((BookSubject.created).label("added")) isn't the answer; that throws an error when I try to use one of the record objects "'sqlalchemy.util._collections.result object' has no attribute 'id'". The template code that generates this is (roughly):

{% for book in records.items %}
  <tr><td><a href="{{ url_for('fullview', id=book.id) }}">{{ book.title }}</a></td></tr>
{% endfor %}

This should be obvious; how am I meant to add this to the result?

add_columns is a correct way, the only difference it returns a named tuple (row) instead of an ORM object (Book). please provide the code which produces the last exception. — inf581
– inf581, Commented Feb 11, 2021 at 6:56
Thanks. I've edited the post to show both the template code that produces the exception, and also the paginate call (from Flask-SQLAlchemy) in the original query that I didn't previously mention. — user9219182
– user9219182, Commented Feb 11, 2021 at 9:52

inf581 · Accepted Answer · 2021-02-11 11:03:12Z

2

By using add_columns

Book.query.add_columns((BookSubject.created).label("added"))

it will return a named tuple with fields Book and added, so to access book fields you'd need something like book.Book.id

{% for book in records.items %}
  <tr><td>
     <a href="{{ url_for('fullview', id=book.Book.id) }}">{{ book.Book.title }} {{ book.added }} </a>
  </td></tr>
{% endfor %}

or iterate by pairs:

{% for book, added in records.items %}
  <tr><td>
     <a href="{{ url_for('fullview', id=book.id) }}">{{ book.title }} {{ added }} </a>
  </td></tr>
{% endfor %}

If you want a flat structure (and without add_columns), then you can use

session.query(
  Book.id,
  Book.title,
  BookSubject.created.label('added')
).filter(BookSubject.book_id == Book.id)...

then results will have a named tuple with fields id, title and added, so you can print directly book.id:

{% for book in records.items %}
  <tr><td>
    <a href="{{ url_for('fullview', id=book.id) }}">{{ book.title }} {{ book.added }} </a>
  </td></tr>
{% endfor %}

answered Feb 11, 2021 at 11:03

inf581

6521 gold badge8 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user9219182 Over a year ago

Spot on, thanks. I'm ending up using the last variant, because there are other queries that use the same template and I'd like the structure to remain flat for all of them.

Collectives™ on Stack Overflow

Adding related column to query results?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related