My book website tracks the books I read each year. The original site was written in PHP and now I'm in the process of rewritting it in Python with Flask and SQLAlchemy using Jinja2 templates.

In my html template, I want to print the books for the most recent year at the top. Then print each book alphabetically by title. Then repeat for subsequent years. It should be something like this.

Year: 2019

  • A Darker Place
  • Before We Were Yours
  • ...

Year: 2018

  • A Superior Death
  • Crown of Midnight
  • ...

I figured out how to sort the list of books in SQL Alchemy, but I wanted to do the group by when rendering the book list on the page. I was having trouble doing this correctly with SQL Alcuemy queries.

If this were plain SQL, I'd be looking at something like:

SELECT year, title from books order by year desc, title group by year;

I found the groupby filter in Jinja2 and thought I had found the answer. But, when using groupby it destroyed my descending sort and and displayed the years in ascending order (2017, 2018, 2019).

Next I found the sort filter and that would sort the titles OK, but playing around with it, I couldn't get the desired order straight.

Finally, I noticed the reverse filter which changes the order of the groupby filter.

Much closer! I thought for sure this following Jinja2 line would be the answer. But, it resulted in an Error:

{% for group in books|groupby('read_year')|reverse|sort(attribute='title') %}

jinja2.exceptions.UndefinedError: 'jinja2.filters._GroupTuple object' has no attribute 'title'

Turns out, the order of applying the filters matters. Doing sort then groupby returns the desired result.

{% for group in books|sort(attribute='title')|groupby('read_year')|reverse %}

Beautiful. Plus, I learned new stuff. That's always a bonus.

Here is the link to the List of Builtin Filters in the Jinja2 Template Designer Documentation.

But wait...

All of that being said about sorting and grouping in the Jinja2 template, as mentioned at the top of the article, Jinja2 may not be the best place to do all of this. I don't think I am done with the SQL Alchemy versions of all this. I will be revisiting it as I learn more.