Runtime Foreign Key vs Integerfield-CodePudding

I have a problem. I already have two solution for my problem, but i was wondering which of those is the faster solution. I guess that the second solution is not only more convienient- to use but also faster, but i want to be sure, so thats the reason why im asking. My problem is i want to group multiple rows together. The group won't hold any meta data. So im only interested in runtime.

On the one hand i can use a Integer field and filter it later on when i need to get all entries that belong to the group. I guess runtime of O(n).

class SingleEntries(models.Model):
    name = models.CharField(max_length=20)
    group = models.IntegerField(null=True)


def find_all_group_members(id):
    return SingleEntries.objects.filter(group=id)

The second solution and probably the more practicle way would be to create a foreign key to another model only using the pk there. Then i can use the reverse relation to find all the entries that belong to the group.

class Group(models.Model):
    id = models.AutoField(primary_key=True)

class SingleEntries(models.Model):
    name = models.CharField(max_length=20)
    group = models.ForeignKey(Group,on_delete=models.CASCADE,null=True)

def find_all_group_members(id):
    return Group.objects.get(id=id).singleentries_set.all()

CodePudding user response：

The first is more efficient, since this will use one query, whereas the latter will first fetch the Group, and then another one for the SingleEntries.

Indeed, if you work with:

SingleEntries.objects.filter(group=id)

this will make a simple query:

SELECT appname_singleentries.*
FROM appname_singleentries
WHERE appname_singleentries.group_id = id

It thus does not first fetch the Group into memory.

The latter will however make two queries. Indeed, it will first make a query to retrieve the Group, and then it will make a query like the one above to fetch the SingleEntries.

The two are also semantically not entirely the same: if there is no such group, then the former will return an empty QuerySet, whereas the latter will raise a Group.DoesNotExists exception.

But you can model this with:

class Group(models.Model):
    pass

class SingleEntries(models.Model):
    name = models.CharField(max_length=20)
    group = models.ForeignKey(Group,on_delete=models.CASCADE,null=True)


def find_all_group_members(id):
    return SingleEntries.objects.filter(group_id=id)

So you can use a Group model without having to retrieve the Group first.

CodePudding user response：

If the groups are static in nature, that means if you don't see more groups coming to your system, you can use choices in Django.

Define choices as below

class GroupType(models.IntegerChoices):
    GROUP_0 = 0, "Group 0 name"
    GROUP_1 = 1, "Group 1 name"
    GROUP_2 = 2, "Group 2 name"

And use it as choices field in the SingleEntries model as below

class SingleEntries(models.Model):
    name = models.CharField(max_length=20)
    group = models.IntegerField(choices=GroupChoices.choices, default=<set default here>)

If the groups are dynamic, meaning users can create groups whenever they want, in that case, go with your second approach of having another model for group.