按django的限制1组 [英] Group by limit 1 in django

问题描述

我在Django中有以下模型（简化为简洁）：

I have the following models in Django (simplified for brevity):

class DistinctWord(models.Model):
    ...

class Word(models.Model):
    distinct_word = models.ForeignKey('DistinctWord', related_name='words')
    ...

class UserWord(models.Model):
    distinct_word = models.ForeignKey(DistinctWord, related_name='user_words')
    ...

在单词中：DistinctWord是从彼此派生的单词的根（例如，愚蠢，sillier，silliest），UserWord是用户的字典。所以，当一个用户向他的字典添加一个Word时，他实际上添加了根词（和所有相关的单词）。所以，当他请求查看/研究他的字典中的单词时，我必须带一个实际的词（例如第一个）。

In words: DistinctWord is the root of words derived from each other (e.g., silly, sillier, silliest), and UserWord is the user's dictionary. So, when a user adds a Word to the his dictionary he actually adds the root word (and thus all related words). So, I must bring an actual Word (e.g., the first one) when he requests to see/study the words in his dictionary.

也就是说，对于给定的UserWords的查询（例如 uw ），我想检索第一个Word与该查询集中的每一行相关（最好是在数据库中进行一次或几次访问，而不是每一行）。这将是一个简单的连接，分组和限制1在原始sql，但我不能把我的头围绕在Django。

That is, for a given queryset of UserWords (say uw), I would like to retrieve the first Word related for each row in that queryset (preferably in one or a few trips to the database, not one for each row). This would be a simple join, group by and limit 1 in raw sql, but I could not wrap my head around it in Django.

推荐答案

让我们：

uw # be a given queryset of UserWord's
dw # be a queryset of DistinctWords (will be derived from `uw`)
w  # be a queryset of Words needed (will be derived from `dw`)

每个 UserWord 有一个 DistinctWord ，每个 DistinctWord 有许多 Word （松散地表示为code> uw> dw< w ）

Each UserWord has a DistinctWord, and each DistinctWord has many Word's (loosely denoted as uw>dw<w).

这是我的答案：

dw_id=uw.values_list('distinct_word_id', flat=True) # 1: get dw ids from uw
dw=DistinctWord.objects.filter(id__in=dw_id)        # 2: get dw's
w_first_id=dw.annotate(first_word=Min('words')).values_list('first_word', flat=True) 
                                                    # 3: find id of first word 
w=Word.objects.filter(id__in=w_first_id)            # 4: get first words

总结：林es 1和2得到 dw ，应该只是一次到数据库

In summary: lines 1 and 2 get dw and should be just 1 trip to the database

第3行使用注释，后跟 values_list 找到第一个相关的 Word

line 3 uses annotate followed by values_list to find the id of first related Word

第4行带来了上一步中生成的id的实际Word对象。第3行和第4行应该是另一次访问数据库，因为 annotate 不是终端语句。

Line 4 brings the actual Word objects from the id's generated in the previous step. Lines 3 and 4 should be another trip to the database since annotate is not a terminal statement.

因此，2次访问数据库（未测试）。

Thus 2 trips to the database (not tested).

这篇关于按django的限制1组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！