I have 3 databases: Units, Contracts and Tenants. Each contract has 1 tenant and 1 unit, but 1 tanent can be referenced by multiple contracts. There are old, current and upcomming contracts. I want to implement filter in my GET /tenant API to filter tenants
- by porjects_id of the unit in the current contract (contract where current date is between start_date and end_date of the contract
- by the end_date of the latest contract (contract with the latest end_date).
I can do both usin a lambda function (implement the filter in the function stack insted of query filter in query all functrion), but then I would have a problem with pagination - example: I query 10 items per page with pagination from db and then I filter them by project_id and filter all 10 items out, the client receives 0 items and must send request for the next page when filter applied - awful UX.
____
My alternative solution idea for the end_date was to use the aggregation addon which would output a sorted list of the end dates, but I can't find a way to reference the list produced by the aggregation addon in the query filter.
Another solution would to run everyday a cronjob which would query each tenant entry and update the current contract and the latest end_date for each tenant. But it looks for me like a dirty workaround.
My questions:
Do you have any better solution for my case than using cron jobs (background tasks)?
Should I have any concerns regarding the performance of the app if I decide to use cron jobs to solve this task? Starting at which amount of tenant we could face any performance issues or be forsed to upgrade the plan if the cron job would run at the night when noone uses the app?
