CODEWITHSUNDEEP
djangodjango-modelsorm
Rupin
Rupin

Reputation: 620

How to query by joining a Django Model with other, on a non unique column?

I have the following models in my models.py file in my django project 

from django.contrib.auth.models import AbstractUser
from django.db import models
from django.conf import settings

class CustomUser(AbstractUser):
    pass
    # add additional fields in here

class PDFForm(models.Model):

    pdf_type=models.IntegerField(default=0)
    pdf_name=models.CharField(max_length=100,default='')
    file_path=models.FileField(default='')  

class FormField(models.Model):

    fk_pdf_id=models.ForeignKey('PDFForm', on_delete=models.CASCADE,default=0)
    field_type=models.IntegerField(default=0)
    field_page_number=models.IntegerField(default=0)
    field_x=models.DecimalField(max_digits=6,decimal_places=2,default=0)
    field_y=models.DecimalField(max_digits=6,decimal_places=2,default=0)
    field_x_increment=models.DecimalField(max_digits=6,decimal_places=2,default=0)
    class Meta:
        ordering= ("field_page_number", "field_type")

class UserData(models.Model):

    fk_user_id=models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE,default=0)
    field_type=models.IntegerField(default=0)
    field_text=models.CharField(max_length=200,default='')
    field_date=models.DateField()

Here is how the models are related

1) a pdfform contains a pdf form and path for it on the file system

2) A pdfform has multiple FormFields in it. Each field has attributes, and the specific one under discussion is field_type

3)The UserData model has user's data, so one User can have multiple rows in this table. This model also has the field_type column.

What I am trying to query is to find out all rows present in the Userdata Model which are present in the FormField Model ( matched with field_type) and that are of a specific PDFForm.

Given that the Many to Many relationship in django models cannot happen between no unique fields, how would one go about making a query like below

select a.*, b.* from FormField a, UserData b where b.fk_user_id=1 and a.fk_pdf_id=3 and a.field_type=b.field_type

I have been going through the documentation with a fine toothed comb, but obviously have been missing how django creates joins. what is the way to make the above sql statement happen, so I get the required dataset?

Upvotes: 0

Views: 125

Answers (1)

Krukas
Krukas

Reputation: 677

I think UserData is missing a relation to FormField, but if you had this relation you could do:

  UserData.objects.filter(
    fk_user_id=1, # Rename this to user, Django wilt automicly create a user_id column
    form_field__in=FormField.objects.filter(
      fk_pdf_id=<your pdfid>  # same as fk_user_id 
    )
  )

Edit updated models

When you use a ForeignKey you don't have to specify the _id or default=0, if you don't always want to fill the field its better to set null=True and blank=True 

from django.contrib.auth.models import AbstractUser
from django.db import models
from django.conf import settings

class CustomUser(AbstractUser):
    pass
    # add additional fields in here

class FieldTypeMixin:
    TYPE_TEXT = 10
    TYPE_DATE = 20

    TYPE_CHOISES = [
        (TYPE_TEXT, 'Text'),
        (TYPE_DATE, 'Date'),
    ]

    field_type=models.IntegerField(default=TYPE_TEXT, choises=TYPE_CHOISES)

class PDFForm(models.Model):
    pdf_type = models.IntegerField(default=0)
    pdf_name = models.CharField(max_length=100,default='')
    file_path = models.FileField(default='')  

class FormField(models.Model, FieldTypeMixin):
    pdf_form = models.ForeignKey('PDFForm', on_delete=models.CASCADE)
    field_page_number = models.IntegerField(default=0)
    field_x = models.DecimalField(max_digits=6,decimal_places=2,default=0)
    field_y = models.DecimalField(max_digits=6,decimal_places=2,default=0)
    field_x_increment = models.DecimalField(max_digits=6,decimal_places=2,default=0)

    class Meta:
        ordering = ("field_page_number", "field_type")

class SubmittedForm(models.Model):
    user = models.ForeignKey(settings.AUTH_USER_MODEL, models.CASCADE)
    pdf_form = models.ForeignKey(PDFForm, models.CASCADE)

class SubmittedFormField(models.Model, FieldTypeMixin):
    submitted_form = models.ForeignKey(SubmittedForm, models.CASCADE)
    form_field = models.ForeignKey(FormField, models.CASCADE, related_name='fields')

    field_text = models.CharField(max_length=200,default='')
    field_date = models.DateField()

    class Meta:
        unique_together = [
            ['submitted_form', 'form_field']
        ]

Upvotes: 2

Related Questions