join

Definition

In some situations, your data schema may contain intermediate tables (a.k.a. junction tables), which represent many-to-many relationships.

However, these tables can be confusing for Business Users to understand, since they only serve a purely relational modelling purpose and not a business concept per se.

In these cases, you may want to define a join to go over these tables and hide them from your business users.

Let's look into some example of schemas that we would like to simplify to our business users over Veezoo using the property join.

Usage

A join can be used inside relationship, number / integer, boolean, string and date. It is always defined as an Array of SQL Expressions, even if there is only one join needed.

Intermediate Table

A very common kind of modelling in relational databases is that of an intermediate table with two foreign keys (and optionally a primary key).

Example: A table CUSTOMER_LANGUAGE(customer_id, language_id) that specifies the languages that a customer speaks, by using two foreign keys.

In this case, this is how we would model it in Veezoo:

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

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

kb {

    class Customer {
        name.en: "Customer"
        sql: "${CUSTOMER.id}"
        
        relationship speaks_Language {
            display_name.en: "speaks"
            
            tag: KB_Many2Many

            to: kb.Language

            // CUSTOMER -> CUSTOMER_LANGUAGE
            join: [
              "${CUSTOMER.id} = ${CUSTOMER_LANGUAGE.customer_id}"
            ]

            // this is used to join with LANGUAGE.id (the sql for kb.Language)
            sql: "${CUSTOMER_LANGUAGE.language_id}"
        }
    }
}

First of all, we needed to add to our import another table reference, namely to CUSTOMER_LANGUAGE, so we can use its shorthand form.

Similarly to the case of Foreign Keys described in the sql Overview for Class Relations, we also need to specify the sql here.

Additionally, we see for the first time the usage of join. A relationship always goes from the parent class (e.g. kb.Customer) to another class (e.g. kb.Language). Since the join path goes over an intermediate table, we need to specify how we get to the final CUSTOMER_LANGUAGE table, namely first over the customer_id foreign key.

Finally, notice that this relationship has an explicit tag set, in contrast to most of our relationships. KB_Many2Many specifies that this is a many-to-many relationship, i.e. that customers may speak multiple languages and that languages may be spoken by multiple customers. The default is KB_Many2One and is used when nothing is set.

Now, there is nothing special about this two foreign keys case. Maybe your intermediate table contains actually a numeric column, a date, boolean, instead of a second foreign key.

Let's say we have a table like CUSTOMER_EXTRA(customer_id, age). Following the same idea, we can have a new attribute defined like this:

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

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

kb {

    class Customer {
        name.en: "Customer"
        sql: "${CUSTOMER.id}"

        integer Age {
            name.en: "Age"

            // CUSTOMER -> CUSTOMER_EXTRA
            join: [
              "${CUSTOMER.id} = ${CUSTOMER_EXTRA.customer_id}"
            ]

            sql: "${CUSTOMER_EXTRA.age}"
        }
    }
}

Multi-Joins

If the schema is more complicated, we may need to go over several intermediate tables to find the information we need.

Let's look at the following artificial example with tables CUSTOMER(id, name), CUSTOMER_INFO(customer_id, customer_extra_id), CUSTOMER_EXTRA(id, age).

In this case, we will need to define how the path of join needs to be performed.

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

import: [
    db.postgres.movie_db.public.CUSTOMER,
    db.postgres.movie_db.public.CUSTOMER_INFO,
    db.postgres.movie_db.public.CUSTOMER_EXTRA
]

kb {

    class Customer {
        name.en: "Customer"
        sql: "${CUSTOMER.id}"

        integer Age {
            name.en: "Age"

            // CUSTOMER -> CUSTOMER_INFO -> CUSTOMER_EXTRA
            join: [
             "${CUSTOMER.id} = ${CUSTOMER_INFO.customer_id}", 
             "${CUSTOMER_INFO.customer_extra_id} = ${CUSTOMER_EXTRA.id}"
            ]

            sql: "${CUSTOMER_EXTRA.age}"
        }
    }
}

If you need even more joins, just define them inside the array. Also, don't forget to add the table to the import statement at the top.

Advanced Joins

Finally, your Join may not be of the form 'column A = column B'. Still, with VKL you can put whatever fancy SQL expression you want for the join.

Let's take the following example.

We have the tables CUSTOMER(id, name, private_id, company_id, segment_valid) and CUSTOMER_SEGMENT_INFO(id, website), where private_id and company_id are foreign keys to CUSTOMER_SEGMENT_INFO, but only one is set at a time and the reference only makes sense if segment_valid is set to 1, because... why not. And we want in the end to get the website.

This is how it will look like:

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

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

kb {

    class Customer {
        name.en: "Customer"
        sql: "${CUSTOMER.id}"

        string Website {
            name.en: "Website"

            // CUSTOMER -> CUSTOMER_SEGMENT_INFO
            join: [
              "COALESCE(${CUSTOMER.private_id}, ${CUSTOMER.company_id}) = ${CUSTOMER_SEGMENT_INFO.id} AND ${CUSTOMER.segment_valid} = 1"
            ]

            sql: "${CUSTOMER_SEGMENT_INFO.website}"
        }
    }
}