SQL N:M query merging results by condition flag in intermediate table

Question

[First of all, if this is a duplicate, sorry, I couldn't find a response for this, as this is a strange solution for a limitation on an ORM and I'm clearly a noobie on SQL]

Domain requirements:

• A brigades must be composed by one user (the commissar one) and, optionally, one and only one assistant (1:1)
• A user can only be part of one brigade (1:1)

CREATE TABLE Users
(
   id SERIAL PRIMARY KEY,
   username VARCHAR(100) NOT NULL UNIQUE,
   password VARCHAR(100) NOT NULL
);

CREATE TABLE Brigades
(
   id SERIAL PRIMARY KEY,
   name VARCHAR(100) NOT NULL
);

-- N:M relationship with a flag inside which determine if that user is a commissar or not
CREATE TABLE Brigade_User
(
   brigade_id INT NOT NULL REFERENCES Brigades(id)
         ON DELETE CASCADE
         ON UPDATE CASCADE,
   user_id INT NOT NULL REFERENCES Users(id)
         ON DELETE CASCADE
         ON UPDATE CASCADE,
   is_commissar BOOLEAN NOT NULL
   PRIMARY KEY(brigade_id, user_id)
);

Ideally, as relations are 1:1, Brigade_User intermediate table could be erased and a Brigade table with two foreign keys could be created instead (this is not supported by Diesel Rust ORM, so I think I'm coupled to first approach)

CREATE TABLE Brigades
(
   id SERIAL PRIMARY KEY,
   name VARCHAR(100) NOT NULL
   -- 1:1
   commisar_id INT NOT NULL REFERENCES Users(id)
         ON DELETE CASCADE
         ON UPDATE CASCADE,
   -- 1:1
   assistant_id INT NOT NULL REFERENCES Users(id)
         ON DELETE CASCADE
         ON UPDATE CASCADE
);

An example...

> SELECT * FROM brigade_user LEFT JOIN brigades ON brigade_user.brigade_id = brigades.id;

 brigade_id | user_id | is_commissar | id |       name
------------+---------+--------------+----+------------------
          1 |       1 | t            |  1 | Patrulla gatuna
          1 |       2 | f            |  1 | Patrulla gatuna
          2 |       3 | t            |  2 | Patrulla perruna
          2 |       4 | f            |  2 | Patrulla perruna
          3 |       6 | t            |  3 | Patrulla canina
          3 |       5 | f            |  3 | Patrulla canina

(4 rows)

Is it possible to make a query which returns a table like this?

brigade_id | commissar_id | assistant_id |        name
-----------+--------------+--------------+--------------------
         1 |       1      |      2       | Patrulla gatuna
         2 |       3      |      4       | Patrulla perruna
         3 |       6      |      5       | Patrulla canina

See that each two rows have been merged into one (remember, a brigade is composed by one commissary and, optionally, one assistant) depending on the flag.

Could this model be improved (having in mind the limitation on multiple foreign keys referencing the same table, discussed here)

Answer 1

Try the following:

with cte as
(
  SELECT A.brigade_id,A.user_id,A.is_commissar,B.name 
  FROM brigade_user A LEFT JOIN brigades B ON A.brigade_id = B.id
)
select C1.brigade_id, C1.user_id as commissar_id , C2.user_id as assistant_id, C1.name from 
cte C1 left join cte C2
on C1.brigade_id=C2.brigade_id
and C1.user_id<>C2.user_id
where C1.is_commissar=true

See a demo from here.