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') ...
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.
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.
How about (given your queryset of
[obj.words.first() for obj in uw]
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 has a
DistinctWord, and each
DistinctWord has many
Word's (loosely denoted as
Here is my answer:
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
In summary: lines 1 and 2 get
dw and should be just 1 trip to the database
line 3 uses
annotate followed by
values_list to find the id of first related
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.
Thus 2 trips to the database (not tested).