Skip to main content

Define Relations

Refer to the relations documentation for more examples and information about relationships between models.

After defining the models, we can establish relationships between them. These relationships describe how to link two models together and enable seamless data integration and analysis across both entities.

For example, between Orders and Customer, we can build a relation called OrdersCustomer.

Relation OrderCustomer @condition(Orders.custkey = Customer.custkey) @desc("Orders of Customer") {
models: [Orders, Customer]
type: "MANT_TO_ONE"
}

Relation field

After defining OrdersCustomer relation, we can then use it when defining relation fields in models.

Model Orders @sql(select * from tpch_tiny.orders) {
orderkey: INTEGER! @primaryKey
custkey: INTEGER!

...

customer Customer @relation(OrderCustomer)
}

Model Customer @sql(select * from tpch_tiny.customer) {
custkey: INTEGER
// columns of Customer

orders: Orders @relation(OrderCustomer)
}

The type of a Relation Field is determined by its target table name, indicating that the value of this field is an Object.

For example, the customer column represents a Customer object, allowing us to access the columns of the Customer table through this field . On the other hand, the orders column represents a ONE_TO_MANY relation (the reverse of OrdersCustomer), resulting in an array of Orders objects as the value of orders.

More details of defining a relation, see Relation

Access the relation field

Now, we can access orders from lineitems like

SELECT customer.name FROM Orders -- MANY TO ONE

For this query, Accio will generate an underlying statement executed in the data source:

WITH
Customer AS (
SELECT
c_custkey custkey
, c_name name
, 'relationship<OrdersCustomer>' order_field
FROM
(
SELECT *
FROM
tpch_tiny.customer
) t
)
, Orders AS (
SELECT
o_orderkey orderkey
, o_custkey custkey
, 'relationship<OrdersCustomer>' customer
FROM
(
SELECT *
FROM
tpch_tiny.orders
) t
)
, rs_gbz1a9ltdy AS (
SELECT DISTINCT
t.custkey custkey
, t.name name
, t.order_field order_field
, s.orderkey bk
FROM
(Orders s
LEFT JOIN Customer t ON (s.custkey = t.custkey))
)
SELECT rs_gbz1a9ltdy.name
FROM
(Orders
LEFT JOIN rs_gbz1a9ltdy ON (Orders.orderkey = rs_gbz1a9ltdy.bk))

Now, try query a to-many relation field like accessing an array of object.

SELECT orders[1].orderkey FROM Customer -- ONE TO MANY

For this query, Accio will generate an underlying statement executed in the data source:

WITH
Customer AS (
SELECT
c_custkey custkey
, c_name name
, 'relationship<OrdersCustomer>' orders
FROM
(
SELECT *
FROM
tpch_tiny.customer
) t
)
, Orders AS (
SELECT
o_orderkey orderkey
, o_custkey custkey
, 'relationship<OrdersCustomer>' customer
FROM
(
SELECT *
FROM
tpch_tiny.orders
) t
)
, rs_1bfku9765o AS (
SELECT
o.custkey custkey
, o.custkey bk
, array_agg(m.orderkey IGNORE NULLS ORDER BY m.orderkey ASC) orders
FROM
(Customer o
LEFT JOIN Orders m ON (o.custkey = m.custkey))
GROUP BY 1, 2
)
, rs_n6aea6789i AS (
SELECT
t.orderkey orderkey
, t.custkey custkey
, t.customer customer
, s.bk bk
FROM
(rs_1bfku9765o s
LEFT JOIN Orders t ON (s.orders[SAFE_ORDINAL(1)] = t.orderkey))
)
SELECT rs_n6aea6789i.orderkey
FROM
(Customer
LEFT JOIN rs_n6aea6789i ON (Customer.custkey = rs_n6aea6789i.bk))

Based on the aforementioned description, Accio has the capability to identify the relations being utilized and subsequently generate several Common Table Expressions (CTEs) to establish the connections between models. The expression of the relation field will be transformed to reflect the result of the corresponding CTE.

The relation field access allow users to traverse data without understanding the underlying relationships and avoid using join themselves.

More way to access relation columns, see How to query relations ?