Django合并查询结果，并在postgres上对合并后的查询结果进行查询

Question

我正在尝试完成一个查询，以确定员工在一天中唯一的签入和签出次数，而不重复签入和签出，并选择最新的来区分。你知道吗

本质上是组合两个查询结果，并对结果执行一个查询。你知道吗

下面就简单多了。你知道吗

我有两种型号：

class Employee(models.Model):
    fullname = models.CharField(max_length=30, blank=False)    

class Attendance(models.Model):
    CHECKIN = 1
    CHECKOUT = 2
    ATTENDANCE_TYPE_CHOICES = (
        (CHECKIN, "Check In"),
        (CHECKOUT, "Check Out"),
    )
    employee = models.ForeignKey(Employee)
    activity_type = models.IntegerField(choices = ATTENDANCE_TYPE_CHOICES, default=CHECKIN)
    timestamp = models.DateTimeField(auto_now_add=True)

我有两个查询来获取考勤表中员工的唯一签入和签出。你知道吗

unique_employee_attendance_checkin_today = Attendance.objects.filter(company=auth_employee.employee.company.id,
                                    activity_type=Attendance.CHECKIN,
                                    timestamp__date = today).order_by(
                                    'employee_id','-timestamp').distinct('employee_id')

unique_employee_attendance_checkout_today = Attendance.objects.filter(company=auth_employee.employee.company.id,
                                    activity_type=Attendance.CHECKOUT,
                                    timestamp__date = today).order_by(
                                    'employee_id','-timestamp').distinct('employee_id')

假设我有这些记录

Employee
{"id":1, "employee":"michael jackson",
"id":2, "fullname":"mariah carey",
"id":3, "fullname":"taylor swift"}

Attendance
{"id":1, "employee": 1,"activity_type": 1, timestamp: "2017-12-05 09:08", 
"id":2, "employee": 2,"activity_type": 1, timestamp: "2017-12-05 10:13",
"id":3, "employee": 3,"activity_type": 1, timestamp: "2017-12-05 11:30",
"id":4, "employee": 2,"activity_type": 2, timestamp: "2017-12-05 15:13", 
"id":5, "employee": 3,"activity_type": 2, timestamp: "2017-12-05 18:30", 
"id":6, "employee": 2,"activity_type": 1, timestamp: "2017-12-05 19:13", 
"id":7, "employee": 1,"activity_type": 2, timestamp: "2017-12-06 08:08"}

今日唯一员工考勤签入-将输出此结果

{"id":4, "employee": 2,"activity_type": 2, timestamp: "2017-12-05 15:13", 
"id":5, "employee": 3,"activity_type": 2, timestamp: "2017-12-05 18:30"}

今日唯一员工出勤结帐-将输出此结果

{"id":1, "employee": 1,"activity_type": 1, timestamp: "2017-12-05 09:08", 
"id":6, "employee": 2,"activity_type": 1, timestamp: "2017-12-05 19:13"}

1. 第一个问题是：

我想合并这两个查询

unique_employee_attendance_checkin_today + unique_employee_attendance_checkout_today

以及

我想为雇员创建一个distinct，它将删除2条记录，因此输出结果如下所示。你知道吗

{"id":1, "employee": 1,"activity_type": 1, timestamp: "2017-12-05 09:08", 
"id":5, "employee": 3,"activity_type": 2, timestamp: "2017-12-05 18:30"
"id":6, "employee": 2,"activity_type": 1, timestamp: "2017-12-05 19:13"}

1. 第二个问题是：

然后根据activity_type=1（签入）和activity_type=2（签出）的结果执行查询

上面的问题是，如何组合2个查询结果，并根据上述要求对组合查询进行查询。你知道吗

Answer 1

组合查询（每个员工/日期的最新出勤率，无论类型如何）可以完全不使用activity_type：

unique_employee_attendance_today = Attendance.objects.filter(
    company=auth_employee.employee.company.id,
    timestamp__date = today).order_by(
        'employee_id','-timestamp'
    ).distinct('employee_id')

现在，您可以在嵌套查询中从该集合中筛选签入，如：

Attendance.objects.filter(
    id__in=unique_employee_attendance_today,
    activity_type=Attendance.CHECKIN
)

这只会导致一个数据库查询。你知道吗