CODEWITHSUNDEEP
pythonsqldjangopostgresql
Marin
Marin

Reputation: 1121

Django model query

I have question about Django query models. I know how to write simple query, but Im not familiar with LEFT JOIN on two tables. So can you give me some advice on his query for better understanding DJango ORM.

query

select
    count(ips.category_id_id) as how_many,
    ic.name
from
    izibizi_category ic
left join
    izibizi_product_service ips
on
    ips.category_id_id = ic.id
where ic.type_id_id = 1 
group by ic.name, ips.category_id_id

From this query I get results:

How many | name
0;"fghjjh"
0;"Papir"
0;"asdasdas"
0;"hhhh"
0;"Boljka"
0;"ako"
0;"asd"
0;"Čokoladne pahuljice"
0;"Mobitel"
2;"Čokolada"

And I have also try with his Django query:

a = Category.objects.all().annotate(Count('id__category',distinct=True)).filter(type_id=1)

But no results.

My models:

models.py

class Category(models.Model):
    id = models.AutoField(primary_key=True)
    type_id = models.ForeignKey('CategoryType')
    name = models.CharField(max_length=255)


    def __str__(self):
        return str(self.name)


class Product_service(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=255, blank=True, null=True)
    selling_price = models.DecimalField(decimal_places=5, max_digits=255, blank=True, null=True)
    purchase_price = models.DecimalField(decimal_places=5, max_digits=255, blank=True, null=True)
    description = models.CharField(max_length=255, blank=True, null=True)
    image = models.FileField(upload_to="/", blank=True, null=True)
    product_code = models.CharField(max_length=255, blank=True, null=True)
    product_code_supplier = models.CharField(max_length=255, blank=True, null=True)
    product_code_buyer = models.CharField(max_length=255, blank=True, null=True)
    min_unit_state = models.CharField(max_length=255, blank=True, null=True)
    state = models.CharField(max_length=255, blank=True, null=True)
    vat_id = models.ForeignKey('VatRate')
    unit_id = models.ForeignKey('Units')
    category_id = models.ForeignKey('Category')

If you culd help me on this problem.

Upvotes: 1

Views: 303

Answers (1)

Fanis Despoudis
Fanis Despoudis

Reputation: 386

You should add a related name on the category_id field like:

category_id = models.ForeignKey('Category', related_name="product_services")

so that in your query you can do:

a = Category.objects.all().annotate(Count('product_services',distinct=True)).filter(type_id=1)

and then you can access the individual counts as:

a[0].product_services__count

Upvotes: 1

Related Questions