Table join only returning 1 matching column instead of multiple

Database Setup
-------
Table

tournaments

tournaments

tournaments

tournaments

- id
- title
- matches ( FK
```
id
```
```
id
```
  field in
```
t_matches
```
  t_matches
```
t_matches
```
  t_matches
  )

able

t_matches

t_matches

t_matches

t_matches

id
-created_at
t_id ( FK
```
id
```
```
id
```
field in
```
tournaments
```
tournaments
```
tournaments
```
tournaments
)
t_match ( FK
```
id
```
```
id
```
field in
```
t_match
```
t_match
```
t_match
```
t_match
) -- Holds details about match

Table

t_match

t_match

t_match

t_match

id
title
state
participants ( FK
```
id
```
```
id
```
field of
```
participants
```
participants
```
participants
```
participants
table )

Table

t_match_participants

t_match_participants

t_match_participants

t_match_participants

id
match_id ( FK
```
id
```
```
id
```
field of
```
t_match
```
t_match
```
t_match
```
t_match
participant ( FK
```
id
```
```
id
```
for
```
profiles
```
profiles
```
profiles
```
profiles
table )

The issue I am having is using this query...

let { data, error, status } = await supabase
        .from("tournaments")
        .select('*, matches(*, t_match(*, participants(*)))')
        .eq("id", id).single();

let { data, error, status } = await supabase
        .from("tournaments")
        .select('*, matches(*, t_match(*, participants(*)))')
        .eq("id", id).single();

let { data, error, status } = await supabase
        .from("tournaments")
        .select('*, matches(*, t_match(*, participants(*)))')
        .eq("id", id).single();

let { data, error, status } = await supabase
        .from("tournaments")
        .select('*, matches(*, t_match(*, participants(*)))')
        .eq("id", id).single();

Using this query ^ I am expecting to retrieve...

Multiple
```
matches
```
matches
```
matches
```
matches
, returning as an array since there are multiple matches in my database using the
```
t_id
```
t_id
```
t_id
```
t_id
of
```
1
```
```
1
```
Multiple
```
participants
```
participants
```
participants
```
participants
, returning as an array since there are multiple participants for the given
```
match_id
```
match_id
```
match_id
```
match_id

But instead I am only getting....

A single
```
participant
```
participant
```
participant
```
participant
object
A single
```
match
```
match
```
match
```
match
object

Below I will post some photos of the Tables + Data. I would appreciate some help here. Thank you.

Image 1 -

tournaments

tournaments

tournaments

tournaments
Image2 -

t_matches

t_matches

t_matches

t_matches
Image3-

t_match

t_match

t_match

t_match
Image4 -

t_match_participants

t_match_participants

t_match_participants

t_match_participants

Here is my current returned data...

{
   "id":1,
   "created_at":"...",
   "title":"Test Tournament",
   "min_participants":2,
   "max_participants":5,
   "start_time":null,
   "matches":{
      "id":1,
      "created_at":"...",
      "t_id":1,
      "t_match":{
         "id":1,
         "created_at":"...",
         "name":"Match Round 1",
         "state":"ongoing",
         "participants":{
            "id":1,
            "created_at":"...",
            "match_id":1,
            "participant":"48191...",
            "isWinner":null
         }
      }
   }
}

{
   "id":1,
   "created_at":"...",
   "title":"Test Tournament",
   "min_participants":2,
   "max_participants":5,
   "start_time":null,
   "matches":{
      "id":1,
      "created_at":"...",
      "t_id":1,
      "t_match":{
         "id":1,
         "created_at":"...",
         "name":"Match Round 1",
         "state":"ongoing",
         "participants":{
            "id":1,
            "created_at":"...",
            "match_id":1,
            "participant":"48191...",
            "isWinner":null
         }
      }
   }
}

{
   "id":1,
   "created_at":"...",
   "title":"Test Tournament",
   "min_participants":2,
   "max_participants":5,
   "start_time":null,
   "matches":{
      "id":1,
      "created_at":"...",
      "t_id":1,
      "t_match":{
         "id":1,
         "created_at":"...",
         "name":"Match Round 1",
         "state":"ongoing",
         "participants":{
            "id":1,
            "created_at":"...",
            "match_id":1,
            "participant":"48191...",
            "isWinner":null
         }
      }
   }
}

{
   "id":1,
   "created_at":"...",
   "title":"Test Tournament",
   "min_participants":2,
   "max_participants":5,
   "start_time":null,
   "matches":{
      "id":1,
      "created_at":"...",
      "t_id":1,
      "t_match":{
         "id":1,
         "created_at":"...",
         "name":"Match Round 1",
         "state":"ongoing",
         "participants":{
            "id":1,
            "created_at":"...",
            "match_id":1,
            "participant":"48191...",
            "isWinner":null
         }
      }
   }
}

Screen_Shot_2022-10-13_at_4.07.13_PM.png

EthanOP•10/13/22, 9:09 PM

Screen_Shot_2022-10-13_at_4.09.45_PM.png

EthanOP•10/13/22, 9:10 PM

Screen_Shot_2022-10-13_at_4.10.07_PM.png

EthanOP•10/13/22, 9:10 PM

Screen_Shot_2022-10-13_at_4.10.29_PM.png

jensen•10/13/22, 9:22 PM

Can you tell me what you get with

let { data, error, status } = await supabase
        .from("tournaments")
        .select('*, t_matches(*)')
        .eq("id", id);

let { data, error, status } = await supabase
        .from("tournaments")
        .select('*, t_matches(*)')
        .eq("id", id);

garyaustin•10/13/22, 9:25 PM

Looks like you have foreign keys pointing back and forth between tables, if I'm reading that right. You usually only would have an fk from matches to tournaments and not one from tournaments to a single match (you seem to point it to matches id 1) The same applies for your other relation not working. You want foreign key pointing from the many to the one relation.

Ggaryaustin Looks like you have foreign keys pointing back and forth between tables, if I'm ...

EthanOP•10/16/22, 10:18 PM

Ohh okay this is a DB modeling issue on my end.

Regarding your last sentence,You want FK pointing from many-to-one relationshipYou want FK pointing from many-to-one relationship, I am assuming then that my current implementation of adding a FK to

tournaments

tournaments

creates a 1:1 relationship, which is why querying a match only returns a single result, even though there may be multiple rows where with the same

t_id

t_id

.

This helps a lot in my understanding but I should do more research and get more practice with relational data, thanks a lot!

Ggaryaustin Looks like you have foreign keys pointing back and forth between tables, if I'm ...

EthanOP•10/16/22, 10:31 PM

Thanks once again! I was able to restructure the db and was able to fix the issue and I think I have a better understanding

EthanOP•10/16/22, 10:42 PM

Well actually, maybe not...

I will do more research and report back

EthanOP•10/19/22, 11:14 PM

I ended up needing to refractor the DB more but this definitely helped me get a better understand of how to build relationships. Thanks again !

Table join only returning 1 matching column instead of multiple

Similar Threads

Similar Threads

Similar Threads