主页 / user-1136512

bur's questions

Martin Hope
bur
Asked: 2025-03-30 01:03:40 +0800 CST
  • 6

我正在使用StringAgg如下order：

    # Get order column & annotate with list of credits
    if request.POST.get('order[0][name]'):
        order = request.POST['order[0][name]']
        if order == 'credits_primary':
            releases = releases.annotate(credits_primary=StringAgg(
                'credits__entity__name',
                delimiter=', ',
                filter=Q(credits__type='primary'),
                ordering='credits__id'
            ))
        elif order == 'credits_secondary':
            releases = releases.annotate(credits_secondary=StringAgg(
                'credits__entity__name',
                delimiter=', ',
                filter=Q(credits__type='secondary'),
                ordering='credits__id'
            ))
    else:
        order = 'title'
    
    # Order releases
    if request.POST.get('order[0][dir]') == 'desc':
        releases = releases.order_by(F(order).desc(nulls_last=True), 'title')
    else:
        releases = releases.order_by(F(order).asc(nulls_last=True), 'title')
    
    for release in releases:
        try: print(release.credits_primary)
        except: pass
        try: print(release.credits_secondary)
        except: pass

这本身完全按照预期工作：顺序是我期望的，并print返回我期望的值。

但是，当我在此之前应用 时filter，如果结果包含相同的 的多个实例release，credits__entity__name则尽管使用了 ，该版本的 s 仍会重复或省略，具体取决于所过滤字段有多少个结果distinct。

以下是filter我正在应用的影响聚合的（之前应用的StringAgg）：

    # Other filters...

    # Filter by Search
    if request.POST.get('search[value]'):
        query = Q()
        search = request.POST['search[value]']
        query.add(Q(title__icontains=search), Q.OR)
        query.add(Q(tags__tag__name__icontains=search), Q.OR)
        query.add(Q(credits__entity__name__icontains=search), Q.OR)
        releases = releases.filter(query)
        
    # Make sure items aren't repeated
    releases = releases.distinct()
django
Martin Hope
bur
Asked: 2025-03-29 22:14:05 +0800 CST
  • 6

鉴于这些模型：

class Format(models.Model):
    name = models.CharField(unique=True)
    # More fields...

class Release(models.Model):
    formats = models.ManyToManyField(Format, blank=True)
    # More fields...

当我有一个 Releases 查询集（例如通过releases = Release.objects.filter(foo='bar')）时，如何获取该查询集中的格式列表（作为对象和不同格式）？

以下两种方法都无法实现此目的：

# produces a list of dicts with IDs, not distinct e.g. [ { 'formats': 1 }, { 'formats': 2 }, { 'formats': 1 } ]:
formats = releases.values('formats')

# produces a list of IDs, not distinct, e.g. [ 1, 2, 1 ]:
formats = releases.aggregate(arr=ArrayAgg('platforms'))['arr']

我能想到的唯一方法是通过循环 ID 手动创建一个列表，检查它是否已经存在于列表中，如果不存在就添加它formats.append(Format.objects.get(id=the_id))，但如果可能的话，我真的想避免这样做。

django
Martin Hope
bur
Asked: 2025-02-27 22:17:34 +0800 CST
  • 7

在正常情况下，使用带有参数的StringAggdistinct=True是有效的，例如：

entities = entities.annotate(roles=StringAgg(
    "credits__role__name",
    delimiter=", ",
    distinct=True,
    ordering="credits__role__name"
))

但是当与条件一起使用时expression，它会引发异常django.db.utils.ProgrammingError: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list，例如：

releases = releases.annotate(credits_primary=StringAgg(
    Case(When(credits__type="primary", then="credits__entity__name")),
    delimiter=", ",
    distinct=True,
    ordering="credits__entity__name"
))

为什么会这样？有没有办法使第二个例子起作用？

编辑：这是我目前正在使用的完整代码示例，可以正常工作。我想要做的是entity从聚合列表字符串中消除重复的 s。

    if request.GET.get("order[0][name]"):
        order = request.GET["order[0][name]"]
        if order == "credits_primary":
            releases = releases.annotate(credits_primary=StringAgg(
                Case(When(credits__type="primary", then="credits__entity__name")),
                delimiter=", ",
                ordering="credits__entity__name"
            ))
        elif order == "credits_secondary":
            releases = releases.annotate(credits_secondary=StringAgg(
                Case(When(credits__type="secondary", then="credits__entity__name")),
                delimiter=", ",
                ordering="credits__entity__name"
            ))
    else:
        order = "title"
    
    # Order releases
    if request.GET.get("order[0][dir]") == "desc":
        releases = releases.order_by(F(order).desc(nulls_last=True), "title")
    else:
        releases = releases.order_by(F(order).asc(nulls_last=True), "title")

请注意，arelease可能没有任何主要或次要学分，并且 1entity可能拥有相同的多个主要或次要学分release（具有不同的roles）。

django