codeigniter3, find total number of hits based on time data

Question

I have a table where I record instants of request between user requestFrom to user requestTo against requestTime. It looks like as follows.

id	requestFrom	requestTo	requestTime
1	aaa	bbb	10001
2	aaa	bbb	10002
3	aaa	ccc	10003
4	eee	ccc	10004
5	eee	ccc	20000
6	eee	ccc	20001

I need to group the result based on, if requests are occurred within delta(say 2 seconds) time and also if request happened between same requestFrom and requestTo, it should be considered as one instant in the result. Based on that the output looks like

time	number of hits	remark
10002	1	id 1 and 2 combined
10003	1	id 3
10004	1	here id 5 and 6 did not combine with id 4, because delta is > 2
20001	1	id 5 and 6 combined

here I can use group_by(requestFrom, requestTo), but how do I incorporate requestTime delta.

how can I write a codeigniter Model function inorder to achieve above result?

Answer 1

I will explain the mathematics that is to be implemented. If that's understood, then you should have no problem implementing this for your table.

Now, you already have two aggregations. This means that your records are grouped by requestFrom and the records grouped by requestFrom are grouped further by requestTo. Now, you have to group by a third criteria. In order to define such a criteria, you need to formulate it in your own words first, in such a way that it can be transformed into formulas that can be tested easily:

1. We identify the first type of left-hand-side of such intervals in such a way that there does not exist another element that's earlier by less than delta.
2. We identify the members of such intervals in such a way that we can find a left-hand-side that's not older than the given element by more than delta.

This can be achieved by a join.

However, it's possible that a request from a to b was sent in such a way that it's newer than the left-hand-side by more than delta, but less new from the right-hand-side than delta. So, once you get this result, you can run a similar query, which differs only in the fact that it would search for items that fit this edge-case and group them similarly. You can run this second query, extending the filters each time by the groups and intervals already found, until there are no new groups.

This is a solution that should definitely work, but it's consisting of multiple queries.