SQL Query to combine alterative rows into a single table using Django's Models

I have created an attendance server that allows staffs to log their attendance.
There is a manual and a automatic way of logging attendance. The SQL tables for the system is as follows.

Anytime a biometrics log is added, if the local user in that log has a staff foreign key then a punch log gets created. I felt that this helps in combining automatic and manual logs. Manual Logs are added straight to Punch Log

When the user is requesting for Attendance. This currently will take alterative rows of punch log that are active and combines it into a single row.

Output Data Format

CODE IN USE

Currently I am using raw sql query to get the data

The basic logic is to

• Split into two sub queries -> Punch In & Punch Out with Roll Number Unique to User
• Join with Roll Number

    select i.id, username, i.punched_on as punch_in,  min(punch_out) as punch_out
    from (select *,row_number() over (partition by user_id order by user_id) as r_index from attendance_punch_log) as i
    left join local_user on i.user_id = local_user.id
    left join (
    select  o.logged_on as punch_out, username as out_user
    from (select *, row_number() over (partition by user_id order by user_id) as l_index from attendance_punch_log) as o
    left join local_user on o.user_id = local_user.id) as o on username = out_user and punch_out > punch_in
    where mod(r_index, 2) = 1
    group by username , r_index
    order by punch_in, punch_out

The Django models are as follows

class BiometricsDevice(models.Model):
    location = models.ForeignKey(
        Location, on_delete=models.CASCADE, related_name="device"
    )
    description = models.CharField(max_length=120)
    api_key = models.CharField(max_length=20, null=False, blank=False, unique=True)
    last_sync = models.DateTimeField(null=True)
    sync_user = models.BooleanField(default=True)
    is_active = models.BooleanField(default=True)

    created_on = models.DateField(auto_now_add=True)
    created_by = models.ForeignKey(
        User,
        on_delete=models.CASCADE,
        related_name="created_biometrics",
    )

    def __str__(self):
        return f"{self.location.name} - {self.api_key}"


class LocalUser(models.Model):
    ref = models.PositiveIntegerField()
    name = models.CharField(max_length=20)
    device = models.ForeignKey(
        BiometricsDevice, on_delete=models.CASCADE, related_name="users"
    )
    # Signal on staff update -> Re-Run Biometrics Log to Punch Log Conversion
    staff = models.ForeignKey(
        Staff,
        on_delete=models.CASCADE,
        related_name="biometric_user",
        blank=True,
        null=True,
    )

    def __str__(self):
        return f"{self.name} - {self.device}"

    class Meta:
        unique_together = (["name", "device"], ["name", "ref", "device"])


class PunchLog(models.Model):
    staff = models.ForeignKey(
        Staff,
        on_delete=models.CASCADE,
        related_name="punch_log",
    )
    punched_on = models.DateTimeField()
    is_active = models.BooleanField(default=True)

    created_on = models.DateField(auto_now_add=True)
    created_by = models.ForeignKey(
        User,
        on_delete=models.CASCADE,
        related_name="created_punch_logs",
        null=True,
        blank=True,
    )

    def __str__(self):
        return f"{self.staff.name} - {self.punched_on}"


class BiometricsLog(models.Model):
    user = models.ForeignKey(
        LocalUser,
        on_delete=models.CASCADE,
        related_name="biometrics_log",
    )
    punched_on = models.DateTimeField()

    punch_log = models.OneToOneField(
        PunchLog,
        on_delete=models.CASCADE,
        related_name="biometrics_log",
        null=True,
        blank=True,
    )

    class Meta:
        unique_together = ["user", "punched_on"]

    def __str__(self):
        return f"{self.user.name} - {self.punched_on}"

The Code to create queryset is as follows

    def get(self, request):
        punch_in = (
            BiometricsLog.objects.annotate(
                index=Window(
                    expression=RowNumber(),
                    order_by=F("user_id").asc(),
                    partition_by=[F("user_id")],
                ),
            )
            .annotate(evenindex=F("index") % 2)
            .filter(evenindex=1)
            .order_by("-punched_on")
        )
        punch_out = (
            BiometricsLog.objects.annotate(
                index=Window(
                    expression=RowNumber(),
                    order_by=F("user_id").asc(),
                    partition_by=[F("user_id")],
                ),
            )
            .annotate(evenindex=F("index") % 2)
            .filter(evenindex=0)
            .order_by("-punched_on")
        )
        # Odd Even index join

        queryset = punch_in.annotate(
            punch_in=F("punched_on"),
            punch_out=Subquery(
                punch_out.filter(
                    index__gt=OuterRef("index"),
                    user_id=OuterRef("user_id"),
                ).values("punched_on")[:1]
            ),
            staff=F("user__staff"),
        )

Final Thoughts

• What other method should i be using to create attendance table in the end ?
• Is it better to create a new table insted of a querset ?

It seems to me that the method of determining "in" or "out" in the punch log is overly complicated. This certainly has maintenance issues and could also affect performance.

You could simply add a direction field (or even just a boolean) to your punch log model. It would make the queries and everything else much simpler and more readable.

class PunchLog(models.Model):
...
is_in = models.BooleanField(default=True)

It might make sense make a similar adjustment to the other log model as well.

EDIT

However, perhaps you want to review your entire model architecture. I think that both logs are similar enough to be the practically the same thing. Also, I think it is conceptually not correct to make two log entries in certain not obvious conditions, when only one event occurs. With a simpler architecture, the queries become trivial.

How about combining your logs?

class LogEntry(models.Model):
local_user = models.ForeignKey(...)
created_on = models.DateTimeField(auto_now_add=True)
is_in = models.BooleanField(...)
is_biometric = models.BooleanField(...)