Relation

Relations represent crucial associations between two models, illustrating the intricate connections and interactions within a system. Accio provides three types of Relations to effectively describe the associations between models: ONE_TO_ONE, ONE_TO_MANY and MANY_TO_ONE.

To ensure clarity in our explanation, let's establish some terminology.

For any TO_ONE type of relation, we have two models involved: the source and the target. This implies that the source table contains a relation field that links to the target table. For example, the Orders table has a customer field, which serves as a relation field connecting to the Customer table through the OrdersCustomer relation. In this relationship, we refer to Orders as the source and Customer as the target.

On the other hand, for any TO_MANY type of relation, we use the terms one side table and many side table to describe them. For instance, the Customer table has an orders column that serves as a relation field linking to the Orders table through the OrdersCustomer relation. Here, the OrdersCustomer relation is a MANY_TO_ONE relation. When accessing data from the Customer table, the final result will be a collection of many results.

By establishing these definitions, we can provide clear and consistent explanations regarding the different types of relations in Accio.

ONE_TO_ONE

ONE_TO_ONE relation means each record in the source table maps to only one record in the target table. For instance, User and Profile have a ONE_TO_ONE relation.

model User @sql(select * from user) {
    id: INTEGER @primaryKey
    name: VARCHAR
    profile: Profile @relation(UserProfile)
}

model Profile @sql(select * from Profile) {
    id: INTEGER @primaryKey
    userId: INTEGER
    address: VARCHAR
  phone: VARCHAR
  user: User @relation(UserProfile)
}

relation UserProfile @condition(User.id = Profile.userId) {
    models: [User, Profile]
    type: "ONE_TO_ONE"
}

One user only can be mapped to one profile.

ONE_TO_MANY

ONE_TO_MANY relation means each record in the one table maps to many records in the many table. For instance, Customer and Orders have a ONE_TO_MANY relation.

model Customer @sql(select * from customer) {
    custkey: INTEGER @primaryKey
  name: VARCHAR
  orders: Orders[] @relation(CustomerOrders)
}

model Orders @sql(select * from orders) {
    orderkey: INTEGER @primaryKey
    custkey: INTEGER
    orderstatus: OrderStatus
    customer: Customer @relation(CustomerOrders)
}

relation CustomerOrders @condition(Customer.custkey = Orders.custkey) {
    models: [Customer, Orders]
    type: "ONE_TO_MANY"
}

MANY_TO_ONE

MANY_TO_ONE relation means each record in the many table maps to one records in the one table. For instance, Orders and Customer have a MANY_TO_ONE relation.

model Customer @sql(select * from customer) {
    custkey: INTEGER @primaryKey
  name: VARCHAR
  orders: Orders[] @relation(CustomerOrders)
}

model Orders @sql(select * from orders) {
    orderkey: INTEGER @primaryKey
    custkey: INTEGER
    orderstatus: OrderStatus
    customer: Customer @relation(CustomerOrders)
}

relation CustomerOrders @condition(Customer.custkey = Orders.custkey) {
    models: [Customer, Orders]
    type: "ONE_TO_MANY"
}

The MDL is same as ONE_TO_MANY case. Even though CustomerOrders may appear as ONE_TO_MANY, Accio automatically reverses this relation when invoked by Orders. Users only need to define a single relation between the models, and Accio handles the mapping based on the context. This simplifies the process and ensures accurate relations between the models.

Some limitations of Relation Definition

In Accio, the ordering of models and join conditions holds significant importance. Accio analyzes the join condition to determine the join keys of the left and right tables. It is crucial for users to write the SQL query based on the information provided in the models attribute.

For instance, if the models are defined as models: [Customer, Orders], the join condition should be written as Customer.custkey = Orders.custkey. It should not be reversed as Orders.custkey = Customer.custkey.

The join condition should consist of a simple comparison expression, where the left and right expressions are join keys prefixed with the respective model names. For example, Customer.custkey = Orders.custkey. Currently, complex comparison expressions such as Customer.custkey = Orders.custkey AND Customer.name = Orders.orderstatus are not supported in Accio.

By adhering to these guidelines, users can ensure the accurate and effective utilization of join conditions in their Accio queries.