I want to join two model. I am using postgresql. But the problem is I am not using any foreign key or many to field. But I am using same unique field for both table. So I want depends on thats field I can query from both table. "invoice" is the field. My models are

     class Salesinvoice(models.Model):
           id = models.AutoField(primary_key=True)
           date = models.DateField(default=date.today)
           invoice = models.CharField(max_length=20)
           customer = models.CharField(max_length=100)
           product_name = models.CharField(max_length=80)
           price = models.DecimalField(max_digits=9, decimal_places=2, default=0.00)
           quantity = models.IntegerField(default=0)
   



    class Salespayment(models.Model):
         id = models.AutoField(primary_key=True)
         date = models.DateField(default=date.today)
         invoice = models.CharField(max_length=20)
         amount = models.DecimalField(max_digits=9, decimal_places=2, default=0.00)
         discount = models.DecimalField(max_digits=9, decimal_places=2, default=0.00)
         payment = models.DecimalField(max_digits=9, decimal_places=2, default=0.00)

What should be my Views for join above both table depends on invoice field.

CodePudding user response：

For example, add a function to Salesinvoice class:

class Salesinvoice(models.Model):
 id = models.AutoField(primary_key=True)
 ....
 quantity = models.IntegerField(default=0)
 def payment(self):
  if Salespayment.objects.filter(invoice = self.invoice).count() == 1:
   return Salespayment.objects.get(invoice = self.invoice)
  else:
   return None

So, when you need the related Salespayment in your views.py , just do:

salesinvoice = Salesinvoice.objects.get(...)
salespayment = salesinvoice.payment()

Same in templates:

{{salesinvoice.payment.anyfield}}

CodePudding user response：

Change your models, specifically:

1. SalesInvoice.invoice to be unique. If you cannot guarantee that this value will be unique - think again about your models relationships and the data.
2. make SalesPayment.invoice a ForeignKey field to SalesInvoice. Read about what on_delete=PROTECT does and what other options there are: https://docs.djangoproject.com/en/4.0/ref/models/fields/#foreignkey
3. you don't need to explicitly add id fields. This is done by Django anyway.

class SalesInvoice(models.Model):
    date = models.DateField(default=date.today)
    invoice = models.CharField(max_length=20, unique=True) # changed
    customer = models.CharField(max_length=100)
    product_name = models.CharField(max_length=80)
    price = models.DecimalField(max_digits=9, decimal_places=2, default=0.00)
    quantity = models.IntegerField(default=0)
   

class SalesPayment(models.Model):
    date = models.DateField(default=date.today)
    invoice = models.ForeignKey(SalesInvoice, on_delete=PROTECT)  # changed
    amount = models.DecimalField(max_digits=9, decimal_places=2, default=0.00)
    discount = models.DecimalField(max_digits=9, decimal_places=2, default=0.00)
    payment = models.DecimalField(max_digits=9, decimal_places=2, default=0.00)

Django will automatically support the following lookups and much more:

# get all payments for an invoice (could be empty)
payments = salesInvoice.salespayment_set.all()  # this is a queryset

# get the invoice of a specific payment by date of a customer
payment = SalesPayment.objects.filter(customer=cust_pk, date=some_date).first()
if payment:
    invoice = payment.invoice.invoice # this is your identifying string