Zapytanie SQL- status przyjaciela

Question

Cześć. Mam aplikację w Typescript, bazę danych w Postgresql którego się obecnie uczę i jako ORM uzywam TypeORM. Mam użytkowników i chciałbym przy ich wyszukiwaniu dodać status przyjaźni pomiędzy użytkownikiem aplikacji. Przyjaźnie przechowuję w osobnej tabeli tak jak w tym wątku: https://stackoverflow.com/questions/2910134/friendship-database-schema . Gdy pytam bazę danych o użytkowników to moja logika jest taka: jeśli w bazie jest prośba ode mnie do kogoś, a ta osoba nie ma wysłanej prośby do mnie, to status przyjaźni chcę ustawić na "PENDING OUTGOING". Jeśli z kolei tamta osoba wysłała zaproszenie do mnie, ale ja nie wysłałem do niej to chcę mieć "PENDING INCOMING". W ostatnich dwóch przypadkach 1. jeśli prośba jest ode mnie i do mnie to chcę mieć "ARE FRIENDS" natomiast gdy nie ma żadnej takiej prośby to "NO REQUEST". Poradziłem sobie z tym w sposób z który działa, jednak nie jestem z niego zadowolony, ponieważ po znalezieniu wszystkich userów (bez statusu przyjaźni), lecę pętlą for i wywołuję taką funkcję:

const friendshipStatus = async (
  currentUserId: number,
  secondUserId: number
) => {
  try {
    const requestFromUser = await getConnection()
      .getRepository(Friendship)
      .createQueryBuilder("f")
      .where("f.user = :currentUserId AND f.friend = :secondUserId", {
        currentUserId,
        secondUserId
      })
      .getOne();

    const decisionOfSecondUser = await getConnection()
      .getRepository(Friendship)
      .createQueryBuilder("f")
      .where("f.user = :secondUserId AND f.friend = :currentUserId", {
        secondUserId,
        currentUserId
      })
      .getOne();

    if (requestFromUser && decisionOfSecondUser) return "ARE FRIENDS";

    if (requestFromUser && !decisionOfSecondUser) return "PENDING OUTGOING";

    if (!requestFromUser && decisionOfSecondUser) return "PENDING INCOMING";

    return "NO REQUEST";
  } catch (error) {
    console.log(error);
    return undefined;
  }
};

i dodaje do każdego usera pole "friendshipStatus" ze zwróconą wartością. Głowię się nad tym jak zapisać to wszystko w jednym zapytaniu SQL, żeby nie używać tej pętli jednak ciągle napotykam jakieś błędy. Obecnie stoję na czymś takim (jest to tylko pogląd na to jakbym chciał żeby to działało):

 const replacements = [cursor, currUserId, realLimitPlusOne];

    const users = await getConnection().query(
      `
      CREATE FUNCTION getFriendshipStatus(u1 int, u2 int) RETURNS text
        as
        $$
        BEGIN
          (select id as from friendship 
            where f.user = u1 and f.friend = u2) as "requestFromUser",
        
          (select id from friendship
            where f.user = u2 and f.friend = u1) as "decisionOfSecondUser"
 
          IF requestFromUser IS NOT NULL and decisionOfSecondUser IS NOT NULL
          THEN 
            RETURN 'ARE FRIENDS';
   
          ELSEIF requestFromUser IS NOT NULL and decisionOfSecondUser IS NULL
          THEN
            RETURN 'PENDING ONGOING';
   
          ELSEIF requestFromUser NOT NULL and decisionOfSecondUser IS NOT NULL
          THEN
            RETURN 'PENDING INCOMING';
          
          ELSE
            RETURN 'NO REQUEST';
          
          END IF;
        END;
        $$ LANGUAGE plpgsql;

      select u.*,
      select * from getFriendshipStatus($2, u.id) as "friendshipStatus"

      from user u
      where u.id > $1 and u.id != $2
      order by u.id ASC
      limit $3
      `,
      replacements
    );

gdzie cursor to id ostatniego usera który otrzymał frontend, currUserId to użytkownik wysyłający zapytanie oraz realLimitPlusOne to limit ilu użytkowników pobrać z bazy. Jestem świadom, że mogłem tu dość dużo namieszać, jednak nigdy nie wychodziłem poza proste zapytania SQL toteż skonstruowanie tych bardziej złożonych jest dla mnie póki co trudne a niestety nie udało mi się znaleźć analogicznego przypadku. Bardzo bym prosił kogoś o jakieś sugestie

Answer 1

Udało mi się rozwiązać problem, więc dzielę się rozwiązaniem to może ktoś kiedyś skorzysta.

Użyłem deklarację funkcji którą potem używam w moim zapytaniu:

await connection.query(`
    CREATE OR REPLACE FUNCTION get_status(u1 int, u2 int)
    RETURNS TEXT
    AS
    $BODY$

    DECLARE 
    result text := '';
    f1 int;
    f2 int;
   
    BEGIN
    SELECT id from friendship
    where "user" = u1 and friend = u2 into f1;

    SELECT id from friendship
    where "user" = u2 and friend = u1 into f2;

    IF f1 IS NOT NULL and f2 IS NOT NULL THEN
    result := 'ARE FRIENDS';

    ELSIF f1 IS NOT NULL and f2 IS NULL  THEN
    result := 'PENDING_ONGOING';

    ELSIF f1 IS NOT NULL and f2 IS NOT NULL THEN
    result := 'PENDING_INCOMING';

    ELSE
    result := 'NO REQUEST';

    END IF;

    RETURN result;
    END;
    $BODY$ 
    language plpgsql;

    `);

A następnie już we właściwym zapytaniu wywołuję tą funkcję z odpowiednimi parametrami

const replacements = [cursor, currUserId, realLimitPlusOne];
 
const users = await getConnection().query(
      `
      select u.*,
      (select * from get_status($2, u.id) ) "friendshipStatus"

      from "user" u
      where u.id > $1 and u.id != $2
      order by u.id ASC
      limit $3
      `,
      replacements
    );