Relationship Query

Overview

Relationship Query allows you to retrieve data from multiple related tables in a single request, eliminating the need for multiple queries and their associated performance overhead. The system automatically identifies table relationships through MySQL foreign key constraints.

Basic Concepts

• Child Table: The table containing the foreign key field, representing the "many-side" in the relationship.
• Parent Table: The table being referenced, which establishes a relationship with the child table via its primary key and the child table's foreign key.
• Join Type: Supports outer joins (LEFT JOIN) and inner joins (INNER JOIN)

Query Syntax

Join Type

Join Type	Syntax	Description	SQL Equivalent
Left Join	join field(subfield1, subfield2)	Uses LEFT JOIN by default, returning main table records even if the joined table has no data.	LEFT JOIN
Inner Join	join field!inner(subfield1, subfield2)	Uses INNER JOIN, returning only records where data exists in both tables.	INNER JOIN

Join Field Expression

Expression	Syntax	Example	Description
Foreign Key Field Name	foreign_key_field(subfield_list)	director_id(name,country)	The most common way, directly using the foreign key field name.
Join Table Name	table_name(subfield_list)	director(name,country)	Use the associated table name
Foreign Key Constraint Name	constraint_name(subfield_list)	fk_relate_director(name,country)	Use the name of the foreign key constraint

Alias Configuration

Usage	Syntax	Example	Description
Field Alias	Alias:Join Field(subfield list)	d:director_id(name,country)	Set an alias for the join field
Self-join Alias	alias1:field1(...),alias2:field2(...)	manager:manager_id(name),mentor:mentor_id(name)	Must use aliases to distinguish in self-joins

Many-to-One

Tables and E-R

Query Example

Requirement: Query movie information while retrieving detailed information of the corresponding director

Description: By associating the director table through the foreign key field director_id, obtain the director's id, name, and nationality information. Using outer join, movie records will still be returned even if some movies have no director information.

curl -i -X GET 'http://{{host}}/v1/rdb/rest/film?select=id,title,release_year,duration,director_id(id,name,country)' \
-H 'Authorization: Bearer <access_token>'

Response Example

HTTP/1.1 200 OK
Header:
    content-length: 573
    content-type: application/json; charset=utf-8
Body:
[
    {
        "director_id": {
            "country": "USA",
            "id": 1,
            "name": "Steven Spielberg"
        },
        "duration": 164,
        "id": 1,
        "release_year": 1993,
        "title": "Jurassic Park"
    },
    {
        "director_id": {
            "country": "UK",
            "id": 2,
            "name": "Christopher Nolan"
        },
        "duration": 172,
        "id": 2,
        "release_year": 2010,
        "title": "Inception"
    },
    {
        "director_id": {
            "country": "Japan",
            "id": 3,
            "name": "Hayao Miyazaki"
        },
        "duration": 98,
        "id": 3,
        "release_year": 2001,
        "title": "Spirited Away"
    },
    {
        "director_id": null,
        "duration": 100,
        "id": 4,
        "release_year": 2020,
        "title": "Unknown Film 1"
    },
    {
        "director_id": null,
        "duration": 100,
        "id": 5,
        "release_year": 2015,
        "title": "Untitled Project"
    }
]

One-to-Many

Tables and E-R

Query Example

Requirement: Query movie information while retrieving all reviews for that movie

Description: By reverse association querying, retrieve associated review table data from the film table. The system will automatically recognize the foreign key film_id in the review table and return all review content for that movie.

curl -i -X GET 'http://{{host}}/v1/rdb/rest/film?select=id,title,release_year,duration,review(content)' \
-H 'Content-Type: application/json' \
-H 'Authorization: Bearer <access_token>'

Response Example

HTTP/1.1 200 OK
Header:
    content-length: 437
    content-type: application/json; charset=utf-8
Body:
[
    {
        "duration": 142,
        "id": 1,
        "release_year": 1994,
        "review": [
            {
                "content": "One of the greatest movies ever made!"
            },
            {
                "content": "Tim Robbins and Morgan Freeman delivered outstanding performances."
            }
        ],
        "title": "The Shawshank Redemption"
    },
    {
        "duration": 173,
        "id": 2,
        "release_year": 1972,
        "review": [
            {
                "content": "Marlon Brando's performance is legendary."
            }
        ],
        "title": "The Godfather"
    },
    {
        "duration": 98,
        "id": 3,
        "release_year": 1994,
        "review": [

        ],
        "title": "Pulp Fiction"
    }
]

One-to-One

Tables and E-R

Query Example

Requirement: Query movie information and retrieve its award information

Description: One-to-one relationship query, where each movie corresponds to at most one award record.

curl -i -X GET 'http://{{host}}/v1/rdb/rest/film?select=id,title,release_year,duration,award(award_name,year)' \
-H 'Authorization: Bearer <access_token>'

Response Example

HTTP/1.1 200 OK
Header:
    content-length: 354
    content-type: application/json; charset=utf-8
Body:
[
    {
        "award": {
            "award_name": "Best Picture Nominee",
            "year": 1994
        },
        "duration": 142,
        "id": 1,
        "release_year": 1994,
        "title": "The Shawshank Redemption"
    },
    {
        "award": null,
        "duration": 154,
        "id": 3,
        "release_year": 1994,
        "title": "Pulp Fiction"
    }
]

Many-to-Many

Tables and E-R

Relationship Recognition Rules

💡 Note: Many-to-many relationships must be implemented through a junction table, which must contain two foreign key fields as part of the composite primary key.

The system automatically recognizes many-to-many relationships without the need to specify a junction table in the request.

Query Example

Requirement: Query movie information and retrieve the list of starring actors

Description: The many-to-many association is implemented through the junction table film_actor. The system automatically infers the relationship and returns actor information for each movie.

curl -i -X GET 'http://{{host}}/v1/rdb/rest/film?select=id,title,release_year,duration,actor(name,country)' \
-H 'Authorization: Bearer <access_token>'

Response Example

HTTP/1.1 200 OK
Header:
    content-length: 367
    content-type: application/json; charset=utf-8
Body:
[
    {
        "actor": [
            {
                "country": "USA",
                "name": "Morgan Freeman"
            }
        ],
        "duration": 142,
        "id": 1,
        "release_year": 1994,
        "title": "The Shawshank Redemption"
    },
    {
        "actor": [

        ],
        "duration": 175,
        "id": 3,
        "release_year": 1972,
        "title": "The Godfather"
    }
]

Self-Join

Tables and E-R

Query Example

Requirement: Query employee information and simultaneously retrieve their supervisor and mentor details.

Description: Aliases must be used to distinguish different join fields in self-join queries. Here, the aliases manager and mentor represent the supervisory and mentorship relationships, respectively.

curl -i -X GET 'http://{{host}}/v1/rdb/rest/employee?select=name,position,manager:manager_id(name,position),mentor:mentor_id(name,position)' \
-H 'Authorization: Bearer <access_token>'

Response Example

HTTP/1.1 200 OK
Header:
    content-length: 979
    content-type: application/json; charset=utf-8
Body:
[
    {
        "manager": null,
        "mentor": null,
        "name": "John Smith",
        "position": "CEO"
    },
    {
        "manager": null,
        "mentor": null,
        "name": "Sarah Johnson",
        "position": "CTO"
    }
]