How to perform this complex query with subquery by supabase-js?

[dule-kitemetric]

Context

• I have a table projects(id, name) and a table project_members(id, project_id, user_id, roles).
  • roles is an array of project member roles. It can be manager, developer, tester,... since a user can have multiple project roles
• Basic: I want to filter projects that have both user1 (user_id = 1) is developer and user2 (user_id = 2) is tester.
  • Advance: Find all projects that have (user11 or user12 or user13 or ...) as developer and (user21 or user22 or user23 or ...) as tester

Question

• How can I perform query with supabase-js for the basic and advance problem?
  • Currently the basic query can be translated into: "Find projects that have (a member with user_id = 1 and roles include developer) and have (a member in that same project with user_id = 2 and roles include tester)"
    Example:

SELECT p.*
FROM projects p
JOIN project_members pm1 
  ON p.id = pm1.project_id 
JOIN project_members pm2 
  ON p.id = pm2.project_id 
WHERE
  pm1.user_id = 1 
  AND pm1.roles @> ARRAY['developer']
  AND pm2.user_id = 2 
  AND pm2.roles @> ARRAY['tester']

Thank you so much