title: Django ORM - How to left join on two conditions with many to many field
body: <p>I have two models:</p>
  
  <pre class="lang-py prettyprint-override"><code>class Subscription(models.Model):
      name = models.CharField(max_length=100, unique=True)
  
  class Email(models.Model):
      email = models.EmailField(unique=True)
      subscriptions = models.ManyToManyField(Subscription)
      is_confirmed = models.BooleanField(default=False)
  </code></pre>
  
  <p>I need to simulate sql query through django orm</p>
  
  <pre class="lang-sql prettyprint-override"><code>select * from newsletter_subscription ss
  left join newsletter_mail_subscriptions ms
      on ss.id = ms.subscription_id and ms.mail_id = &lt;mail_id&gt;;
  </code></pre>
  
  <p>so I can see if the mail is subscribed to each subscription by getting the entire list of available subscriptions</p>
  
  <p>i need something like FilteredRelated:</p>
  
  <pre class="lang-py prettyprint-override"><code>q = Subscription.objects.annotate(
      is_subscribe=FilteredRelation(
          'mail_subscriptions', condition=Q(mail_subscriptions__mail_id=10)
      )
  )
  </code></pre>
  
  <p>But FilteredRelation doesn’t support conditions that span relational fields</p>