我正在尝试找到一个查询,以获取特定日期的最新出勤列表,而独特的员工已签入(checkin = 1)
以下是我的模型,记录和我要完成的目标。
我的型号有2个模型:
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)
假设我有以下记录:
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", -interested in this for the activity type (1 for CHECKIN) and the date 2017-12-05, last of that employee id for that day
"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", -interested in this for the activity type (1 for CHECKIN) and the date 2017-12-05, last of that employee id for that day
"id":7, "employee": 3,"activity_type": 1, timestamp: "2017-12-05 20:30", -interested in this for the activity type (1 for CHECKIN) and the date 2017-12-05, last of that employee id for that day
"id":8, "employee": 1,"activity_type": 2, timestamp: "2017-12-06 08:08"}
我是兴趣,因此它仅根据Checkin的活动类型1和2017-12-05的活动类型1输出这2个记录,并且该员工ID必须是唯一且最新的:
{"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"
"id":7, "employee": 3,"activity_type": 1, timestamp: "2017-12-05 20:30"}
我正在使用Postgres 9.4
如何使用django queryset产生预期的结果?
activity_type
的过滤出勤率, employee_id
和 -timestamp
订购,然后在 employee_id
Attendance.objects.filter(activity_type=Attendance.CHECKIN).order_by('employee_id','-timestamp').distinct('employee_id')