title: Django: How to annotate M2M or OneToMany fields using a SubQuery?
body: <p>I have <code>Order</code> objects and <code>OrderOperation</code> objects that represent an action on a Order (creation, modification, cancellation).</p>
  
  <p>Conceptually, an order has 1 to many order operations. Each time there is an operation on the order, the total is computed in this operation. Which means when I need to find an attribute of an order, I just get the last order operation attribute instead, using a Subquery.</p>
  
  <h2><strong>The simplified code</strong></h2>
  
  <pre><code>class OrderOperation(models.Model):
      order = models.ForeignKey(Order)
      total = DecimalField(max_digits=9, decimal_places=2)
  
  class Order(models.Model)
      # ...
  
  class OrderQuerySet(query.Queryset):
  
      @staticmethod
      def _last_oo(field):
          return Subquery(OrderOperation.objects
                          .filter(order_id=OuterRef("pk"))
                          .order_by('-id')
                          .values(field)
                          [:1])
  
      def annotated_total(self):
          return self.annotate(oo_total=self._last_oo('total'))
  </code></pre>
  
  <p>This way, I can run <code>my_order_total = Order.objects.annotated_total()[0].oo_total</code>. It works great.</p>
  
  <h2><strong>The issue</strong></h2>
  
  <p>Computing total is easy as it's a simple value. However, when there is a M2M or OneToMany field, this method does not work. For example, using the example above, let's add this field:</p>
  
  <pre><code>class OrderOperation(models.Model):
      order = models.ForeignKey(Order)
      total = DecimalField(max_digits=9, decimal_places=2)
      ordered_articles = models.ManyToManyField(Article,through='orders.OrderedArticle')                                       
  </code></pre>
  
  <p>Writing something like the following does NOT work as it returns only 1 foreign key (not a list of all the FKs):</p>
  
  <pre><code>def annotated_ordered_articles(self):
      return self.annotate(oo_ordered_articles=self._last_oo('ordered_articles'))
  </code></pre>
  
  <h2><strong>The purpose</strong></h2>
  
  <p>The whole purpose is to allow a user to search among all orders, providing a list or articles in input. For example: "Please find all orders containing at least article 42 or article 43", or "Please find all orders containing exactly article 42 and 43", etc.</p>
  
  <p>If I could get something like:</p>
  
  <pre><code>&gt;&gt;&gt; Order.objects.annotated_ordered_articles()[0].oo_ordered_articles
  &lt;ArticleQuerySet [&lt;Article: Article42&gt;, &lt;Article: Article43&gt;]&gt;
  </code></pre>
  
  <p>or even:</p>
  
  <pre><code>&gt;&gt;&gt; Order.objects.annotated_ordered_articles()[0].oo_ordered_articles
  [42,43]
  </code></pre>
  
  <p>That would solve my issue.</p>
  
  <h2><strong>My current idea</strong></h2>
  
  <ul>
  <li>Maybe something like <a href="https://docs.djangoproject.com/en/1.11/ref/contrib/postgres/aggregates/#arrayagg" rel="nofollow noreferrer"><code>ArrayAgg</code></a> (I'm using pgSQL) could do the trick, but I'm not sure to understand how to use it in my case.</li>
  <li>Maybe this has to do with <code>values()</code> method that seems to not be intended to handle M2M and 1TM relations as stated in the doc:</li>
  </ul>
  
  <blockquote>
    <p>values() and values_list() are both intended as optimizations for a
    specific use case: retrieving a subset of data without the overhead of
    creating a model instance. This metaphor falls apart when dealing with
    many-to-many and other multivalued relations (such as the one-to-many
    relation of a reverse foreign key) because the “one row, one object”
    assumption doesn’t hold.</p>
  </blockquote>