Supabase•4y ago•

9 replies

Table join only returning 1 matching column instead of multiple

Database Setup
-------
Table

tournaments

tournaments

-
- id
- title
- matches ( FK

id

id

field in

t_matches

t_matches

)

Table

t_matches

t_matches

- id
-created_at
- t_id ( FK

id

id

field in

tournaments

tournaments

)
- t_match ( FK

id

id

field in

t_match

t_match

) -- Holds details about match

Table

t_match

t_match

- id
- title
- state
- participants ( FK

id

id

field of

participants

participants

table )

Table

t_match_participants

t_match_participants

- id
- match_id ( FK

id

id

field of

t_match

t_match

- participant ( FK

id

id

for

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();

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

1. Multiple

matches

matches

, returning as an array since there are multiple matches in my database using the

t_id

t_id

2. Multiple

participants

participants

, returning as an array since there are multiple participants for the given

match_id

match_id

But instead I am only getting....

1. A single

participant

participant

object
2. A single

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

Image2 -

t_matches

t_matches

Image3-

t_match

t_match

Image4 -

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
         }
      }
   }
}

Screen_Shot_2022-10-13_at_4.07.13_PM.png

Table join only returning 1 matching column instead of multiple

Database Setup
-------
Table

tournaments

tournaments

-
- id
- title
- matches ( FK

id

id

field in

t_matches

t_matches

)

Table

t_matches

t_matches

- id
-created_at
- t_id ( FK

id

id

field in

tournaments

tournaments

)
- t_match ( FK

id

id

field in

t_match

t_match

) -- Holds details about match

Table

t_match

t_match

- id
- title
- state
- participants ( FK

id

id

field of

participants

participants

table )

Table

t_match_participants

t_match_participants

- id
- match_id ( FK

id

id

field of

t_match

t_match

- participant ( FK

id

id

for

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();

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

1. Multiple

matches

matches

, returning as an array since there are multiple matches in my database using the

t_id

t_id

2. Multiple

participants

participants

, returning as an array since there are multiple participants for the given

match_id

match_id

But instead I am only getting....

1. A single

participant

participant

object
2. A single

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

Image2 -

t_matches

t_matches

Image3-

t_match

t_match

Image4 -

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
         }
      }
   }
}

Table join only returning 1 matching column instead of multiple

Table join only returning 1 matching column instead of multiple

Similar Threads

Similar Threads

Similar Threads