• Thu. Apr 25th, 2024

Using selected fields from ModelMultipleChoiceField to run a SQL query

ByKaren

Dec 17, 2021 , ,

I’m learning Django and I’m trying something that may be unorthodox. In forms.py, I have the following class:

class CompareFormClient(forms.ModelForm):
class Meta:
    model = OrderFile
    fields = ['origine']

items = forms.ModelMultipleChoiceField(
    widget=FilteredSelectMultiple(
        'items',
        False,
    ),
    queryset=OrderFile.objects.all().values_list('reference', flat=True)
)

In models.py, I have these two classes:

class OrderFile(models.Model):
    uploadedfile = models.ForeignKey(File, on_delete=models.CASCADE, default=1)
    ean = models.CharField(max_length=50)
    designation = models.TextField(blank=True)
    reference = models.CharField(max_length=200)
    quantite = models.IntegerField()
    prix_unitaire = models.IntegerField(blank=True)
    prix_total = models.IntegerField(blank=True)
    poids_unitaire = models.IntegerField(blank=True)
    poids_total = models.IntegerField(blank=True)
    composition = models.TextField(blank=True)
    origine = models.IntegerField(blank=True)
    nomenclature_douaniere = models.CharField(max_length=50)
    reference_transport = models.CharField(max_length=50)


class DeliveryFile(models.Model):
    uploadedfile = models.ForeignKey(File, on_delete=models.CASCADE, default=1, db_constraint=False)
    ean = models.CharField(max_length=50)
    designation = models.TextField(blank=True)
    reference = models.CharField(max_length=200)
    quantite = models.IntegerField()
    prix_unitaire = models.IntegerField(blank=True)
    prix_total = models.IntegerField(blank=True)
    poids_unitaire = models.IntegerField(blank=True)
    poids_total = models.IntegerField(blank=True)
    composition = models.TextField(blank=True)
    origine = models.IntegerField(blank=True)
    nomenclature_douaniere = models.CharField(max_length=50)
    reference_transport = models.CharField(max_length=50)

Both models OrderFile and DeliveryFile are already filled with data. In my form, I select values from the following queryset : queryset=OrderFile.objects.all().values_list('reference', flat=True).

My objective is to run a SQL query to find any differences with the selected values (from ModelMultipleChoiceField) between two tables. This SQL query would be the following:

SELECT
    id --want to use the selected values from the django form here
FROM
    foo
FULL OUTER JOIN bar USING (id)
WHERE
    foo.id IS NULL
OR bar.id IS NULL;

Do you have any idea on how I could do this? I would be really grateful if someone could help me with this request.