Django-使用postgres后端返回所有关联的行以获得不同的结果集 [英] Django - return all associated rows for distinct results set with postgres backend

问题描述

我具有以下形式的数据：

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

我需要做的是选择所有图像，并且只返回每个图像一次（区别），然后为每个图像返回不同的行。

What I need to do, is select all images and only return each image once (distinct), but then return non-distinct row for each image.

我的目标是确保在模板中只显示一次图像，但显示与每个图像相关的所有记录图像。

My goal is to ensure I display each image only once in my template, but show all records associated with each image.

在上面的示例中，1.jpg是一张图像，在一张图像中同时显示了床和床头柜。我想显示这样的图像并列出与之相关的产品。

In the example above, 1.jpg is one image that would show both beds and the nightstand in one image. I would like to show such an image and list associated products with it.

我也看到了类似的问题，尽管在SQL / db级别询问而不是纯粹询问。 django解决方案。

I have seen similar questions, although asking at the SQL/db level and not asking for a pure django solution.

我一直使用的查询类似于：

The query I have been using in my view has been something like:

products = product.objects.filter(collection_name=name) 

然后进行迭代产品上，像这样检索image_url：

and then iterating over products, retrieving image_url like so:

{% for instance in products %}
{{ instance.image_url }}
{{ endfor }}

我尝试了各种尝试来限制重复图像我的模板，但没有一个真正起作用，并且我认为尝试没有成功。

I've tried various attempts to limit repeating images in my template, but none have really worked, and attempts to do so in my view have not been successful.

解决此问题的正确方法是什么？

What is the correct way to approach this?

编辑： 我模型中的相关摘录与上面的示例数据匹配：

A relevant excerpt from my models to match the sample data above:

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')

编辑： 阅读 docs 并查看其他问题（无答案）：

My idea of views and logic to attempt to solve this, after reading the docs and looking at other questions (no answers):

将集合中任何产品的product_id传递给视图。然后根据id字段获取记录的collection_name字段：

Pass the product_id of any product in a collection to the view. Then obtain the collection_name field of a record based on the id field:

collectionname = product.objects.filter(id=id).values('collection_name').distinct()

然后，当我们有collection_name字段时，返回给定collection_name的所有产品：

Then, when we have the collection_name field, return all products for a given collection_name:

products = product.objects.filter(collection_name__in=collectionname)

然后，最后返回给定集合名称的image_url结果列表，并删除重复项：

Then, finally, return a list of image_url results for a given collection name, removing duplicates:

images = product.objects.filter(collection_name__in=collectionname).values('image_url').distinct()

从理论上讲，我认为这应该可行...

I think this should work, in theory...

编辑：

当前正在根据Juancarlos的以下答案尝试以下操作：

Currently attempting the following based on Juancarlos' answer below:

products = product.objects.filter(collection_name=name)
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":[{"type":pbi.type} for pbi in pbis]}
    results.append(obj)

推荐答案

也许这种逻辑可以帮助您，我不确定，但是您可以执行以下操作：

mabe this logic can help you, i am not sure but you can do something like this:

images = product.objects.filter(collection_name=name).values("image_url").distinct()
results = []
for img in images:
    pbis = product.objects.filter(collection_name=name, image_url=img['image_url'])###this get all record tha contains this image
    obj = {"image": img['image_url'], "items":[{"attr":pbi.attr, ...} for pbi in pbis]}
    results.append(obj)
    ###this iterate all record by images and you can store items attribute from all recors that contains that image

这篇关于Django-使用postgres后端返回所有关联的行以获得不同的结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！