Add updated_after and updated_before to projects REST API
What does this MR do and why?
Now the REST API allows filtering projects by the updated_after and updated_before params. The new filter is available for the following endpoints
- GET /projects
- This one has a restriction, you can only filter by
updated_at
if you order byupdated_at
. This is necessary because other queries would be too inefficient and would timeout in .com. Related discussion in !113602 (comment 1301483651)
- This one has a restriction, you can only filter by
- GET /users/:user_id/projects
- GET /users/:user_id/starred_projects
- GET /projects/:project_id/forks
How to set up and validate locally
Use the command line to make an API request
curl --header "PRIVATE-TOKEN: <token>" "http://localhost:3000/api/v4/projects?updated_before=2014-02-17T09%3A24%3A18Z"
curl --header "PRIVATE-TOKEN: <token>" "http://localhost:3000/api/v4/projects?updated_after=2014-02-17T09%3A24%3A18Z"
curl --header "PRIVATE-TOKEN: <token>" "http://localhost:3000/api/v4/projects/:id/forks?updated_before=2014-02-17T09%3A24%3A18Z"
curl --header "PRIVATE-TOKEN: <token>" "http://localhost:3000/api/v4/projects/:id/forks?updated_after=2014-02-17T09%3A24%3A18Z"
curl --header "PRIVATE-TOKEN: <token>" "http://localhost:3000/api/v4/users/:user_id/projects?updated_before=2014-02-17T09%3A24%3A18Z"
curl --header "PRIVATE-TOKEN: <token>" "http://localhost:3000/api/v4/users/:user_id/projects?updated_after=2014-02-17T09%3A24%3A18Z"
curl --header "PRIVATE-TOKEN: <token>" "http://localhost:3000/api/v4/users/:user_id/starred_projects?updated_before=2014-02-17T09%3A24%3A18Z"
curl --header "PRIVATE-TOKEN: <token>" "http://localhost:3000/api/v4/users/:user_id/starred_projects?updated_after=2014-02-17T09%3A24%3A18Z"
DB review
Projects visible to current user GET /projects
Restricting this one to only allow filtering by updated_at if ordered_by updated_at.
https://console.postgres.ai/shared/f9624a9b-6cc0-4e7b-8041-0d06e6b24e0a
SELECT
"projects".*
FROM
"projects"
WHERE
(
EXISTS (
SELECT
1
FROM
"project_authorizations"
WHERE
"project_authorizations"."user_id" = 1614863
AND (
project_authorizations.project_id = projects.id
)
)
OR projects.visibility_level IN (0, 10, 20)
)
AND "projects"."pending_delete" = FALSE
AND "projects"."updated_at" <= '2023-02-17 09:24:18'
AND "projects"."hidden" = FALSE
ORDER BY
"projects"."updated_at" DESC
"projects"."id" DESC
LIMIT
20 OFFSET 0
Projects for a given user GET /users/:id/projects
https://console.postgres.ai/shared/758ee776-ac5a-4c51-b6df-bed3c09a3cf5
SELECT
"projects".*
FROM
"projects"
INNER JOIN "namespaces" ON "projects"."namespace_id" = "namespaces"."id"
WHERE
"namespaces"."owner_id" = 1614863
AND "namespaces"."type" = 'User'
AND "projects"."pending_delete" = FALSE
AND "projects"."updated_at" <= '2023-02-17 09:24:18'
AND "projects"."hidden" = FALSE
ORDER BY
"projects"."id" DESC
LIMIT
20 OFFSET 0
Forks of a project /projects/:id/forks
https://console.postgres.ai/shared/c4928aa0-ff81-4fb8-b09b-e1023d384252
SELECT
"projects".*
FROM
"projects"
WHERE
(
EXISTS (
SELECT
1
FROM
"project_authorizations"
WHERE
"project_authorizations"."user_id" = 1614863
AND (
project_authorizations.project_id = projects.id
)
)
OR projects.visibility_level IN (0, 10, 20)
)
AND "projects"."pending_delete" = FALSE
AND "projects"."id" IN (
SELECT
"projects"."id"
FROM
"projects"
INNER JOIN "fork_network_members" ON "projects"."id" = "fork_network_members"."project_id"
WHERE
"fork_network_members"."forked_from_project_id" = 13083
)
AND "projects"."updated_at" <= '2023-02-17 09:24:18'
AND "projects"."hidden" = FALSE
ORDER BY
"projects"."id" DESC
LIMIT
20 OFFSET 0
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #393979 (closed)
Edited by Mario Celi