sql

Definition

A relationship in VKL describes how classes relate to each other.

The sql property specifies how these classes connect to each other in the database. These are usually foreign keys in your database.

Usage

Let's say we have the following tables: CUSTOMER(id, name) and ORDER(id, customer_id, total_order).

In this case of Foreign Keys, the sql of the connecting classes (e.g. kb.Order and kb.Customer) are not both in the same table.

Now, customer_id is a foreign key pointing to the CUSTOMER table. This is how our new classes and relationship will look like.

File: hitchhiker/knowledge-base/classes/Customer.vkl

import: [
    db.postgres.movie_db.public.CUSTOMER
]

kb {

    class Customer {
        name.en: "Customer"

        sql: "${CUSTOMER.id}"
        name_sql: "${CUSTOMER.name}"
    }
}

File: hitchhiker/knowledge-base/classes/Order.vkl

import: [
    db.postgres.movie_db.public.ORDER
]

kb {

    class Order {
        name.en: "Order"
        sql: "${ORDER.id}"

        relationship from_Customer {
            to: kb.Customer
            sql: "${ORDER.customer_id}"
        }

        ...
    }
}

The relationship from_Customer has a sql defined that uses the foreign key column customer_id. Since the to is set to kb.Customer, Veezoo can now correctly do the join using the sql from the relationship and the sql from the referenced class kb.Customer, like this: ${ORDER.customer_id} = ${CUSTOMER.id}.

Keep in mind that sql accepts any kind of SQL expression (e.g. concatenation of multiple columns) and therefore the join does not need to be of the type "column A = column B". For joins that need to go over multiple tables or have a more advanced SQL expression, refer to join.