hublify.dataset

Through this function you can access all your data of the datasets you have access to within Hublify.

Basically you are building and executing dataset-retrieval-query with these functions.

Behaves like a SQL's SELECT query
Provide fields to retrieve
It handles automatic connections to other datasets (SQL JOINs)
Provide filter & segments to apply
Returns one or multiple record-objects (associative arrays) as a numeric array

Example

For quick dive, here an example

{# Getting data (fields "pcode","name" and "price") from dataset "product" #}
{% set myData = hublify.dataset('product').fields(['pcode','name','price']).all %}

Functions

dataset(...)

Creates a dataset-query-object.

Parameter: "<dataset>" A string containing the dataset-label.

Return: This functions returns dataset-query-object.

{# Creating a standalone query-object #}
{% set myQueryObj = hublify.dataset('product') %}

field(...)

Adds one field to the dataset-query.

Parameter: Pass a string containing the field you want to add.

{# Adding the field "firstname" to the query #}
{% set myData = hublify.dataset('person').field('firstname').all %}

You can also cascade multiple field(...) functions. But see also to directly add an array of fields, using the fields(...)-function.

{# Cascaded adding of single fields #}
{% set myData = hublify.dataset('person').field('firstname').field('lastname').field('gender').all %}

fields(...)

Adds multiple fields you want to read to the dataset-query.

Parameter: Pass an array of strings of fields you want to add.

{# Adding three fields as an array to the query #}
{% set myData = hublify.dataset('person').fields(['firstname','lastname','gender']).all %}

measure(...)

Adds one measure field (typically Y-Axis) to the query.

Parameter:

$field string The field to apply the aggregation to.
$aggregation - string (required) One of: "SUM | COUNT | COUNTDISTINCT | AVG | MIN | MAX | DISTINCT".
$alias - string (optional) A field-label as which you want the aggregated field-values to be returned.

{% set myData = hublify.dataset('person')
    .measure('persid', 'COUNT, 'myPersonCount')
    .filter('someFilter', 'someValue') 
    .all() %}

filter(...)

Adds a filter to your dataset-query.

Parameter:

$field - string The field-label to apply the filter on.
$filterValue - string|int|float|array The value / expression to filter for.
$operator- string (optional, default NULL => "equal") The operator can be one of:
- "equal"|null - Exact search (one value). $value must be then a string. All other filters for this field are removed then!
- "search" - "Smart Filter": Allows to use complex search-phrases. $value must be then a string. Other filters are kept for this field - except an "equal"-filter!
- "in" - List-of-OR-equal-values. $value is typically then an array, e.g.: ["nike","reebok","adidas"]. $value can be a string also e.g.: "nike".
- "not_in" - List-of-AND-NOT-equal-values. $value is typically then an array, e.g.: ["nike","reebok","adidas"]. $value can be a string also e.g.: "nike".
- "range" - $value must be an array containing one or two of these keys:
  - [min] - number. Describing the range's lower
  - [max] - number. Describing the range's upper end.
- "null" - (! "null" as a written-STRING!)

{# Getting all persons "firstname" whos "lastname" is "skywalker" #}
{% set myData = hublify.dataset('person')
    .field('firstname')
    .filter('lastname','skywalker')
    .all()
%}

filters(...)

Adds multiple filters to your dataset-query at once.

{# Getting all persons "firstname" filtered for the "firstname" and "lastname" #}
{% set myData = hublify.dataset('person').field('firstname').filters([ {'lastname':'skywalker'}, {'firstname':'luke'}]).all %}

segment(...)

Adds a filtering segment to your dataset-query.

{# Adding the segment "bday_next_within_7days" to the query #}
{% set myData = hublify.dataset('person').segment('bday_next_within_7days').all %}

Of course you can combine segments with other filters!

segments(...)

Adds multiple segments for filtering to your dataset-query at once.

{# Adding multiple segments to the query #}
{% set myData = hublify.dataset('person').segments(['bday_next_within_7days','a-customer']).all %}

groupBy(...)

Adds a groupBy-Field to the query.

You can apply multiple groupBy(...) multiple times.

Parameter:

$field - string The field you want to group-by.
$cluster - string (optional) A Cluster-Label you want the $field-values to be clustered into.
This cluster must exist and be configured in the Hublify-Clusters.

{% set myData = hublify.dataset('person')
    .measure('persid', 'COUNT', 'myCnt')
    .groupBy('age')
    .all()
%}

Of course you can combine segments with other filters!

orderBy(...)

Adds an ordering to your retrieved data.
This can be called multiple times. Given order-by-statements are then cascaded in the same order.

{# Enabling a ordering by field "name" in ascending order #}
{% set myData = hublify.dataset('product').fields(['pcode','name']).orderBy('name', 'ASC').all %}

Parameter: First parameter is the field name, the second the order-direction ("ASC|DESC").

limit(...)

To limit the number of returned dataset-records, use this function. It makes only really sense to use it when executing the dataset-query with all(...).

{# Limit the number to the first 5 products #}
{% set myData = hublify.dataset('product')
    .fields(['pcode','name'])
    .limit(5)
    .all
%}

offset(...)

To set a number of records to skip on when retrieving the record-list. Use this e.g. for pagination. It makes only really sense to use it when executing the dataset-query with all(...).

{# Get the next 5 records, starting a from position 10 #}
{% set myData = hublify.dataset('product') 
    .fields(['pcode','name'])
    .offset(10)
    .limit(5)
    .all
%}

resultIndex(...)

Configures a field which values should be used for a data-list's result index, instead of the typical numerical range 0..n.

{# Get result of products as object/assoc array (key = 'pcode' #}
{% set myData = hublify.dataset('product').fields(['name']).resultIndex('pcode').all() %}

param(...)

Add an own parameter to the query. This function can be called multiple times with same or different key/values.

Example

{# Add a parameter "viewingUser" = "kevin" to actual dataset-query. #}
{% set myData = hublify.dataset('product').param('viewingUser', 'kevin').fields(['pcode','name']).all %}

params(...)

Add any own multiple parameters to the query.

Example

{# Add a parameter "viewingUser" = "kevin" to actual dataset-query. #}
{% set myData = hublify.dataset('product')
    .params({
        "myParam1": 123,
        "anotherParam": "aloha!"
    })
    .fields(['pcode','name'])
    .all %}

one(...)

EXECUTEs the data-query and returns only the first object.
(Like a: limit 1).

Put this always to the end of your twig-expression!

all(...)

EXECUTEs the data-query and returns an array of records.
Filters and sorting and limits are regarded.

Put this always to the end of your twig-expression!

Examples

Simple "count rows"

Example

{% set myData = hublify.dataset('person')
    .measure('persid','COUNT','person_count')
    .one()
%}

{# ---- RESULT ---- #}
{
    "person_count": "1667",
    "persid": "1194"
}

Simple "count & groupBy"

Example

Bonus: Use resultIndex(...) to get counted values as an easy accessible object / associative array back!

{% set myData = hublify.dataset('person')
    .resultIndex('grp_name')
    .fields([
        'grp_name',
    ])
    .measure('persid','COUNT','person_count')
    .groupBy('grp_name')
    .filter('tdyninc_container_label', containers, 'in')
    .all()
%}

{# --- RESULT (Example values) --- #}
{
    "A-Kunde": {
        "grp_name": "A-Kunde",
        "person_count": "1200",
        "persid": "25"
    },
    "ADMIN": { 
        "grp_name": "ADMIN",
        "person_count": "1",
        "persid": "1917"
    },
    "B2B Company": {
        "grp_name": "B2B Company",
        "person_count": "2",
        "persid": "1512"
    },
    ...
}