I am using Hasura to build a GraphQL API. It's coming along nicely but I have a permissions related problem.
I am looking for a way to have a permission based on a field value from the latest row of a multi row table.
So I have created an array relationship which I would like to use in a permission. But I am only interested in the latest row from this array. Permissions allow me to use the _in or _nin operators on the array. But I specifically need the value from the latest row. As far as I know there is no such operator for this.
When there is only one row in the table then using the _eq operator on the field works. But the chances or there being only one row in the table is very low.
Here's some simplified information about my tables and relationships:
| id | title | status (array relationship) |
|---|---|---|
| 1 | Job title 1 | |
| 2 | Job title 2 |
| id | job_id | datetime | status |
|---|---|---|---|
| 1 | 1 | 2023-01-01 | 'open' |
| 2 | 2 | 2023-01-02 | 'closed' |
| 2 | 2 | 2023-01-03 | 'open' |
| id | job_id | response | job (object relationship) |
|---|---|---|---|
| 1 | 2 | response A | |
| 1 | 2 | response B | |
| 1 | 2 | response C |
jobs.status (Array): jobs (id) -> jobs_status (job_id)
jobs_responses.job (Object): jobs_responses (job_id) -> jobs (id)
The permission I created is an insert permission on the jobs_responses table which uses the job relationship to check the value of the job.status field. But since this is an array I would like to only check the status value of the latest row (sorted by datetime). The _eq, _in or _nin operators cannot do that.
I tried making an Object relationship for jobs.status instead of an Array relationship. But that only returns the first row that matches the relationship.
I thought about creating a normal jobs.status field in the jobs table that can be used for the permission. The jobs_status table would still exist but would not be used for the permission. The jobs.status field would have the same value as the status field in the latest row of jobs_status. Both the jobs.status field and jobs_status table would be populated at the same time when creating/updating a job. But I don't really like this solution because it feels like a double administration.
Does anyone know if Hasura can handle my use case? Would love to read how you would solve this problem. Thanks in advance!
I would probably solve this using a Computed Field.
You would have to define a function that fetches the latest status given a jobs row argument. Then you would define a computed field latest_job_status on jobs referencing this function.
Now you can refer to this computed field in permissions like you would any other field, and you don't have to manually ensure that the field is kept up to date whenever changes are made to the jobs_status table.