Column Directive

Column directive allows instruction of a data source and modification of its presentation. Column directives are defined relative to the table that they are part of. They can be used in visible-columns or a visible-foreign-keys annotation.

  • In Overall structure we briefly explain different methods of defining a column directive.
  • In Shorthand syntax we mention the alternative and simpler way of defining column directives.
  • Using Properties section you can find all the available properties in column directive.
  • Please Find the examples in this section.

Overall structure

As it was described, column directives are meant to intruct the data source and its presentation. Based on how the data source is defined, we can categorize them into the following:

1. Column directive with source

In this category, you use the source property to define the data source of the column directive in place. Other source related properties (i.e. entity, aggregate) can be used in combination with source to change the nature of the column directive. The following is an overview of such column directive with all the available properties (some might not be applicaple depending on where the column directive is used):

{
  "source" : <source path>,
  "entity": <true or false>,
  "aggregate": <aggregate function>,
  "self_link": <boolean>
  "markdown_name": <display name>,
  "comment": <tooltip message>,
  "comment_display": <inline|tooltip>,
  "hide_column_header": <boolean>
  "display": {
      "markdown_pattern": <pattern>,
      "template_engine": <handlebars or mustache>,
      "wait_for": <wait_for list>,
      "show_foreign_key_link": <boolean>,
      "show_key_link": <boolean>,
      "array_ux_mode": <csv|ulist|olist|raw>
  },
  "array_options": {
    "order": <change the default order>,
    "max_lengh": <max length>
  }
}

2. Column directive with sourcekey

In this category, the sourcekey proprety is used to refer to one of the defines sources in the source-definitions annotations. The following is an overview of such column directive with all the available properties (some might not be applicaple depending on where the column directive is used):

{
  "sourcekey" : <source key>,
  "markdown_name": <display name>,
  "comment": <tooltip message>,
  "comment_display": <inline|tooltip>,
  "hide_column_header": <boolean>
  "display": {
      "markdown_pattern": <pattern>,
      "template_engine": <handlebars or mustache>,
      "wait_for": <wait_for list>,
      "show_foreign_key_link": <boolean>,
      "show_key_link": <boolean>,
      "array_ux_mode": <csv|ulist|olist|raw>
  },
  "array_options":{
    "order": <change the default order>,
    "max_lengh": <max length>
  }
}

3. Column directive without any source

If you want to have a column directive that its value is made up of multiple column directives, you don’t need to define any source or sourcekey. The only required attributes for these types of columns (we call them virtual columns) are markdown_name that is used for generating the display name, and markdown_pattern under display to get the value. For instance the following is an acceptable virtual column:

{
  "markdown_name": "displayname value",
  "display": {
      "markdown_pattern": "{{{column1}}}, {{{column2}}}"
  }
}

In order to access the data of other column directives in this virtual column, you can use the wait_for option.

The following is an overview of such column directive with all the available properties (some might not be applicaple depending on where the column directive is used):

{
  "markdown_name": <display name>,
  "comment": <tooltip message>,
  "comment_display": <inline|tooltip>,
  "hide_column_header": <boolean>
  "display": {
      "markdown_pattern": <pattern>,
      "template_engine": <handlebars or mustache>,
      "wait_for": <wait_for list>
  }
}

Properties

Some properties are only available in special scenarios and are not used in all the scenarios. As you can see in the overall structure, there are three different ways that you can define a column directive:

1. Data source properties

These sets of properties change the nature of the column directive, as they will affect the communication with server. To detect duplicate column-directives we only look for these attributes. The properties are:

These attribute will also dictate the default display heuristics that are used for the column. For instance if it’s an aggregate, it will require an extra request and will therefore modify the heuristics for displaying the values.

source

This property allows the definition of “source path”. As column directive is define on a table, it can either be

  • One of the current table’s column.
  • A column in table that has a valid foreign key relationship with the current table.
Even if the column directive is used in “entity” mode where it’s suppsoed to represent the row and not just a column, we must record this column choice explicitly in the column directive so that we can use an unambiguous column while communicating with ERMrest.

Therefore the following are acceptable ways of defining source path:

  • A column name string literal (an array of one string is also acceptable):

    {"source": "column"}
    {"source": ["column"]}
    
  • An array of path element that ends with a columnname that will be projected and filtered.

    • [ path element , ... , columnname]

    Each anterior element MAY use one of the following sub-document structures:

    • { "sourcekey": sourcekey prefix }
    • { direction : fkeyname }
      • Links a new table instance to the existing path via join.
      • direction can either be "inbound", or "outbound".
      • fkeyname is the given name of the foreign key which is usually in the following format: [ schema name , constraint name ]
    • { "and": [ filter ,], "negate": negate }
      • A logical conjunction of multiple filter clauses is applied to the query to constrain matching rows.
      • The logical result is negated only if negate is true.
      • Each filter clause may be a terminal filter element, conjunction, or disjunction.
    • { "or": [ filter ,], "negate": negate }
      • A logical disjunction of multiple filter clauses is applied to the query to constrain matching rows.
        • The logical result is negated only if negate is true.
        • Each filter clause may be a terminal filter element, conjunction, or disjunction.
    • { "filter": column , "operand_pattern": value , "operator": operator , "negate": negate }
      • An individual filter path element is applied to the query or individual filter clauses participate in a conjunction or disjunction.
      • The filter constrains a named column in the current context table.
      • The operator specifies the constraint operator via one of the valid operator names in the ERMrest REST API, which are
    | operator  | meaning |
    |-----------|---------|
    | `::null::`| column is `null`     |
    | `=`      | column equals value |
    | `::lt::` | column less than value |
    | `::leq::` | column less than or equal to value |
    | `::gt::` | column greater than value |
    | `::geq::` | column greater than or equal to value |
    | `::regexp::` | column matches regular expression value |
    | `::ciregexp::` | column matches regular expression value case-insensitively |
    | `::ts::` | column matches text-search query value |
  > If `operator` is missing, we will use `=` by default.

- The _value_ specifies the constant operand for a binary constraint operator and must be computed to a non-empty value. [Pattern expansion](annotation.md#pattern-expansion) MAY be used to access [the pre-defined values in templating envorinment](mustache-templating.md#using-pre-defined-attributes). Like other pattern expansions the default `template_engine` will be applied and if you want to change it, you can define `template_engine` alongside the `operand_pattern`.

- The logical result of the constraint is negated only if _negate_ is `true`.

The following are some examples of defining source path:

[{"inbound": ["S1", "FK1"]}, "Column2"]
[{"inbound": ["S1", "FK1"]}, {"outbound": ["S2", "FK2"]}, "Column3"]
[{"sourcekey": "path_to_f1"}, {"outbound": ["S2", "FK2"]}, "Column3"]
[{"sourcekey": "path_to_f2"}, "Column3"]
[{"inbound": ["S1", "FK1"]}, {"filter": "RCB", "operand_pattern": "{{{$session.client.id}}}"}, "Column3"]
Source path with reusable prefix

In some cases, the defined foreign key paths for different columns/facets might be sharing the same prefix. In those cases, reusing the prefix allows sharing certain joined table instances rather than introducing more “copies” as each facet is activated which in turn will increase the performance.

To do this, you would have to use 2019:source-definitions annotation and define the shared prefix. Then you can use sourcekey to refer to this shared prefix in the source path.

  • When using a prefix, the prefix’s last column and all the other extra attributes on it will be ignored for the purpose of prefix.

  • You can use recursive prefixes. If we detect a circular dependency, we’re going to invalidate the given definition.

  • While using prefix, you MUST add extra foreign key paths to the relationship. The following is not an acceptable source:

    [ {"sourcekey": "path_2"}, "RID" ]
    
  • Since our goal is to reuse the join instance as much as we can, all-outbound foreign keys can also share the same join instances.

For example, assume the following is the ERD of table:

erd_01

And the following is source-definition and visible-columns annotation:

"tag:isrd.isi.edu,2019:source-definitions`": {
 "sources": {
   "path_to_o1": {
       "source": [
           {"outbound": ["schema", "const1"]},
           "o1_col"
       ]
   },
   "path_to_o1_o1": {
       "source": [
           {"sourcekey": "path_to_path_prefix_o1"},
           {"outbound": ["schema", "const2"]},
           "o1_o1_col"
       ]
   }
 }
},
"tag:isrd.isi.edu,2016:visible-columns": {
 "compact": [
   "id",
   {
     "sourcekey": "path_to_o1",
   },
   {
     "sourcekey": "path_to_o1_o1",
   },
   {
     "source": [
       {"sourcekey": "path_to_o1"},
       {"outbound": ["schema", "const3"]},
       "o1_o1_o1_col"
     ]
   }
 ]

}

Then this is a valid facet blob:

{
  "and": [
    {
        "source":  [
            {"sourcekey": "path_to_o1_o1"},
            {"inbound": ["faceting_schema", "const4"]},
            "o1_o1_i1_col"
        ],
        "choices": ["v1"]
    },
    {
        "sourcekey": "path_to_o1_o1",
        "choices": ["v2"]
    }
  ]
}

Which is roughly translated to the following ERMrest query:

M:=schema:main/
M_P2:=(fk_col)=(schema:o1:RID)/M_P1:=(fk_col)=(schema:o1_o1:RID)/
(RID)=(schema:o1_o1_i1:fk_col)/o1_o1_i1_col=v1/$M/

$M_P1/o1_o1_col=v2/$M/

$M_P1/F3:=left(fk_col)=(schema:o1_o1_o1:RID)/$M/
RID;M:=array_d(M:*),F3:=array_d(F3:*),F2:=array_d(M_P1:*),F1:=array_d(M_P2:*)@sort(RID)

sourcekey

Instead of defining a column directive in place, you can define them in the source-definitions annotations, and refer to those definitions using sourcekey. If sourcekey is defined on a column directive, the rest of data source attributes defined on the column directive will be ignored (but you still can modify the display and other types of attributes).

entity

If the column directive can be treated as entity (the column that is defined in source path is key of the table), setting entity attribute to false will force the scalar mode. This will affect different logic and heuristics. In a nutshell, entity-mode means we try to provide a UX around a set of entities (table rows). Scalar mode means we try to provide a UX around a set of values like strings, integers, etc.

"entity": false

aggregate

This is only applicable in visible columns definition (Not applicable in Facet definition). You can use this attribute to get aggregate values instead of a table. The available functions are cnt, cnt_d, max, min, array, and array_d.

  • min and max are only applicable in scalar mode.
  • array will return ALL the values including duplicates associated with the specified columns. For data types that are sortable (e.g integer, text), the values will be sorted alphabetically or numerically. Otherwise, it displays values in the order that it receives from ERMrest. There is no paging mechanism to limit what’s shown in the aggregate column, therefore please USE WITH CARE as it can incur performance overhead and ugly presentation.
  • array_d will return the distinct values. It has the same performance overhead as array, so pleas USE WITH CARE.

2. Presentation properties

The following attributes can be used to manipulate the presentation settings of the column directive.

markdown_name

markdown_name captures the display name of the column. You can change the default display name by setting the markdown_name attribute.

"markdown_name": "**new name**"

comment

In Chaise, comment is displayed as tooltip associated with columns. To change the default tooltip for the columns, the comment attribute can be used.

"comment": "New comment"

comment_display

By default Chaise will display comment as a tooltip. Set this value to inline to show it as text or tooltip to show as a hover tooltip. This property is only supported for related tables in detailed context of visible-foreign-keys annotation, and is not honored in other annotations.

hide_column_header

By setting this to true, chaise will hide the column header (and still show the value). This is only supported in detailed context. If this attribute is missing, we are going to use the inherited behavior from the column display annotation. If that one is missing too, display annotation will be used.

display

By using this attribute you can customize the presented value to the users. The following is the accepted syntax:

{
    ...,
    "display": {
        "markdown_pattern": <markdown pattern value>,
        "template_engine": <"handlebars" | "mustache">,
        "wait_for": <wait_for list>,
        "show_foreign_key_link": <boolean>,
        "show_key_link": <boolean>
        "array_ux_mode": <csv|ulist|olist|raw>
    }
}
markdown_pattern

Allows modification of the displayed values for the column directive. You can access the current column directive data with $self namespace alongside the defined source definitions. Please refer to the Column directive display documentation for more information.

wait_for

Used to signal Chaise that this column directive’s markdown_pattern relies on the values of other column directives. It’s an array of sourcekeys that are defined in the source-definitions annotation of the table. You should list all the all-outbound, aggregates, and entity sets that you want to use in your markdown_pattern. Entity sets (column directives with inbound path and no aggregate attribute) are only acceptable in detailed context. Please refer to the column directive display documentation for more information.

array_ux_mode

If you have "aggregate": "array" or "aggregate": "array_d" in your column directive definition, you can use array_ux_mode attribute to change the display of values. You can use

  • olist for ordered bullet list.
  • ulist for unordered bullet list.
  • csv for comma-seperated values (the default presentation).
  • raw for space-seperated values.

array_options

If you have "aggregate": "array" or "aggregate": "array_d" in your column directive definition, you can use array_options to change the array of data that client will present. It will not have any effect on the generated ERMrest query and manipulation of the array is happening on the client side. The available options are:

  • order: An alternative sort method to apply when a client wants to semantically sort by key values. Its syntax is similar to column_order.
    • Assuming your path ends with column col, the default order is {"column": "col", "descending": false}.
    • In scalar mode, you can only sort based on the scalar value of the column (other table columns are not available). So you can only switch the sort from ascending to descending.
  • max_length: A number that defines the maximum number of elements that should be displayed. We are not going to apply any default value for this attribute. If you don’t provide any max_length, we are going to show all the values that ERMrest returns.
{
  "source": <a valid path in entity mode>,
  "entity": true,
  "aggregate": <array or array_d>,
  "array_options": {
    "order": [
      {
        "column": <a column in the projected table>,
        "descending": <boolean value>,
      },
      {
        "column": <another column in the projected table>,
        "descending": <boolean value>,
      },
      ...
    ],
    "max_length": <number>
  }
},
{
  "source": <a valid path in scalar mode>,
  "entity": false,
  "aggregate": <array or array_d>,
  "array_options": {
    "order": [
      {
        "column": <the scalar projected column>,
        "descending": <boolean value>,
      }
    ],
    "max_length": <number>
  }
}