CODEWITHSUNDEEP
django
Krisnadi
Krisnadi

Reputation: 681

Subquery select where clause

I have this sql statement:

SELECT * FROM result WHERE bet_id IN (SELECT id FROM bet WHERE STATUS="Active")

and this is my view:

def manageresult(request):
    if 'usid' in request.session:
        result = Result.objects.all()
        admin = Admin.objects.get(id=request.session['usid'])
        return render(request, 'manageresult.html', {'result':result,'admin':admin})
    else:
        return redirect('login')

How to change result = Result.objects.all() to that sql statement? This is bet model:

class Bet(models.Model):
    status = models.CharField(max_length=20, default="Active")

This is Result model:

class Result(models.Model):
    bet = models.OneToOneField(Bet, on_delete=models.CASCADE)

Upvotes: 2

Views: 373

Answers (3)

zsepi
zsepi

Reputation: 1662

disclaimer: when writing this answer, the models in question were not known

In case the Result model has a ForeignKey to Bet, you can filter by joins - it would make it more like

result = Result.objects.filter(bet__status='Active')

which would translate to the below SQL query

SELECT result.* FROM result INNER JOIN bet on result.bet_id = bet.id WHERE bet.STATUS="Active"

See Django's documentation on Lookups that span relationships

If that is not the case, Todor's answer is the way to go

Upvotes: 2

falsetru
falsetru

Reputation: 369074

You can use bet__status=...:

result = Result.objects.filter(bet__status='Active')

Upvotes: 2

Todor
Todor

Reputation: 16010

You can make querysets nested

result = Result.objects.filter(bet__in=Bet.objects.filter(status='active'))

Upvotes: 0

Related Questions