How to join tables and make the results available

Joining tables

Typically, you design the data for your application so that there are neat places to store all the different types of data. You could have separate tables for INSTRUMENTS, CURRENCIES and COUNTERPARTIES, for example. And then you might have more tables for ORDERS, TRADES and CLIENT_DETAILS.

However, when you want to display data and provide it for reports, you want a view that takes data from more than one table.

A record on your trade table might include a client ID, but you would probably want to include the full client name in a display - so you create a view that joins one table to another.

Database normalisation

Genesis follows the principles of database normalisation (1nf, 2nf and 3nf) for organising data efficiently. These are designed to avoid unnecessary duplication and complexity.

Viewing the data

Now you have something called a view, which joins one table to another (or others) to take information from them.

Now you need to make this view available so that the front end can retrieve and display the information.

You need to create a requestReply (if the information is static) or a query (if you want to stream data as it changes). This is a simple statement that makes the view you have created available as a source of data for the front end.

In summary

Databases store information in neat tables. But in many cases, you need to view information that is stored in two or more tables. For this, you create views, which join together information in different tables.

To make a view available as a data source for the front end, you must create a request reply or a query that identifies the view.

It is worth noting that a view does not itself contain any information - that is only contained in the tables in your database.

You create all your views in your application's app-name-view-dictionary.kts file.

You create all your requestReplies in your application's app-name-reqrep.kts file.

You create all your queries in your application's app-name-dataserver.kts file.

Creating a simple view

Here is an example of a simple app-name-view-dictionary.kts file that defines one view. This view enables the app to present details of client trades, which include the details of the trade itself (from the TRADE table) and the client's details (from the CLIENT_DETAILS table).

• The view has been given the name TRADE_CLIENT.
• You need a root table as the basis of the view - in this case, TRADE.
• There is a join that connects this table to the CLIENT_DETAILS table
• By default, all fields from the joined table become part of the view. In this case, we have specified that only the field CLIENT_NAME is added from the joined table.

views {
    view("TRADE_CLIENT", TRADE) {
        joins {
            joining(CLIENT_DETAILS) {
                on(TRADE.CLIENT_ID to CLIENT_DETAILS {CLIENT_ID})
            }
        }
        fields {
            TRADE.allFields()
            CLIENT_DETAILS.CLIENT_NAME
        }
    }
}

Creating a simple Data Server query

Here is an example of an application's application's app-name-dataserver.kts file. It contains one query that makes the view TRADE_CLIENT available as a data source to the front end. So, the front end can now display details of all the client trades.

• The query has been given the name ALL_TRADE_CLIENTS. This is the name that the front end can call to retrieve the data from the two underlying tables.

dataServer {
    query("ALL_TRADE_CLIENTS", TRADE_CLIENT) {}
}

This enables the created view to be visible to the front end (or through a Rest API call).

Adding permissions to the Data Server query

As the view is not accessible to the outside world, all authorisation must be done at the dataserver level (not shown here).

You can also add permissions to each individual view, requestReply and query so that only authorised users have access to the information.

Code to follow.

Testing

info

Go to our Testing page for details of our testing tools and the dependencies that you need to declare.

To test your auth set-up on your app:

• Details to follow shortly. Thank you for your patience.