Django - Performing a subquery on a subquery and then getting all associated fields

Question

I have data in the following form:

collection_name | type       | manufacturer | description | image_url
---------------------------------------------------------------------------
beach           | bed        | company a    | nice bed    | 1.jpg
beach           | king bed   | company a    | nice bed    | 1.jpg
beach           | nightstand | company a    | nice ns     | 1.jpg
grass           | chest      | company a    | nice chest  | 2.jpg
apple           | chest      | company a    | nice chest  | 3.jpg
fiver           | chest      | company b    | good chest  | 4.jpg

and models like:

class Product(models.Model):
    collection_name = models.TextField(null='true',blank='true')
    type = models.TextField(null='true',blank='true')
    manufacturer = models.TextField(null='true',blank='true')
    description = models.TextField(null='true',blank='true')
    image_url = models.TextField(null='true',blank='true')

What am I trying to do in my app at the moment is:

• Link to a product by id (using the hidden pk id field),
• Then for that id get the collection name
• Get a list of products with that collection name
• Get a list of distinct image_urls from the set of products with a certain collection name *
• for each image_url in the new set, get all records that have the same image_url

The reason I want to do this, is as you can see in the above sample data, different products sometimes reuse the same image (some images show multiple products in one image). I want to show each image only once, while being able to show all products associated with a given image (of which there could be multiple).

I've been thinking to do something like the following based on this answer, which I think follows the logic given above, but I'm not sure it is the correct approach.

collectionname = product.objects.filter(id=id).values('collection_name').distinct()
images = product.objects.filter(collection_name__in=collectionname).values("image_url").distinct()
results = []
for img in images:
    pbis = product.objects.filter(collection_name__in=collectionname, image_url=img['image_url'])
    obj = {"image": img['image_url'], "items":[{"attr":pbi.attr, ...} for pbi in pbis]}
    results.append(obj)

What are the obvious errors in my approach here, and is there a better, cleaner way to do this? In case it is relevant, the backend is postgres.

What I would like to be able to do in the template is something like:

{% for instance in image_url %}
{{ collection_name }} Collection:
<img src="{{ instance }}">
Product type: {{ instance.type }}
Product Description: {{ instance.description }}
{% endfor %}

Which should output something like:

For 1.jpg:

Beach Collection
<img src="1.jpg">
Product type: bed
Product Description: nice bed
Product type: king bed
Product Description: nice bed
Product type: nightstand
Product Description: nice ns

For 2.jpg:

Grass Collection
<img src="2.jpg">
Product type: chest
Product Description: nice chest

For 3.jpg:

Apple Collection
<img src="3.jpg">
Product type: chest
Product Description: nice chest

For 4.jpg:

Fiver Collection
<img src="4.jpg">
Product type: chest
Product Description: good chest

Answer 1

It sounds like a couple of extra models would make it easier to work with your content: Collection and Image. For your Image model, I'd recommend using Django's ImageField, which is more fully featured than just saving the URL as text. To use ImageField, you need to install Pillow, which is a current maintained fork of the Python Image Library (PIL). Do this with pip install Pillow on the command line. An ImageField has a built-in attribute url, so you can still access the URL of your image easily (see template below). Then your new models.py can be:

# models.py
class Image(models.Model):
    source = models.ImageField(upload_to='my_media_path')

class Collection(models.Model):
    name = models.CharField(max_length=30)
    products = models.ManyToManyField(Product, blank=True)
    feature_image = models.ForeignKey(Image, related_name='collections', on_delete=models.SET_NULL)

class Product(models.Model):
    ...
    image = models.ForeignKey(Image, related_name='products', on_delete=models.SET_NULL) 
    # or ManyToManyField if a product has several images

Then you can get the other products that are in the same image as your selected product:

bucket = Product.objects.get(pk=1)
bucket.image.products.all()
<Product: Bucket>
<Product: Spade>

And for your templates - which organise content by collection mainly:

# query
collections = Collection.objects.all()

# template.html
{% for collection in collections %}
    <h1>{{ collection.name }}</h1>
    <img src="{{ collection.feature_image.source.url }}">
    {% for product in collection.products.all %}

        <p>{{ product.name }}</p>
        <p>{{ product.description }}</p>

    {% endfor %}
{% endfor %}

EDIT:

With no time to implement the above approach, the approach you mentioned will work fine. You can change one line in the view code to keep it simpler:

...
obj = {"image": img['image_url'], "items": pbis}

And then when you pass the results dict to the template context:

{% for collection in results %}
    {{ collection.items.0.collection_name }} Collection:
    <img src="{{ collection.image }}">
    {% for item in collection.items %}
        <p>Product type: {{ item.type }}</p>
        <p>Product Description: {{ item.description }}</p>
    {% endfor %}
{% endfor %}