[240808 TIL] 테이블 join 해서 가져오기

supabase 를 사용하다 보니 데이터를 join 해서 가져오면 매우 편리한 경우가 많았습니다.
그런데 방법의 의미를 정확하게 몰라서 gpt 쌤한테 하나하나 물어봤습니다.

첫번째 질문

supabase 에서 데이터를 join 해서 가져오는 방법에 관해 질문이 있어요. 첫번째 사례는 다음과 같고요.

await supabase
    .from('stories')
    .select('*, buddies:story_created_by (*)')
    .order('story_created_at', { ascending: false });

두번째는 다음과 같아요.

await supabase
    .from('trips')
    .select('*, contract:contract!contract_contract_trip_id_foreign (*)')
    .order('trip_created_at', { ascending: false });

각각 select 에서 무슨일이 일어나는 자세하게 설명해주세요!

첫번째 질문에 대한 GPT 쌤의 답변

Supabase에서 데이터를 join하여 가져오는 방법에 대한 질문에 대한 답변입니다. 두 개의 예제를 살펴보면서 각각의 select 구문에서 무슨 일이 일어나는지 자세히 설명드리겠습니다.

첫 번째 예제:

await supabase
    .from('stories')
    .select('*, buddies:story_created_by (*)')
    .order('story_created_at', { ascending: false });

 

이 구문에서는 stories 테이블에서 데이터를 선택합니다. select 메서드 내의 내용은 다음과 같습니다:

1. *:
   • stories 테이블의 모든 열을 선택합니다.
2. buddies:story_created_by (*):
   • buddies는 조인된 테이블의 별칭입니다.
   • story_created_by는 stories 테이블과 buddies 테이블을 조인하는 외래 키 열입니다.
   • (*)는 buddies 테이블의 모든 열을 선택합니다.
   • 결과적으로 stories 테이블의 각 행에 대해 관련된 buddies 테이블의 행을 가져옵니다.
3. order('story_created_at', { ascending: false }):
   • story_created_at 열을 기준으로 내림차순으로 정렬합니다.

이 쿼리는 stories 테이블의 모든 데이터를 가져오면서 각 스토리를 만든 사람(buddies 테이블에 해당)과 조인된 데이터를 포함합니다. 스토리는 최신 순으로 정렬됩니다.

두 번째 예제:

await supabase
    .from('trips')
    .select('*, contract:contract!contract_contract_trip_id_foreign (*)')
    .order('trip_created_at', { ascending: false });

 

이 구문에서는 trips 테이블에서 데이터를 선택합니다. select 메서드 내의 내용은 다음과 같습니다:

1. *:
   • trips 테이블의 모든 열을 선택합니다.
2. contract:contract!contract_contract_trip_id_foreign (*):
   • contract는 조인된 테이블의 별칭입니다.
   • contract!contract_contract_trip_id_foreign는 trips 테이블과 contract 테이블을 조인하는 외래 키 관계입니다.
   • (*)는 contract 테이블의 모든 열을 선택합니다.
   • 결과적으로 trips 테이블의 각 행에 대해 관련된 contract 테이블의 행을 가져옵니다.
3. order('trip_created_at', { ascending: false }):
   • trip_created_at 열을 기준으로 내림차순으로 정렬합니다.

이 쿼리는 trips 테이블의 모든 데이터를 가져오면서 각 여행과 관련된 계약(contract 테이블에 해당)과 조인된 데이터를 포함합니다. 여행 데이터는 최신 순으로 정렬됩니다.

요약

• 첫 번째 예제에서는 stories 테이블에서 데이터를 가져오면서 각 스토리와 연관된 작성자(buddies 테이블의 데이터)를 함께 가져옵니다.
• 두 번째 예제에서는 trips 테이블에서 데이터를 가져오면서 각 여행과 연관된 계약(contract 테이블의 데이터)을 함께 가져옵니다.
• 두 예제 모두 관련된 테이블 간의 외래 키를 통해 조인하며, 선택한 열은 *를 사용하여 모든 열을 가져옵니다. 결과는 지정된 열에 따라 내림차순으로 정렬됩니다.

두번째 질문

그래서 contract!contract_contract_trip_id_foreign (*) 이 구문이 의미하는 바는 정확히 뭐죠?

contract:contract!contract_contract_trip_id_foreign (*) 이 구문은 Supabase에서 특정 테이블과 조인하여 데이터를 선택하는 방법을 정의하는 구문입니다. 이를 자세히 설명드리겠습니다.

두번째 질문에 대한 GPT 쌤의 답변

구문의 구성 요소

1. contract:
   • 이 부분은 별칭입니다. 조인되는 테이블을 참조하는데 사용할 이름입니다.
   • 예를 들어, 조인된 테이블의 이름을 contract로 지정하여 나중에 이 별칭을 사용해 데이터를 참조할 수 있습니다.
2. contract!contract_contract_trip_id_foreign:
   • 이 부분은 조인 조건을 나타냅니다.
   • 첫 번째 contract는 조인할 테이블의 이름입니다.
   • contract_contract_trip_id_foreign는 외래 키 관계를 나타냅니다. 이는 contract 테이블과 trips 테이블을 연결하는 외래 키입니다.
   • 즉, trips 테이블의 특정 열과 contract 테이블의 특정 열 간의 관계를 기반으로 조인이 이루어집니다.
3. (*)
   • 이 부분은 조인된 테이블(contract)의 모든 열을 선택한다는 의미입니다.
   • 조인된 테이블의 모든 데이터를 가져오겠다는 것을 나타냅니다.

전체 구문 설명

contract:contract!contract_contract_trip_id_foreign (*) 이 구문은 다음을 의미합니다:

• trips 테이블에서 데이터를 선택할 때 contract 테이블을 조인합니다.
• 이 조인은 contract_contract_trip_id_foreign 외래 키 관계를 사용하여 수행됩니다.
• 조인된 contract 테이블의 모든 열을 선택합니다.
• 선택된 데이터에서 contract 테이블의 데이터는 별칭 contract로 참조할 수 있습니다.

예제 구문의 작동 방식

await supabase
    .from('trips')
    .select('*, contract:contract!contract_contract_trip_id_foreign (*)')
    .order('trip_created_at', { ascending: false });

이 예제에서:

• trips 테이블에서 데이터를 선택합니다.
• contract 테이블을 contract_contract_trip_id_foreign 외래 키를 사용하여 trips 테이블과 조인합니다.
• contract 테이블의 모든 열을 선택하여 결과에 포함합니다.
• 결과에서 contract 테이블의 데이터는 별칭 contract로 참조할 수 있습니다.
• 결과는 trip_created_at 열을 기준으로 내림차순으로 정렬됩니다.

따라서 이 구문은 trips 테이블의 각 여행 항목에 대해 관련된 계약 정보(contract 테이블의 데이터)를 포함한 데이터를 반환합니다.

세번째 질문

첫번째 사례에서는 buddies가 객체이면서 join 되서 반환되고, 두번째 사례에서는 contract 가 배열이면서 join 되서 반환되던데, contract!contract_contract_trip_id_foreign 이 구문은 관련된 전체를 가져오는 것이고 buddies:story_created_by 이렇게만 쓰는 것은 관련된 하나만 가져오는 것인가요?

세번째 질문에 대한 GPT 쌤의 답변

Supabase에서 데이터를 가져올 때, 조인된 테이블의 데이터가 객체인지 배열인지에 따라 다르게 반환되는 이유는 테이블 간의 관계 설정 방식 때문입니다. 각 관계의 종류에 따라 조인 결과가 달라지게 됩니다. 이를 더 자세히 설명드리겠습니다.

첫 번째 사례: stories와 buddies

await supabase
    .from('stories')
    .select('*, buddies:story_created_by (*)')
    .order('story_created_at', { ascending: false });

 

여기서 buddies:story_created_by (*) 구문은 다음을 의미합니다:

• stories 테이블에서 각 스토리를 만든 사람(story_created_by 열을 통해 참조되는 buddies 테이블의 데이터)을 조인합니다.
• 이 관계는 일반적으로 One-to-One 또는 Many-to-One 관계입니다. 즉, 각 스토리는 단 하나의 작성자만 가질 수 있습니다.
• 따라서 buddies 데이터는 객체 형태로 반환됩니다.

두 번째 사례: trips와 contract

await supabase
    .from('trips')
    .select('*, contract:contract!contract_contract_trip_id_foreign (*)')
    .order('trip_created_at', { ascending: false });

 

여기서 contract:contract!contract_contract_trip_id_foreign (*) 구문은 다음을 의미합니다:

• trips 테이블에서 각 여행과 관련된 계약(contract_contract_trip_id_foreign 외래 키를 통해 참조되는 contract 테이블의 데이터)을 조인합니다.
• 이 관계는 일반적으로 One-to-Many 관계입니다. 즉, 각 여행은 여러 개의 계약을 가질 수 있습니다.
• 따라서 contract 데이터는 배열 형태로 반환됩니다.

요약

• 객체 반환 (buddies:story_created_by):
  • One-to-One 또는 Many-to-One 관계에서 사용됩니다.
  • 각 부모 테이블 항목에 대해 단 하나의 관련 항목을 가지므로 객체로 반환됩니다.
• 배열 반환 (contract:contract!contract_contract_trip_id_foreign):
  • One-to-Many 관계에서 사용됩니다.
  • 각 부모 테이블 항목에 대해 여러 관련 항목을 가지므로 배열로 반환됩니다.

관계의 종류에 따른 데이터 반환 방식

1. One-to-One 또는 Many-to-One:
   • 관계의 각 항목이 부모 테이블의 단일 항목에 연결됩니다.
   • 예: stories 테이블의 각 항목이 단일 buddies 항목에 연결됩니다.
   • 결과: 객체 형태로 반환.
2. One-to-Many:
   • 관계의 각 항목이 부모 테이블의 여러 항목에 연결됩니다.
   • 예: trips 테이블의 각 항목이 여러 contract 항목에 연결됩니다.
   • 결과: 배열 형태로 반환.

따라서, 첫 번째 사례에서는 buddies가 객체로 반환되고, 두 번째 사례에서는 contract가 배열로 반환되는 이유는 각각의 관계 설정이 다르기 때문입니다.

네번째 질문

그렇다면, 두번째 사례에서 contract:contract_trip_id (*) 이렇게 쓰면 에러가 나던데 그 이유는 무엇인가요?

네번째 질문에 대한 GPT 쌤의 답변

두 번째 사례에서 contract:contract_trip_id (*) 구문을 사용하면 에러가 나는 이유는 Supabase의 구문 규칙과 관계 정의 방식 때문입니다. 이를 더 구체적으로 설명드리겠습니다.

이유 1: 외래 키 정의의 명확성 부족

contract:contract_trip_id (*) 구문에서:

• contract는 조인할 테이블의 이름 또는 별칭입니다.
• contract_trip_id는 외래 키를 나타내는 것으로 보이지만, Supabase에서 외래 키 관계를 명시하는 올바른 방법이 아닙니다.

올바른 구문에서 외래 키 관계를 명확히 정의해야 합니다. Supabase는 외래 키 관계를 통해 두 테이블을 연결할 때, 명시적으로 관계를 정의하는 것을 요구합니다. 이는 테이블 간의 명확한 조인 조건을 설정하기 위함입니다.

이유 2: 올바른 구문 형식

Supabase에서 테이블 간의 외래 키 관계를 통해 조인하려면 다음과 같은 형식을 사용해야 합니다:

• <별칭>:<테이블명>!<외래키명> (*)

예를 들어, contract 테이블과 trips 테이블 간의 외래 키 관계를 통해 조인하려면, 외래 키 명을 명확히 지정해야 합니다:

• contract:contract!contract_contract_trip_id_foreign (*)

여기서 contract_contract_trip_id_foreign는 외래 키 관계를 명확히 정의합니다. 이는 contract 테이블의 외래 키가 trips 테이블의 기본 키와 연결되어 있음을 나타냅니다.

예시 구문과 비교

잘못된 구문

await supabase
    .from('trips')
    .select('*, contract:contract_trip_id (*)')
    .order('trip_created_at', { ascending: false });

• 이 구문은 contract_trip_id가 외래 키로서 정확히 어떤 관계를 정의하는지 명확하지 않기 때문에 에러가 발생합니다.

올바른 구문

await supabase
    .from('trips')
    .select('*, contract:contract!contract_contract_trip_id_foreign (*)')
    .order('trip_created_at', { ascending: false });

• 이 구문은 contract 테이블과 trips 테이블 간의 외래 키 관계를 명확히 정의하여, 조인 조건을 명확히 설정합니다.

정리

contract:contract_trip_id (*) 구문에서 에러가 나는 이유는 외래 키 관계를 명확히 정의하지 않았기 때문입니다. Supabase는 조인할 때 테이블 간의 외래 키 관계를 명확히 정의해야 하며, 이를 위해 올바른 구문 형식을 사용해야 합니다. contract:contract!contract_contract_trip_id_foreign (*)와 같이 외래 키 관계를 명확히 지정하는 것이 필요합니다.

마지막 다섯번째 질문

알겠습니다. 그렇다면 마지막으로 첫번째 사례는 buddies:story_created_by 이렇게만 썼는데도 되는 이유가 궁금합니다.

다섯번째 질문에 대한 GPT 쌤의 답변

첫 번째 사례에서 buddies:story_created_by처럼 간단하게 작성했을 때 동작하는 이유는, Supabase가 이 구문을 통해 단일 관계를 쉽게 추론할 수 있기 때문입니다. 이를 좀 더 구체적으로 설명드리겠습니다.

첫 번째 사례 다시 보기:

await supabase
    .from('stories')
    .select('*, buddies:story_created_by (*)')
    .order('story_created_at', { ascending: false });

이유 1: 단순한 관계 추론 가능

• story_created_by는 stories 테이블의 외래 키로서, buddies 테이블의 기본 키를 참조하고 있습니다.
• Supabase는 이 외래 키가 명확히 buddies 테이블의 기본 키와 연결되어 있음을 추론할 수 있습니다.
• 따라서, 별도의 명시적인 관계 정의 없이도 Supabase는 이 외래 키를 기반으로 조인을 수행할 수 있습니다.

이유 2: 기본 외래 키 관계 사용

• story_created_by가 buddies 테이블과의 외래 키 관계를 형성하고 있으며, 이 관계가 기본적으로 설정된 경우, Supabase는 이를 자동으로 인식합니다.
• 일반적으로, 테이블 간의 외래 키 관계는 데이터베이스 스키마에서 명확히 정의됩니다. 예를 들어, stories 테이블의 story_created_by 열이 buddies 테이블의 기본 키를 참조하는 외래 키로 설정된 경우입니다.
• Supabase는 이러한 기본 외래 키 관계를 자동으로 인식하여, 별도의 명시적인 외래 키 이름 없이도 조인을 수행할 수 있습니다.

올바른 예제에서의 동작 원리

• buddies:story_created_by (*) 구문은 stories 테이블의 story_created_by 외래 키를 통해 buddies 테이블과 조인합니다.
• story_created_by 열은 buddies 테이블의 기본 키를 참조하므로, Supabase는 이를 쉽게 추론할 수 있습니다.
• 따라서, 명시적인 외래 키 관계 정의 없이도 조인이 가능합니다.

요약

• 첫 번째 사례에서 buddies:story_created_by (*) 구문이 동작하는 이유는, Supabase가 story_created_by 외래 키를 통해 buddies 테이블과의 단일 관계를 추론할 수 있기 때문입니다.
• story_created_by 열이 buddies 테이블의 기본 키를 참조하는 외래 키로 설정되어 있어, 별도의 명시적인 관계 정의 없이도 조인이 가능합니다.
• 이는 데이터베이스 스키마에서 명확히 정의된 외래 키 관계를 Supabase가 자동으로 인식하여 조인을 수행할 수 있기 때문입니다.