Rewrite membership queries

[htdvisser]

Summary

I want to rewrite the membership queries in the identity server to a single query that uses joins and subqueries to get a membership listing.

Slightly related to #443

Why do we need this?

The current solution with multiple queries is slow and doesn't allow for proper pagination.

What is already there? What do you see now?

On a "list applications" without collaborator (ttn-lw-cli applications list), the IS uses multiple queries to determine membership:

 DEBUG Run database query                       duration=2ms grpc_method=List grpc_service=ttn.lorawan.v3.ApplicationRegistry namespace=db query=SELECT * FROM "accounts"  WHERE "accounts"."deleted_at" IS NULL AND (("accounts"."uid" = $1) AND ("accounts"."account_type" = $2)) request_id=01DGJ00DVW3KKYRJGEYRX8X6XY rows=1 source=account.go:50 values=[admin user]
 DEBUG Run database query                       duration=2ms grpc_method=List grpc_service=ttn.lorawan.v3.ApplicationRegistry namespace=db query=SELECT * FROM "memberships"  WHERE ("memberships"."account_id" = $1) request_id=01DGJ00DVW3KKYRJGEYRX8X6XY rows=4 source=membership.go:49 values=[6042f8b0-ac9c-4f0e-bf22-a76b89186a9f]
 DEBUG Run database query                       duration=4ms grpc_method=List grpc_service=ttn.lorawan.v3.ApplicationRegistry namespace=db query=SELECT id as uuid, application_id as friendly_id FROM "applications"  WHERE (id in ($1)) request_id=01DGJ00DVW3KKYRJGEYRX8X6XY rows=1 source=membership.go:98 values=[8685010b-7ba6-45e7-ba8a-4a851e3e1d8e]
 DEBUG Run database query                       duration=2ms grpc_method=List grpc_service=ttn.lorawan.v3.ApplicationRegistry namespace=db query=SELECT id as uuid, client_id as friendly_id FROM "clients"  WHERE (id in ($1,$2)) request_id=01DGJ00DVW3KKYRJGEYRX8X6XY rows=2 source=membership.go:98 values=[bd8ca7bf-4eba-43e8-b9bd-de6520812756 3bdb1903-a04e-46ec-9990-d419487e107f]
 DEBUG Run database query                       duration=2ms grpc_method=List grpc_service=ttn.lorawan.v3.ApplicationRegistry namespace=db query=SELECT account_id AS uuid, uid AS friendly_id FROM "accounts"  WHERE (account_type = $1) AND (account_id in ($2)) request_id=01DGJ00DVW3KKYRJGEYRX8X6XY rows=1 source=membership.go:95 values=[organization 2c398a2d-9bb8-4ed1-8837-5252dfb638bd]
 DEBUG Run database query                       duration=1ms grpc_method=List grpc_service=ttn.lorawan.v3.ApplicationRegistry namespace=db query=SELECT * FROM "accounts"  WHERE "accounts"."deleted_at" IS NULL AND (("accounts"."uid" = $1) AND ("accounts"."account_type" = $2)) request_id=01DGJ00DVW3KKYRJGEYRX8X6XY rows=1 source=account.go:50 values=[admin-org organization]
 DEBUG Run database query                       duration=2ms grpc_method=List grpc_service=ttn.lorawan.v3.ApplicationRegistry namespace=db query=SELECT * FROM "memberships"  WHERE ("memberships"."account_id" = $1) request_id=01DGJ00DVW3KKYRJGEYRX8X6XY rows=1 source=membership.go:49 values=[aed014e1-1fc4-4144-a083-ef37dfd23863]
 DEBUG Run database query                       duration=1ms grpc_method=List grpc_service=ttn.lorawan.v3.ApplicationRegistry namespace=db query=SELECT id as uuid, application_id as friendly_id FROM "applications"  WHERE (id in ($1)) request_id=01DGJ00DVW3KKYRJGEYRX8X6XY rows=1 source=membership.go:98 values=[03ef8687-8cd7-40ba-a09e-f69db3da9403]
 DEBUG Run database query                       duration=2ms grpc_method=List grpc_service=ttn.lorawan.v3.ApplicationRegistry namespace=db query=SELECT count(*) FROM "applications"  WHERE "applications"."deleted_at" IS NULL AND ((application_id IN ($1,$2))) request_id=01DGJ00DVW3KKYRJGEYRX8X6XY rows=0 source=pagination.go:58 values=[admin-app admin-org-app]
 DEBUG Run database query                       duration=1ms grpc_method=List grpc_service=ttn.lorawan.v3.ApplicationRegistry namespace=db query=SELECT id, created_at, updated_at, application_id FROM "applications"  WHERE "applications"."deleted_at" IS NULL AND ((application_id IN ($1,$2))) ORDER BY application_id LIMIT 50 OFFSET 0 request_id=01DGJ00DVW3KKYRJGEYRX8X6XY rows=2 source=application_store.go:93 values=[admin-app admin-org-app]

What is missing? What do you want to see?

Less queries, and preferably not ones that pipe the output of one into the next.

How do you propose to implement this?

Perhaps something like

select distinct applications.id, applications.application_id from applications
join memberships on memberships.entity_type = 'application' and memberships.entity_id = applications.id
where memberships.account_id = (
    select accounts.id from accounts where account_type = 'user' and uid = 'admin'
) or memberships.account_id in (
    select accounts.id from accounts
    join memberships on memberships.entity_type = accounts.account_type and memberships.entity_id = accounts.account_id
    where memberships.account_id = (
        select accounts.id from accounts where account_type = 'user' and uid = 'admin'
    )
) order by applications.application_id;

But perhaps we'd need to split it into two queries in order to immediately determine rights, which we'll need anyway.

Can you do this yourself and submit a Pull Request?

I will