Join two tables through third one with two foreign keys-CodePudding

I have three tables:

class Bank(models.Model):
    ...

class Deposit(models.Model):
    ...

class DepositProposal(models.Model):
    percent = models.FloatField()
    deposit = models.ForeignKey(Deposit)
    bank = models.ForeignKey(Bank)

Banks can participate in deposit auctions by applying the DepositProposal instance. How can I filter Banks that do not participate in Deposit? Found that I should use two left join but I didn't come across it using PosgtreSQL.

CodePudding user response：

If you want to know the deposits in which a bank does not participate:

1- Add a related_name to deposit and bank:

class DepositProposal(models.Model):
    percent = models.FloatField()
    deposit = models.ForeignKey(Deposit, related_name = "proposals")
    bank = models.ForeignKey(Bank, related_name = "proposals")

2- Get the bank that you want to check:

bank = Bank.objects.first() #For example, the first bank

3- Get the queryset:

deposits = Deposit.objects.exclude(proposals__bank = bank)

EDIT:

If you want to know the banks that not participate on a specific deposit:

deposit = Deposit.objects.first() #For example, the first deposit
banks = Bank.objects.exclude(proposals__deposit = deposit)