Relational Query

Overview

Relational queries allow you to retrieve data from multiple related tables in a single request, avoiding the performance overhead of multiple queries. The system automatically identifies table relationships through PostgreSQL foreign key constraints.

Basic Concepts

• Primary table: The table containing the foreign key field, which is the "many" side in the relationship
• Related table: The referenced table, connected to the primary table through primary key and foreign key
• Join methods: Supports outer join (LEFT JOIN) and inner join (INNER JOIN)

Query Syntax

Join Methods

Join Method	Syntax	Description	SQL Equivalent
Outer Join	related_field(sub_field1,sub_field2)	Uses LEFT JOIN by default; returns primary table records even if no data exists in the related table	LEFT JOIN
Inner Join	related_field!inner(sub_field1,sub_field2)	Uses INNER JOIN; only returns records that exist in both tables	INNER JOIN

Related Field Expressions

Expression	Syntax	Example	Description
Foreign key field name	fk_field(sub_field_list)	director_id(name,country)	Most common approach, using the foreign key field name directly
Related table name	table_name(sub_field_list)	director(name,country)	Using the related table name
Foreign key constraint name	constraint_name(sub_field_list)	fk_relate_director(name,country)	Using the foreign key constraint name

Aliases

Usage	Syntax	Example	Description
Field alias	alias:related_field(sub_field_list)	d:director_id(name,country)	Set an alias for the related field
Self-referential alias	alias1:field1(...),alias2:field2(...)	manager:manager_id(name),mentor:mentor_id(name)	Aliases are required to distinguish self-referential fields

Many-to-One

Table and E-R Diagram

Query Example

Requirement: Query movie information along with the corresponding director details

Description: Join the director table through the foreign key field director_id to retrieve the director's id, name, and country. Using an outer join, movie records are returned even if some movies don't have 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

Table and E-R Diagram

Query Example

Requirement: Query movie information along with all reviews for each movie

Description: Through reverse association query, retrieve data from the review table associated with the film table. The system automatically identifies the film_id foreign key in the review table and returns all review content for each 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

Table and E-R Diagram

Query Example

Requirement: Query movie information along with award details

Description: One-to-one relationship query, where each movie has 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

Table and E-R Diagram

Relationship Identification 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 identifies many-to-many relationships without needing to specify the junction table in the request.

Query Example

Requirement: Query movie information along with the list of participating actors

Description: The many-to-many association is implemented through the junction table film_actor. The system automatically identifies the relationship and returns the 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-Referential

Table and E-R Diagram

Query Example

Requirement: Query employee information along with their manager and mentor details

Description: Self-referential queries must use aliases to distinguish different association fields. Here, manager and mentor aliases are used to represent the manager and mentor 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"
    }
]