Django Raw Query wants ID but sstill doesent work-CodePudding

im a new Programmer and i have a issue with Django. I wrote a raw Query but it wont work. Can anynone help me please?

This are my Models:

class Kunde(models.Model):

    kunden_id = models.IntegerField(db_column='Kunden_ID', primary_key=True)
    vertragspartner = models.CharField(db_column='Vertragspartner', max_length=45, blank=True,                        null=True)
    labor = models.CharField(db_column='Labor', max_length=45, blank=True, null=True)
  
    def __str__(self):
        return str(self.kunden_id)

    class Meta:
        managed = True
        db_table = 'kunde'

class Objekte(models.Model):

    objekt_id = models.CharField(db_column='Objekt_ID', primary_key=True, max_length=50)
    rechnungsempfaenger = models.CharField(db_column='Rechnungsempfaenger', max_length=50, blank=True, null=True)  
    kundecenter = models.CharField(db_column='Kundecenter', max_length=50, blank=True, null=True)  
    twa_nr = models.IntegerField(db_column='TWA_Nr', blank=True, null=True) 
    karte = models.IntegerField(db_column='Karte', blank=True, null=True)

    def __str__(self):
        return self.objekt_id

    class Meta:
        managed = True
        db_table = 'objekte'
        ordering =['objekt_id']

The Objekte Model is a bit bigger but i cut it so it is a bit "prettier".

My Views looks like this

def kunde_all (request):

    sql = "SELECT **kunde.kunden_id as id**, kunde.Vertragspartner, kunde.Labor, objekte.Objekt_ID, COUNT(objekte.Objekt_ID) AS anzahl FROM kunde LEFT JOIN objekte ON kunde.kunden_id = objekte.kunden_id GROUP BY kunde.kunden_id"

    sql_data= Kunde.objects.raw(sql)
    p = Paginator(Kunde.objects.raw(sql_data), 20)
    page = request.GET.get('page')
    kunden=p.get_page(page)
    context={
        'kunden': kunden,
    }
    return render(request,"kunde.html", context=context)

What im trying to do here is that i want to count the objects in Objekte that belong to the Kunde and give them in a Tabel back. The problem is that i always get the Message "Raw query must include the primary key" i searched it up and the most common solution was to ad as id in my Query. I did it but it still wont work for me ...

I tried to do this with a left join i dont know how to do it in the Django way so i used a Raw Query if you could help me with that too i would realy preciate it.

Also my english isnt that good if u didnt understant my question or i forgett something please let me know.

CodePudding user response：

I think you have declared Kunden_ID as your primary key, but you are trying to use kunden_id in your query.

Try using this in you query wherever you address Kunden_ID:

SELECT
    kunde."Kunden_ID" as id,
    kunde."Vertragspartner",
    kunde.Labor, objekte."Objekt_ID",
    COUNT(objekte."Objekt_ID") AS anzahl
FROM kunde
LEFT JOIN objekte ON kunde.kunden_id = objekte."Kunden_ID"
GROUP BY kunde."Kunden_ID"

CodePudding user response：

You should declare Objekte.kunden_id as a foreign key (by default, for a FK field kunden, the backing field is kunden_id, so this works out):

class Objekte(models.Model):
    # ...
    kunden = models.ForeignKey(Kunden, on_delete=models.PROTECT)

Then, you don't need raw SQL at all (though I elided the objekt_id field from this, since its value would be indeterminate thanks to the group by):

qs = (
    Kunde.objects.all()
    .annotate(anzahl=Count("objekte_set"))
    .values_list("kunden_id", "Vertragspartner", "Labor", "anzahl")
)

Iterating qs will then yield 4-tuples.