How can I refer to a field from a table which is not related with the field directly?

Question

In Django ORM I tried to JOIN 4 tables, Order, OrderDetail, Item and ItemPhoto and to access ItemPhoto.photo from Order table.

At first, I wrote code with Subquery starting with OrderDetail table and could get fields I wanted to get, but I realized the result was not what I expected.

Then, I changed my codes and tried to use Subquery starting from Order table, but I noticed Order table didn't have item field and it seemed to be difficult to use Subquery in Django in this case.

So, I wrote code without Subquery and managed to reach ItemPhoto.photo, but it is complecated and difficult to use in Template to some extent.

How can I make my code better to refer to a field from a table which is not related with the field directly?

queryset = Order.objects.prefetch_related( 
    Prefetch(
        'orderdetail_orderid', 
        queryset=OrderDetail.objects.select_related('order', 'item'),
        to_attr='oor',
    ),
).filter(user=user).order_by('-order')

# I could access to ItemPhoto.photo, but it's complicated.
[p.photo for q in queryset for i in q.orderdetail_orderid.all() for p in i.item.item_photo.all()]

models.py(omitted)

class ItemCategory(models.Model):
    parent_id = models.IntegerField(default=0) 
    category_name = models.CharField(max_length=64, default="")
    category_type = models.IntegerField(default=0)

class Item(models.Model): # Product
    name = models.CharField(max_length=255, blank=False, default="")
    price = models.DecimalField(
        max_digits=12,
        decimal_places=2,
        default=0.00, 
        blank=False,
    ) 
    category = models.ForeignKey(ItemCategory, 
        on_delete=models.SET_NULL, null=True,
        related_name="item_category")

class ItemPhoto(models.Model):
    item = models.ForeignKey(Item, on_delete=models.CASCADE,
        related_name="item_photo",)
    photo = models.ImageField(blank=True, 
        upload_to=get_itemimage_path)

class Order(models.Model):
    user = models.ForeignKey(settings.AUTH_USER_MODEL, 
        on_delete=models.SET_NULL, 
        null=True, 
        related_name="order_user",
    )
    total_price = models.DecimalField(
        max_digits=12,
        decimal_places=2,
        default=0.00, 
    )
    created_at = models.DateTimeField(auto_now_add=True) 

class OrderDetail(models.Model):
    order = models.ForeignKey(Order, 
        on_delete=models.CASCADE,
        null=False,
        related_name="orderdetail_orderid",) 
    item = models.ForeignKey(Item,
        on_delete=models.SET_NULL,
        null=True, 
        related_name="orderdetail_item",)
# price
# quantity
    created_at = models.DateTimeField(auto_now_add=True)

Python: 3.9 / Django: 4.1 / MySQL / SQLite3

Answer 1

I happened to come up with a different approach with using Subquery.

subqs = ItemPhoto.objects.filter(item=OuterRef('item')).filter(priority=1)

queryset = Order.objects.prefetch_related( 
    Prefetch(
        'orderdetail_orderid',
        queryset=OrderDetail.objects.select_related('order', 'item').annotate(
            item_photos=Subquery(subqs.values('photo')),
        ),
        to_attr='oor',
    ),
).filter(user=user)
prices = [o.price for q in queryset for o in q.oor]
photos = [o.item_photos for q in queryset for o in q.oor]

I could reach ItemPhoto.photo with Subquery easier, although I wonder if this is the best way or not.