Django select count of same foreign keys within the same model

Question

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;

Currently, I haven't built any statement, waiting for a query to count the same foreign key. Currently, I only have this: File.objects.all().values("id") — aman
– aman, Commented Jul 14, 2021 at 4:59

Selcuk · Accepted Answer · 2021-07-14 05:01:11Z

1

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, ...)

answered Jul 14, 2021 at 5:01

Selcuk

60.1k12 gold badges114 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

aman Over a year ago

I have updated my post and defined the Content model. Hope that helps. I just want to print the id and count of same foreign keys same time as my MySQL Query. ``

Collectives™ on Stack Overflow

Django select count of same foreign keys within the same model

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related