66828

Group by limit 1 in django

Question:

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.

Answer1:

How about (given your queryset of uw): [obj.words.first() for obj in uw]

Answer2:

Let:

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

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

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 Word

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

Recommend

  • Regex for IBAN mask
  • VB.NET and a 'login session'
  • Kendo UI Grid - Filter with Select Multi-Checkbox
  • Insert reportlab chart into django template
  • Filter a DJango ContentType queryset to only include models that have a specific method
  • Select a valid choice ModelChoiceField
  • query.group_by in Django 1.9
  • Need clear idea about timespec structure
  • When to use clone() and how actually addAll() and add() works
  • Django RESTful API error “type object 'User' has no attribute '_meta'”
  • Add Jpanel to Jframe NetBeans
  • Change zIndex in HighChart
  • mysql table locked after php crashes
  • Generating random numbers directly inside a .htaccess file
  • Excel passing a range into a function
  • Typecasting `this` of a base class template to its derived class
  • Basic defensive programming [duplicate]
  • Bound DataGridViewCheckBoxColumn not updating binding source
  • File extension of zlib zipped html page?
  • How do I mock an exported typescript function in a jasmine test?
  • jQuery: How to AJAXify WordPress Search?
  • SyntaxError: (irb):26: both block arg and actual block given
  • Salesforce Different WSDL files and when to use
  • Why cepheus don't send int without quotes to orion?
  • Mysql - How to search for 26 records that each begins with the letter of the alphabet?
  • presentShareDialogWithParams posts to FB wall, but callback handler results say error
  • Unable to get column index with table.getColumn method using custom table Model
  • How can I enlarge video fullscreen without the affected interface project in as3?
  • Declaring variable dynamically in VB.net
  • Time complexity of a program which involves multiple variables
  • Checking free space on FTP server
  • Can I display google adwords (AdView) in javafx on android
  • recyclerView does not call the onBindViewHolder when scroll in the view
  • Join two tables and save into third-sql
  • Can I make an Android app that runs a web view in Chrome 39?
  • How to make Safari send if-modified-since header?
  • Web-crawler for facebook in python
  • How do I rollback to a specific git commit
  • how does django model after text[] in postgresql [duplicate]
  • Cant find why the layout is getting smaller