How to use a triple join in supabase?

[jarckn]

Hello,

I am kinda stuck in a architectural and understanding problem.

I have three tables: players, matches and heroes.
I want to be able to access the matches a player played and the heroes he picked.
My thought was to create a join table named matches_players_heroes and put in three columns: match_id, player_id and hero_id. Afterwards I put FKs to all tables and thats it.

And that works fine, at least I am getting the data I want. But here's the thing: I am getting them duplicated as soon as I have at least two matching columns.

This is how I query my data:

  let { data, error } = await supabase
    .from('matches')
    .select('*, players  (*, heroes (*))');

For example do I have two rows in my table:

match_id	player_id	hero_id
4	3	5
4	4	6

This would result in a output like this:

[
  {
    "id": "4",
    "notes": "test match",
    "inserted_at": "2021-05-09T17:53:09+00:00",
    "updated_at": "2021-05-09T17:53:09+00:00",
    "result": "Loss",
    "players": [
      {
        "id": "3",
        "username": "test",
        "notes": "test",
        "battletag": "test",
        "heroes": [
          {
            "id": "5",
            "name": "Baptiste",
            "description": null,
            "type": "Support"
          }
        ]
      },
      {
        "id": "4",
        "username": "testuser",
        "notes": "testest",
        "battletag": "testuser#1000",
        "heroes": [
          {
            "id": "6",
            "name": "Brigitte",
            "description": null,
            "type": "Support"
          }
        ]
      }
    ]
  }
]

Everythings fine, just as I want it.
But if I have something like this:

match_id	player_id	hero_id
4	3	5
4	3	6

I am getting something like this:

[
  {
    "id": "4",
    "notes": "test match",
    "inserted_at": "2021-05-09T17:53:09+00:00",
    "updated_at": "2021-05-09T17:53:09+00:00",
    "result": "Loss",
    "players": [
      {
        "id": "3",
        "username": "test",
        "notes": "test",
        "battletag": "test",
        "heroes": [
          {
            "id": "5",
            "name": "Baptiste",
            "description": null,
            "type": "Support"
          },
          {
            "id": "6",
            "name": "Brigitte",
            "description": null,
            "type": "Support"
          }
        ]
      },
      {
        "id": "3",
        "username": "test",
        "notes": "test",
        "battletag": "test",
        "heroes": [
          {
            "id": "5",
            "name": "Baptiste",
            "description": null,
            "type": "Support"
          },
          {
            "id": "6",
            "name": "Brigitte",
            "description": null,
            "type": "Support"
          }
        ]
      }
    ]
  }
]

I assume whats causing this is the fact that theres some kind of missing uniqueness to the rows, which is totally intended.
Anyway, are there any ways to fight this? I haven't really found anything for PostgreSQL directly, although I don't really know what to look for.

This is my first project with Supabase and I'm not really experienced with JS development in general, so any tips would be really appreciated.

Best,

Niklas

[steve-chavez]

Hi there @jarckn,

I think your model could benefit from a bit of normalization, that way you can avoid duplicates. It will require one more join table.

So IIUC, a player can pick one or more heroes for the match. Then we could have the model like this:

players >- plays -< matches

plays >- picks -< heroes

A plays join table for the M2M relationship between players and matches, and a picks join table for the M2M on plays and heroes.

I want to be able to access the matches a player played and the heroes he picked.

Then you could do that query like this:

  let { data, error } = await supabase
    .from('matches')
    .select('*, plays(player:players(*),heroes(*))');

Click to see result

[
  {
    "id": 4,
    "name": "match-4",
    "plays": [
      {
        "player": {
          "id": 3,
          "name": "player-3"
        },
        "heroes": [
          {
            "id": 5,
            "name": "hero-5"
          },
          {
            "id": 6,
            "name": "hero-6"
          }
        ]
      },
      {
        "player": {
          "id": 4,
          "name": "player-4"
        },
        "heroes": [
          {
            "id": 6,
            "name": "hero-6"
          }
        ]
      }
    ]
  }
]

Hope that helps.

---

This is the SQL I've used for the example:

Sample SQL

create table matches(id int primary key, name text);
create table players(id int primary key, name text);
create table heroes(id int primary key, name text);


create table plays(
  id int primary key,
  match_id int references matches(id),
  player_id int references players(id)
);


create table picks(
  play_id int references plays(id),
  hero_id int references heroes(id)
);


insert into players values(3, 'player-3');
insert into players values(4, 'player-4');


insert into matches values (4, 'match-4');


insert into heroes values (5, 'hero-5');
insert into heroes values (6, 'hero-6');


insert into plays values (1, 4, 3);
insert into plays values (2, 4, 4);


insert into picks values (1, 5);
insert into picks values (1, 6);
insert into picks values (2, 6);

[jarckn]

Hey Steve,

thanks for your reply, this is indeed working and helps a lot!
I was already thinking about this and trying things around but I was missing the correct query, I guess.

One thing I liked about the triple relationship was that I could switch the query around as I wanted.
Like I could get matches by players, matches by heroes, heroes by matches etc.

I'm kind of limited now as I don't have a direct relationship between heroes and matches but at least it's working and not looking messy like before.

Another question: Can you imagine any way to still nest the heroes underneath the player?

The player and heroes data is currently nested right in the plays:

[
  {
    "id": "060216ba-78f0-45a7-aebe-569666d0659b",
    "notes": "test match",
    "inserted_at": "2021-05-09T17:53:09+00:00",
    "updated_at": "2021-05-09T17:53:09+00:00",
    "result": "Loss",
    "plays": [
      {
        "id": "4462cba7-9c7e-468a-afde-ae8c6105646d",
        "player_id": "fa6409ed-e5a8-49af-a8a6-00f2359a214a",
        "match_id": "060216ba-78f0-45a7-aebe-569666d0659b",
        "player": {
          "id": "fa6409ed-e5a8-49af-a8a6-00f2359a214a",
          "username": "jarckn",
          "notes": "test",
          "battletag": "jarckn#1337"
        },
        "heroes": [
          {
            "id": "42aa5f2e-abed-47e2-9ff4-a0a5e8c9b3b2",
            "name": "Lúcio",
            "description": null,
            "type": "Support"
          },
          {
            "id": "34a7d70a-f289-486d-8554-f2891de132e1",
            "name": "Pharah",
            "description": null,
            "type": "DPS"
          },
          {
            "id": "48e3f2ce-6776-4ea3-aec5-578118090014",
            "name": "Orisa",
            "description": null,
            "type": "Tank"
          }
        ]
      }
    ]
  },
  {
    "id": "987f9457-3d2c-4ec6-a5a4-2949011a895a",
    "notes": "asdasdasdasd",
    "inserted_at": "2021-05-09T17:59:40+00:00",
    "updated_at": "2021-05-09T17:59:40+00:00",
    "result": "Win",
    "plays": [
      {
        "id": "968f0cc2-b178-4f12-a05a-fac2ca80d530",
        "player_id": "fa6409ed-e5a8-49af-a8a6-00f2359a214a",
        "match_id": "987f9457-3d2c-4ec6-a5a4-2949011a895a",
        "player": {
          "id": "fa6409ed-e5a8-49af-a8a6-00f2359a214a",
          "username": "jarckn",
          "notes": "test",
          "battletag": "jarckn#1337"
        },
        "heroes": []
      }
    ]
  }
]

This is working just fine and I will continue with this, but are there any options to alter the JSON further via the query strings?
Like i.e. moving the heroes segment into the player segment?

Best,

Niklas