Views - basics
A view definition is made up of joins and fields.
Joins
Starting with one table (the root table), a join enables you to fetch one or more fields from another table so that it can be served and viewed as a single query row. For any join, one of the fields must be an index.
For example, you might want to join a TRADE
to an INSTRUMENT
in order to pull in INSTRUMENT
fields such as the NAME
or CURRENCY
to serve up to a grid.
views {
view("TRADE_VIEW", TRADE) {
joins {
joining(INSTRUMENT) {
on(TRADE.INSTRUMENT_ID to INSTRUMENT { INSTRUMENT_ID })
}
}
fields {
TRADE.allFields()
INSTRUMENT.NAME withPrefix INSTRUMENT
INSTRUMENT.CURRENCY_ID withAlias "CURRENCY"
}
}
}
You can define multiple joins onto any given table.
You can also add nested joins (join to a joined table), and so on...
The following example shows three key things:
-
It joins the table
POSITION
ontoALT_INSTRUMENT_ID
on a 2-field key. One of the join values (ALTERNATE_TYPE
) uses a literal value as opposed to stemming from a field inPOSITION
. -
There is also a nested join, from
ALT_INSTRUMENT_ID
ontoINSTRUMENT_L1_PRICE
. -
Finally, there is a join from
POSITION
onto theINSTRUMENT
table.
joins {
joining(ALT_INSTRUMENT_ID) {
on(POSITION.INSTRUMENT_ID to ALT_INSTRUMENT_ID { INSTRUMENT_ID })
.and(ALT_INSTRUMENT_ID { ALTERNATE_TYPE } to "REFINITIV")
.joining(INSTRUMENT_L1_PRICE) {
on(ALT_INSTRUMENT_ID.INSTRUMENT_CODE to INSTRUMENT_L1_PRICE { INSTRUMENT_CODE })
}
}
joining(INSTRUMENT) {
on(POSITION.INSTRUMENT_ID to INSTRUMENT { INSTRUMENT_ID })
}
}
It is worth noting that only the root table is monitored for changes by default. if you are working with real-time data in your views, you might need to use backwards joins, which we shall look at later in this page.
Joins can be one-to-one (key field match) or one-to-many (part-key-field match).
Views with one-to-many joins cannot be used with Data Servers.
Fields
Fields allow you to define which fields you would like to include in your view. You can reference fields from any of the tables that have been joined inside your view.
Adding a field is as simple as typing it in the fields
section of the view.
fields {
INSTRUMENT.CURRENCY_ID
}
You can add all the fields from a given table to a view using the allFields
accessor.
fields {
TRADE.allFields()
}
You can also access derived fields that were defined in the tables dictionary. This is just the same as any other field. For example, if the table TRADE has a derived field called FAVOURITE
, you could use it as follows:
fields {
TRADE.FAVOURITE
}
Overriding a field name
You can override the name of a field using various operators:
withAlias
- gives the field an alternative name on the viewwithPrefix
- adds a prefix to the standard field name; this is useful if you have a clash (e.g. if COUNTERPARTY and INSTRUMENT both have a NAME field)
COUNTERPARTY.NAME withPrefix COUNTERPARTY
INSTRUMENT.NAME withPrefix INSTRUMENT
INSTRUMENT.CURRENCY_ID withAlias "CURRENCY"
Backwards joins
Backwards joins are important if your view is used in any real-time activity - for example, if you are going to stream data from the view or if you have consolidators monitoring the view.
-
By default, the platform (specifically, the Update Queue) only listens for changes on the root table of the view.
-
If you want to include real-time updates from the fields in a joined table, then you have to specify that this a backwards join. With a backwards join, the joined table is monitored for changes as well as the root table.
Below is an example view that contains a default join and backwards joins. The root table is the POSITION table, and it has two joins:
- The first join has two backwards joins to ensure real-time updates of all fields; the first is to the ALT_INSTRUMENT_ID table, where it picks up an alternative ID for the instrument; the second is to the INSTRUMENT_L1_PRICE table, to pick up the price for the instrument. This is effectively a chain, so note that you cannot make a normal join and then make a backwards join from that table to another one. That would not work.
- The second join is from the root table (POSITION) to the INSTRMENT table, where it picks up the instrument ID. There is no requirement to monitor this in real time, so this is not a backwards join.
view("POSITION_VIEW", POSITION) {
joins {
joining(ALT_INSTRUMENT_ID, backwardsJoin = true) {
on(POSITION.INSTRUMENT_ID to ALT_INSTRUMENT_ID { INSTRUMENT_ID })
.and(ALT_INSTRUMENT_ID { ALTERNATE_TYPE } to "REFINITIV")
.joining(INSTRUMENT_L1_PRICE, backwardsJoin = true) {
on(ALT_INSTRUMENT_ID.INSTRUMENT_CODE to INSTRUMENT_L1_PRICE { INSTRUMENT_CODE })
}
}
joining(INSTRUMENT) {
on(POSITION.INSTRUMENT_ID to INSTRUMENT { INSTRUMENT_ID })
}
}
}
There is a processing overhead in monitoring the joined tables. In some cases, this can be significant.
Only specify a backwards join where it is essential to monitor the fields in real time.