Skip to content

Add updated_after and updated_before to projects REST API

Mario Celi requested to merge 378662-projects-api into master

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 by updated_at. This is necessary because other queries would be too inefficient and would timeout in .com. Related discussion in !113602 (comment 1301483651)
  • 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.

Related to #393979 (closed)

Edited by Mario Celi

Merge request reports