django left join with where clause subexpression
<p>I'm currently trying to find a way to do something with Django's (v1.10) ORM that I feel should be possible but I'm struggling to understand how to apply the documented methods to solve my problem.</p>

<p><strong>Edit:</strong> So here's the sql that I've hacked together to return the data that I'd like from the <code>dbshell</code>, with a postgresql database now, after I realised that my original sqlite3 backed sql query was incorrect:</p>

<pre><code>select 
    voting_bill.*,vv.vote 
from 
    voting_bill 
left join 
    (select 
        voting_votes.vote,voting_votes.bill_id 
    from 
        voting_bill 
    left join 
        voting_votes 
    on 
        voting_bill.id=voting_votes.bill_id 
    where 
        voting_votes.voter_id = (select id from auth_user where username='richard' or username is Null)
   ) 
as 
    vv 
on 
    voting_bill.id=vv.bill_id;
</code></pre>

<p>Here's the 'models.py' for my voting app:</p>

<pre><code>from django.db import models
from django.contrib.auth.models import User


class Bill(models.Model):
    name = models.CharField(max_length=255)
    description = models.TextField()
    result = models.BooleanField()
    status = models.BooleanField(default=False)

    def __str__(self):
        return self.name


class Votes(models.Model):
    vote = models.NullBooleanField()
    bill = models.ForeignKey(Bill, related_name='bill',
                             on_delete=models.CASCADE,)
    voter = models.ForeignKey(User, on_delete=models.CASCADE,)

    def __str__(self):
        return '{0} {1}'.format(self.bill, self.voter)
</code></pre>

<p>I can see that my sql works as I expect with the vote tacked onto the end, or a null if the user hasn't voted yet.  </p>

<p>I was working to have the queryset in this format so that I can iterate over it in the template to produce a table and if the result is null I can instead provide a link which takes the user to another view.</p>

<p>I've read about select_related and prefetch_related, but as I said, I'm struggling to work out how I translate this to how I can do this in SQL.</p>

<p>Hope I correctly understood your problem. Try this:</p>

<pre><code>votes = Votes.objects.filter(voter__username='django').select_related('bill')
</code></pre>

<p>You can use this. But I think you do not need <code>select_related</code> in this case.</p>

<pre><code>bills_for_user = Bill.objects.filter(votes__voter__username='django').select_related('votes').distinct()
</code></pre>

<p>Now you can iterate your bills_for_user</p>

<pre><code>for bill in bills_for_user:
    bill_name = bill.name
    bill_description = bill.description
    bill_result = bill.result
    bill_status = bill.status

    # and there are several variants what you can with votes
    bill_votes = bill.votes_set.all()  # will return you all votes for this bill
    bill_first_vote1 = bill.votes_set.first() # will return first element in this query or None if its empty
    bill_first_vote2 = bill.votes_set.all()[0] # will return first element in this query or Error if its empty
    bill_last_vote = bill.votes_set.last()[0] # will return last element in this query or None if its empty
    # you can also filter it for example by voting
    bill_positive_votes = bill.votes_set.filter(vote=True) # will return you all votes for this bill with 'vote' = True
    bill_negative_votes = bill.votes_set.filter(vote=False) # will return you all votes for this bill with 'vote' = False
    bill_neutral_votes = bill.votes_set.filter(vote=None) # will return you all votes for this bill with 'vote' = None
</code></pre>

python django join django-queryset where-clause