Query elements by attribute of Foreignkey Relation and also Query Foreign attributes most efficient

There are three models

Product: Having an id, a name

Storage: Having an id, a name, a color and a branch

ProductStorage: Maps each Product to a Storage and has some extra attributes.

I want to list all ProductStorages with a specific Storage.branch value grouped by the Storage, displaying storage name and color too.

Is it possible to do this in one query? I am new to Django and I am unsure how to do this in the most efficient way.

I know that I could query all ProductStorages and filter by branch. Then sort them maybe afterwards by their storages and query the storage-attributes in a second query. But I guess this won't be the best way to do it.

Example (All ProductStorages grouped by Storages):

"Storage.Name1, Storage.Color1":
ProductStorage.Name1, ProductStorage.attr1, ...
ProdcutStorage.Name2, ProductStorage.attr2, ...
ProductStorage.Name3, ProductStorage.attr3, ...
...

"Storage.Name2, Storage.Color2":
ProductStorage.Name1, ProductStorage.attr1, ...
ProdcutStorage.Name2, ProductStorage.attr2, ...
ProductStorage.Name3, ProductStorage.attr3, ...
...

Solution

Just query with the related Storage and ProductStorages:

from django.db.models import Prefetch

products = Product.objects.prefetch_related(
    Prefetch(
        'productstorage_set', ProductStorage.objects.select_related('storage')
    )
)

then we can group the items by the corresponding name and color:

from collections import defaultdict

for product in products:
    product.items = defaultdict(list)
    for item in product.productstorage_set.all():
        product.items[item.storage.name, item.storage.color].append(item)

and pass products to the template:

{% for product in products %}
    <h1>{{ product.name }}</h1>
    <ul>
    {% for key, val in product.items %}
        <li><b>{{ key.0 }} - {{ key.1 }}</b>
        {% for item in val %}
            <li>{{ item.name }} - {{ item.extra_attr }}</li>
        {% endfor %}
        </li>
    {% endfor %}
    </ul>
{% endfor %}