How to avoid O(n+1) problem with peewee object?

Question

I have these following models in peewee:

db.py

class Item(BaseModel):
    item_id = IntegerField()
    item_name = CharField()

class Sales(BaseModel):
    sales_id = IntegerField()

class SalesItem(BaseModel):
    sales_item_id = Integer_Field()
    sales = ForeignKeyField(Sales, backref='items')
    item = ForeignKeyField(Item)

view.py

templates = Jinja2Templates(directory="templates")

def html_get(request, sales_id):
    sales = Sales.get(Sales.sales_id==sales_id)
    return templates.templateResponse('view_sales.html',{'sales':sales})

view_sales.html

Sales: {{ sales.sales_id }}
Items: 
{% for it in sales.items %}
<div>{{ it.item.item_name }}</div>
{% endfor %}

The problem is the query sales = Sales.get(Sales.sales_id==sales_id) generates O(n+1) queries to get to each item's name. I have tried to create the join sales = Sales.select(Sales,SalesItem,Item).join(SalesItem).join(Item).where(Sales.sales_id==sales_id) but it also generates O(n+1) queries.

I looked into using prefetch, but couldn't get it to work. Any idea how to reduce the queries to O(k) where k=number of tables?

Answer 1

With these updates, the html_get function will perform only one query by joining the Sales and Item models and fetching the related data using Prefetch. This will reduce the number of queries and improve performance.

def html_get(request, sales_id):
    sales_query = Sales.select().where(Sales.sales_id == sales_id)
    sales_with_items = prefetch(sales_query, SalesItem.select().join(Item))
    return templates.templateResponse('view_sales.html', {'sales': sales_with_items})

Answer 2

Here is how to do it in 2 queries, correctly:

sales = Sales.select().where(Sales.sales_id == s.sales_id)
q = prefetch(sales, SalesItem.select(SalesItem, Item).join(Item))
for s in q:
    print(s.sales_id)
    for si in s.items:
        print('-', si.item.item_name)

Note how in the second query we are selecting from both SalesItem and Item.

This is covered in the docs: http://docs.peewee-orm.com/en/latest/peewee/relationships.html#avoiding-the-n-1-problem