CODEWITHSUNDEEP
pythonmysqldjangodjango-modelsdjango-orm
aman
aman

Reputation: 393

Django select count of same foreign keys within the same model

I have this model:

class Content(models.Model):
    id = models.CharField(max_length=20, null=False, blank=False, primary_key=True, unique=True)

class File(models.Model):
    id = models.CharField(max_length=21, null=False, blank=False,
                          primary_key=True, unique=True)
    id_content = models.ForeignKey(Content, db_column="id_content",
                                   on_delete=models.CASCADE)

I want to select the id and the count of other files with the same Foreign Key as this id. I literally have got no clue about where to define such query inside filter, extra etc.

I have done this previously inside this MySQL query, but I'm looking for a Django Model solution:

SELECT
    f1.`id` AS id,
    (SELECT COUNT(f2.`id_content`)
     FROM `file` AS f2 WHERE f2.`id_content` = f1.`id_content`) AS total
FROM
    `file` AS f1;

Upvotes: 1

Views: 361

Answers (1)

Selcuk
Selcuk

Reputation: 59184

If you have a File object (e.g. my_file):

my_file.id_content.file_set.count()

if you only know the id you should get the File instance first:

my_file = File.objects.get(id=id)

and apply the first method.

That being said, do not start your ForeignKey field names with id_. It makes your code confusing. It would be a better practice to name it content, i.e.

content = models.ForeignKey(Content, ...)

Upvotes: 1

Related Questions