Case & Problem:
Consider a corporate building with some rooms and departments that need authorization to enter.
Employees have to scan their badges to be authorized.
If the employee is inside (the badge was already scanned), the system warns her/him accordingly and does not open the door.
And the supervisor should be able to see and/or report all employees who are in a particular room at any given time.
Checking whether the employee is inside or not was somewhat trivial with the help of a couple of StackOverflow/StackExchange answers.
My problem is at the reporting stage.
The question is how can I filter the table to show those who are currently inside the given point, in a performant way.
Current Situation & What I've Done So Far:
My backend code is looping over all the records and check if they're inside, one by one.
Yet, I suspect that this is not ideal (I might be wrong, though) performance-wise and/or for modularity/responsibility concerns; in the future, I'll need to allow other client applications to connect to the database without having them write their own code/logic to check or report the inside status.
Sample Table:
+-----+----------+---------------+-----------+-----------------+
| id | code | point | action | timestamp |
+-----+----------+---------------+-----------+-----------------+
| 1 | 11111111 | Department A | pass_in | 1561369024008 |
| 2 | 11111111 | Department A | pass_out | 1561369024007 |
| 3 | 11111111 | Department A | pass_in | 1561369024006 |
| 5 | 22222222 | Department B | pass_in | 1561369024005 |
| 6 | 22222222 | Department A | pass_out | 1561369024004 |
| 7 | 22222222 | Department A | pass_in | 1561369024003 |
+-----+----------+---------------+-----------+-----------------+
Sample Code:
var point = 'Department A';
var recordsInPoint = helper
.fetchAllRecords()
.filter( record => helper.isInside( record, point ) );
Query to check if inside:
SELECT
( count_compare.count_in > count_compare.count_out ) AS is_inside
FROM
(
SELECT
(
SELECT
COUNT(`id`)
FROM
scanner_log
WHERE
`code`='11111111' AND
`point`='Department A' AND
`action`='pass_in'
) AS count_in,
(
SELECT
COUNT(`id`)
FROM
scanner_log
WHERE
`code`='11111111' AND
`point`='Department A' AND
`action`='pass_out'
) AS count_out
) AS `count_compare`;
Questions:
• How can I move the reporting logic from backend to database?
• Would it be really faster and less resource-hungry if handled by db server?
Ideas & Options:
• Create a stored procedure and carry the backend logic to db as is by looping over all records.
I don't see much benefit on this, to be honest. Same loop method, just performed by db server. Also, there's a major drawback as I don't have just one table for logs, there are separate tables for each building or company. And I can't pass the table name as a parameter, as far as I know mySQL / MariaDB doesn't support this. I have to use PREPARE QUERY and EXECUTE QUERY commands which sounds a little bit overkill to me.
• Create another nested temporary table and join them somehow to filter but I just couldn't get my head around it, really, nothing.
Thanks for all your help and insights.
