I am using Go as the back end and MongoDB as the database and use beego framework to develop this application. I would like to do some pagination in my front end.
My struct looks like this:
type Employee struct {
Name string
EmpId string
Password string
PhoneNumber int32
EmailAddress string
Position string
AccessLevel string
Gender string
MaritalStatus string
Nationality string
Department string
ICNumber string
JoinDate time.Time
ConfirmationDate time.Time
EndDate time.Time
AnnualLeave []*AnnualLeaveInfo
MedicalLeave []*MedicalLeaveInfo
NopayLeave []*NopayLeaveInfo
ChildcareLeave []*ChildcareLeaveInfo
}
type AnnualLeaveInfo struct {
Id int
Days float64
Type string
From time.Time
To time.Time
AppliedDate time.Time
Status string
Certificate []*CertificateInfo
}
The other leave info as same as the annual leave info. I would like to show all the leaves of all the employee documents sorted by applied date and status. Right now I'm retrieving all the leave details and then appending in array and then I am using the cursor value (last retrieved index value of the array which I pass to the front end in the previous response) and finding the values from the array. I know that this is not a good function because for every request it needs to retrieve all the leave details for retrieving only 20 leave details which is requested by the front end.
My Results after appending in the array is like this
"0": {
"LeaveEmpId": "rajeshk",
"Name": "rahul",
"LeaveId": 8,
"LeaveType": "annualleave",
"LeaveTotal": 2,
"LeaveDays": 1,
"LeaveFrom": "2016-12-12T08:00:00+08:00",
"LeaveTo": "2016-12-12T08:00:00+08:00",
"LeaveAppliedDate": "2016-11-21T10:29:33.713+08:00",
"LeaveStatus": "Processing",
"CertificateName": [
"1.pdf",
"2.pdf"
]
},
"1": {
"LeaveEmpId": "rajeshk",
"LeaveName": "Rajesh",
"LeaveId": 7,
"LeaveType": "medicalleave",
"LeaveTotal": 3,
"LeaveDays": 1,
"LeaveFrom": "2016-12-12T08:00:00+08:00",
"LeaveTo": "2016-12-12T08:00:00+08:00",
"LeaveAppliedDate": "2016-11-21T10:27:06.12+08:00",
"LeaveStatus": "Processing",
"CertificateName": [
"1.pdf",
"2.pdf"
]
},
"2": {
"LeaveEmpId": "kumar",
"LeaveName": "madia",
"LeaveId": 6,
"LeaveType": "nopayleave",
"LeaveTotal": 4,
"LeaveDays": 1,
"LeaveFrom": "2016-12-12T08:00:00+08:00",
"LeaveTo": "2016-12-12T08:00:00+08:00",
"LeaveAppliedDate": "2016-11-21T10:11:58.283+08:00",
"LeaveStatus": "Processing",
"CertificateName": [
"1.pdf",
"2.pdf"
]
},
"3": {
"LeaveEmpId": "master",
"LeaveName": "kumar",
"LeaveId": 5,
"LeaveType": "nopayleave",
"LeaveTotal": 5,
"LeaveDays": 1,
"LeaveFrom": "2016-12-12T08:00:00+08:00",
"LeaveTo": "2016-12-12T08:00:00+08:00",
"LeaveAppliedDate": "2016-11-21T10:02:17.565+08:00",
"LeaveStatus": "Processing",
"CertificateName": [
"1.pdf",
"2.pdf"
]
},
"4": {
"LeaveEmpId": "rajeshk",
"LeaveName": "Rajesh",
"LeaveId": 4,
"LeaveType": "medicalleave",
"LeaveTotal": 6,
"LeaveDays": 1,
"LeaveFrom": "2016-12-12T08:00:00+08:00",
"LeaveTo": "2016-12-12T08:00:00+08:00",
"LeaveAppliedDate": "2016-11-21T09:52:20.185+08:00",
"LeaveStatus": "Processing",
"CertificateName": [
"1.pdf",
"2.pdf"
]
},
"5": {
"LeaveEmpId": "rajeshk",
"LeaveName": "Rajesh",
"LeaveId": 3,
"LeaveType": "annualleave",
"LeaveTotal": 7,
"LeaveDays": 1,
"LeaveFrom": "2016-12-12T08:00:00+08:00",
"LeaveTo": "2016-12-12T08:00:00+08:00",
"LeaveAppliedDate": "2016-11-21T09:49:18.702+08:00",
"LeaveStatus": "Processing",
"CertificateName": [
"1.pdf",
"2.pdf"
]
},
"6": {
"LeaveEmpId": "india",
"LeaveName": "rahul",
"LeaveId": 2,
"LeaveType": "nopayleave",
"LeaveTotal": 8,
"LeaveDays": 1,
"LeaveFrom": "2016-12-12T08:00:00+08:00",
"LeaveTo": "2016-12-12T08:00:00+08:00",
"LeaveAppliedDate": "2016-11-21T09:30:14.99+08:00",
"LeaveStatus": "Processing",
"CertificateName": [
"1.pdf",
"2.pdf"
]
},
"7": {
"LeaveEmpId": "rajeshk",
"LeaveName": "Rajesh",
"LeaveId": 1,
"LeaveType": "medicalleave",
"LeaveTotal": 9,
"LeaveDays": 1,
"LeaveFrom": "2016-12-12T08:00:00+08:00",
"LeaveTo": "2016-12-12T08:00:00+08:00",
"LeaveAppliedDate": "2016-11-21T09:28:02.785+08:00",
"LeaveStatus": "Processing",
"CertificateName": [
"1.pdf",
"2.pdf"
]
}
In the database my empid is only index I use which is unique other values are not unique. Leave id is unique only with in the leave array. Can someone help me how to do this efficiently. Appreciate any help. Thanks.
Added Info:
My Database Structure looks like this ...This is just the info of one employee. Like this I have lots of employee documents. What I need is to get the total leave details of all these employee documents with the limit of 20 for pagination sorted by applieddate and status. Thanks
{
"_id" : ObjectId("58320cec720fed31a3243683"),
"name" : "Rajesh",
"empid" : "rajeshk",
"password" : "c1e1a43a3d65ff4d669f2831b9908acb9e2f755c48280e99f0d7a3e92592fa29",
"phonenumber" : 0,
"emailaddress" : "example@example.com.sg",
"position" : "string",
"accesslevel" : "string",
"gender" : "string",
"maritalstatus" : "string",
"nationality" : "india",
"department" : "it",
"icnumber" : "string",
"childcitizenship" : "string",
"joindate" : ISODate("2015-11-21T00:00:00.000Z"),
"confirmationdate" : Date(-62135596800000),
"enddate" : Date(-62135596800000),
"totalchild" : 0,
"annualleave" : [
{
"id" : 0,
"days" : 0.0,
"type" : "",
"from" : Date(-62135596800000),
"to" : Date(-62135596800000),
"applieddate" : Date(-62135596800000),
"status" : "",
"approveddate" : Date(-62135596800000),
"certificate" : []
}
],
"medicalleave" : [
{
"id" : 0,
"days" : 0.0,
"type" : "",
"from" : Date(-62135596800000),
"to" : Date(-62135596800000),
"applieddate" : Date(-62135596800000),
"status" : "",
"approveddate" : Date(-62135596800000),
"certificate" : []
}
],
"nopayleave" : [
{
"total" : 10.0,
"id" : 0,
"days" : 0.0,
"type" : "",
"from" : Date(-62135596800000),
"to" : Date(-62135596800000),
"applieddate" : Date(-62135596800000),
"status" : "",
"approveddate" : Date(-62135596800000),
"certificate" : []
},
{
"total" : 9.0,
"id" : 1,
"days" : 1.0,
"mdays" : 0.0,
"type" : "nopayleave",
"daytype" : "FullDay",
"from" : ISODate("2016-12-12T00:00:00.000Z"),
"to" : ISODate("2016-12-12T00:00:00.000Z"),
"applieddate" : ISODate("2016-11-21T01:28:02.785Z"),
"status" : "Processing",
"approveddate" : Date(-62135596800000),
"certificatename" : "",
"certificate" : [
{
"filename" : "Rajesh",
"filetype" : ".zip",
"filesize" : 1234
},
{
"filename" : "Rajesh",
"filetype" : ".zip",
"filesize" : 1234
}
]
},
{
"total" : 8.0,
"id" : 2,
"days" : 1.0,
"mdays" : 0.0,
"type" : "nopayleave",
"daytype" : "FullDay",
"from" : ISODate("2016-12-12T00:00:00.000Z"),
"to" : ISODate("2016-12-12T00:00:00.000Z"),
"applieddate" : ISODate("2016-11-21T01:30:14.990Z"),
"status" : "Processing",
"approveddate" : Date(-62135596800000),
"certificatename" : "",
"certificate" : [
{
"filename" : "Rajesh",
"filetype" : ".zip",
"filesize" : 1234
},
{
"filename" : "Rajesh",
"filetype" : ".zip",
"filesize" : 1234
}
]
},
{
"total" : 7.0,
"id" : 3,
"days" : 1.0,
"mdays" : 0.0,
"type" : "nopayleave",
"daytype" : "FullDay",
"from" : ISODate("2016-12-12T00:00:00.000Z"),
"to" : ISODate("2016-12-12T00:00:00.000Z"),
"applieddate" : ISODate("2016-11-21T01:49:18.702Z"),
"status" : "Processing",
"approveddate" : Date(-62135596800000),
"certificatename" : "",
"certificate" : [
{
"filename" : "Rajesh",
"filetype" : ".zip",
"filesize" : 1234
},
{
"filename" : "Rajesh",
"filetype" : ".zip",
"filesize" : 1234
}
]
},
{
"total" : 6.0,
"id" : 4,
"days" : 1.0,
"mdays" : 0.0,
"type" : "nopayleave",
"daytype" : "FullDay",
"from" : ISODate("2016-12-12T00:00:00.000Z"),
"to" : ISODate("2016-12-12T00:00:00.000Z"),
"applieddate" : ISODate("2016-11-21T01:52:20.185Z"),
"status" : "Processing",
"approveddate" : Date(-62135596800000),
"certificatename" : "",
"certificate" : [
{
"filename" : "Rajesh",
"filetype" : ".zip",
"filesize" : 1234
},
{
"filename" : "Rajesh",
"filetype" : ".zip",
"filesize" : 1234
}
]
},
{
"total" : 5.0,
"id" : 5,
"days" : 1.0,
"mdays" : 0.0,
"type" : "nopayleave",
"daytype" : "FullDay",
"from" : ISODate("2016-12-12T00:00:00.000Z"),
"to" : ISODate("2016-12-12T00:00:00.000Z"),
"applieddate" : ISODate("2016-11-21T02:02:17.565Z"),
"status" : "Processing",
"approveddate" : Date(-62135596800000),
"certificatename" : "",
"certificate" : [
{
"filename" : "Rajesh",
"filetype" : ".zip",
"filesize" : 1234
},
{
"filename" : "Rajesh",
"filetype" : ".zip",
"filesize" : 1234
}
]
},
{
"total" : 4.0,
"id" : 6,
"days" : 1.0,
"mdays" : 0.0,
"type" : "nopayleave",
"daytype" : "FullDay",
"from" : ISODate("2016-12-12T00:00:00.000Z"),
"to" : ISODate("2016-12-12T00:00:00.000Z"),
"applieddate" : ISODate("2016-11-21T02:11:58.283Z"),
"status" : "Processing",
"approveddate" : Date(-62135596800000),
"certificatename" : "",
"certificate" : [
{
"filename" : "Rajesh",
"filetype" : ".zip",
"filesize" : 1234
},
{
"filename" : "Rajesh",
"filetype" : ".zip",
"filesize" : 1234
}
]
},
{
"total" : 3.0,
"id" : 7,
"days" : 1.0,
"mdays" : 0.0,
"type" : "nopayleave",
"daytype" : "FullDay",
"from" : ISODate("2016-12-12T00:00:00.000Z"),
"to" : ISODate("2016-12-12T00:00:00.000Z"),
"applieddate" : ISODate("2016-11-21T02:27:06.120Z"),
"status" : "Processing",
"approveddate" : Date(-62135596800000),
"certificatename" : "",
"certificate" : [
{
"filename" : "Rajesh",
"filetype" : ".zip",
"filesize" : 1234
},
{
"filename" : "Rajesh",
"filetype" : ".zip",
"filesize" : 1234
}
]
},
{
"total" : 2.0,
"id" : 8,
"days" : 1.0,
"mdays" : 0.0,
"type" : "nopayleave",
"daytype" : "FullDay",
"from" : ISODate("2016-12-12T00:00:00.000Z"),
"to" : ISODate("2016-12-12T00:00:00.000Z"),
"applieddate" : ISODate("2016-11-21T02:29:33.713Z"),
"status" : "Processing",
"approveddate" : Date(-62135596800000),
"certificatename" : "",
"certificate" : [
{
"filename" : "Rajesh",
"filetype" : ".zip",
"filesize" : 1234
},
{
"filename" : "Rajesh",
"filetype" : ".zip",
"filesize" : 1234
}
]
}
],
"childcareleave" : [
{
"total" : 10.0,
"id" : 0,
"days" : 0.0,
"type" : "",
"from" : Date(-62135596800000),
"to" : Date(-62135596800000),
"applieddate" : Date(-62135596800000),
"status" : "",
"approveddate" : Date(-62135596800000),
"certificate" : []
}
],
"hospitalleave" : [
{
"total" : 10.0,
"id" : 0,
"days" : 0.0,
"mdays" : 0.0,
"type" : "",
"daytype" : "",
"from" : Date(-62135596800000),
"to" : Date(-62135596800000),
"applieddate" : Date(-62135596800000),
"status" : "",
"approveddate" : Date(-62135596800000),
"certificatename" : "string",
"certificate" : []
}
],
"maternityleave" : [
{
"total" : 10.0,
"id" : 0,
"days" : 0.0,
"mdays" : 0.0,
"type" : "",
"daytype" : "",
"from" : Date(-62135596800000),
"to" : Date(-62135596800000),
"applieddate" : Date(-62135596800000),
"status" : "",
"approveddate" : Date(-62135596800000),
"certificate" : []
}
],
"paternityleave" : [
{
"total" : 0.0,
"id" : 0,
"days" : 0.0,
"mdays" : 0.0,
"type" : "",
"daytype" : "",
"from" : Date(-62135596800000),
"to" : Date(-62135596800000),
"applieddate" : Date(-62135596800000),
"status" : "",
"approveddate" : Date(-62135596800000),
"certificate" : []
}
],
"compassionateleave" : [
{
"total" : 0.0,
"id" : 0,
"days" : 0.0,
"type" : "",
"from" : Date(-62135596800000),
"to" : Date(-62135596800000),
"applieddate" : Date(-62135596800000),
"status" : "",
"approveddate" : Date(-62135596800000),
"certificate" : []
}
],
"marriageleave" : [
{
"total" : 0.0,
"id" : 0,
"days" : 0.0,
"type" : "",
"from" : Date(-62135596800000),
"to" : Date(-62135596800000),
"applieddate" : Date(-62135596800000),
"status" : "",
"approveddate" : Date(-62135596800000),
"certificate" : []
}
],
"otherleave" : []
}
Being as it is, you can't really achieve a much more efficient pagination, since all the documents you want to list and paginate are in a single document, separated into multiple slices/arrays.
To achieve efficient and simple pagination, I would refactor your model. First I would store all kinds of XXLeave documents as the same type, Leave, and add a property to it which tells you what kind of leave it is (e.g. Annual, Medical etc.). Next, I would not store this list in the Employee itself, but rather in a separate MongoDB collection, and it could have an EmployeeID field, telling to which employee a Leave document belongs to (owner).
Having this model, you can easily list a specific type of Leave documents of an employee with a query like:
c := session.DB("").C("leaves")
err := c.Find(bson.M{"empid": eid, "type": LeaveAnnual}).All(&result)
To list all Leave documents of an employee, simply don't use the "type" filter:
err := c.Find(bson.M{"empid": eid}).All(&result)
Now on to pagination. If you have this model, pagination becomes a piece of cake:
q = c.Find(bson.M{"empid": eid}).Sort("applieddate", "status")
// Use Skip() and Limit() to denote the page you want to send, e.g.
q = Skip((page-1) * 20).Limit(20)
err := q.All(&result)
If you want to send and paginate over all Leave documents regardless of the Employee (the author), then also leave out the EmpId filter:
err := c.Find(nil).Sort("applieddate", "status").
Skip((page-1) * 20).Limit(20).All(&result)
Note that using Skip() and Limit() works great for "small" number of documents. But for large number of documents you should use pagination based on specifying starting index entry. See this question for more details and a ready-to-use library for it: