Package net.pricefx.formulaengine.scripting.queryapi

Interface QueryApi

public interface QueryApi

QueryAPI

Query API is a Groovy API available in all logics to query the data from Pricefx.

Its goal is to provide unified access to data in Pricefx and long term wise it should be the only API for querying data.

The main entrance point for use of QueryAPI is SandboxAPI.queryApi() that returns a QueryApi interface. This interface provides access to a set of methods which you will need for querying. Let’s look at this query which reads a single row with sku = “MB-0001” from the Product master table: 


 def qapi = api.queryApi()
 def p = qapi.tables().products()
 return qapi.source(p, p.sku().equal("MB-0001"))
     .stream { it.toList() }

Pipeline based data queries

Query api provides a pipeline based data query approach. Contrary to SQL which provides a declarative language that describe the data query in a single (but sometime complex) statement, pipeline based data queries gives the user the ability describe his query as a sequence of data transformation stages.

Stages can be seen as a data transformation node that takes a table as input and provides a table as output.

Like regular database tables, a table is a concrete or virtual (table view) data set structured by named columns and composed of data rows.

There exists two kind of stages

  • Source stages: which are the starting stage of every pipelined query, their source is a concrete database table. Their output is a subset of theirs rows and columns provided as a table view
  • Transformation stages: which are transforming the output of a previous stage, getting optionally a concrete databases table as additional input and providing a table view as output

Here is an example of representation of a pipelined query with data example: 

 [ SourceStage: table=Products, columns=[Product.sku, Product.label]] (output) ->
       ------------------
      | sku   | label  |
      ------------------
      | S1    | foo    |
   -> | S2    | bar    | ->
      | S3    | egg    |
      | S4    | spam   |
      ------------------
 -> (input) [ InnerJoinStage: table=Costs, columns=[Costs.sku], criteria=(Costs.sku = input.sku) ] (output) ->
      -------------------------
      | sku   | label  | Cost |
      -------------------------
      | S1    | foo    | 1O   |
   -> | S2    | bar    | 15   | ->
      | S3    | egg    | 5    |
      | S4    | spam   | 20   |
      -------------------------
 -> (input) [ FilterStage: filter=input.Cost > 10 ] (output) ->*
      -------------------------
      | sku   | label  | Cost |
      -------------------------
   -> | S2    | bar    | 15   |
      | S4    | spam   | 20   |
      -------------------------
 -> (input) [ SortByStage: order=descending(input.Cost) ] (output) ->
      -------------------------
      | sku   | label  | Cost |
      -------------------------
   -> | S4    | spam   | 20   |
      | S2    | bar    | 15   |
      -------------------------

As we can see each stage can only refer to its input columns (which is the previous stage output) and if it is available a concrete source table (used here in SourceStage and JoinStage).

The above example is implemented with QueryApi as follows: 


 def qapi = api.queryApi()
 def p = qapi.tables().products()
 def c = qapi.tables().productExtensions("Costs")
 def query = qapi.source(p, [p.sku(), p.label()])
     .innerJoin(c, { input -> [c.Cost] }, { input -> input.sku.equal(c.sku()) })
     .filter { input -> input.Cost.greaterThan(10) }
     .sortBy { input -> [qapi.orders().descendingNullsLast(input.Cost)] }
As shown in this code snippet, each stage can refer to columns
  • of a concrete table when it is belonging to its arguments. This is the case of
    • the source stage which uses p to refer to the concrete product table columns [...]source(p, [p.sku(), p.label()])
    • the join stage which uses c to refer to the concrete costs table columns [...]innerJoin(c, { input -> [c.Cost] }, { input -> input.sku.equal(c.sku()) })
  • of the table view it gets as input. To refer to these columns the client code should provide a function getting a Tables.Columns object which give access to this input columns. In the above example this is the case for the last three stages of the query pipeline.

Tables.Columns objects are immutable map associating columns names to their reference. As groovy supports the . operator to access the entry of a map, columns can be accessed directly with it, like {input -> input.MyColumnName}

By default, columns defined from a concrete source table have the same name as the accessor used to get it. For example, qapi.tables().products().sku() will lead to a column named "sku". This name can be anyway changed using Expression.as(String).

Executing the query and fetching the data

Once a data pipeline has been defined, the user can run the corresponding query and fetch its results using PipelineStage.stream(Function). This method gets a function as argument which will be in charge of consuming the result stream. This PipelineStage.ResultStream is an Iterable of PipelineStage.ResultStream.ResultRow which are giving access to the result rows data in the same way columns are provided when adding pipeline stages, i.e. using an immutable map view indexed by column names. 

 def qapi = api.queryApi()
 def p = qapi.tables().products()
 def allSkuSize = 0
 return qapi.source(p, [p.sku()])
         .stream { it.sum {
             row -> row.sku.size()
         }}*
Since:
14.0 - Caribou Lou

  • Method Details

    • source

      PipelineStage source(Tables.Table table, List<? extends Selectable> selectables, Expression filter)
      Main entry point to start writing a query.

      You must specify

      • a source Table
      • a list of columns or aliased expressions made up from the source Table
      • a filter expression that specify the row of the table to be fetched

      The following example fetches the sku and createDate columns of each row of the master data Product table having label containing "foo" 

      
 def qapi = api.queryApi()
 def p = qapi.tables().products()
 return qapi.source(p, [p.sku(), p.createDate()], p.label().like("%foo%"))
        .stream { it.collect { it } }

      Note that the result of this method is a PipelineStage which can be

      • either used as input for other data transformation like joins, filter, aggregation, etc. (see all the available methods in PipelineStage
        • -
      • or used to directly fetch the selected columns or expressions via PipelineStage.stream(Function).
      Parameters:
      table - the table to get the data from
      selectables - an Iterable object containing the columns/expressions to fetch. It must only contain reference to columns coming from the given table argument.
      filter - a boolean expression that specify the rows to be fetched. It must only contain reference to columns coming from the given table argument.
      Returns:
      the corresponding pipeline source
      Since:
      14.0 - Caribou Lou
      See Also:

    • source

      PipelineStage source(Tables.Table table, List<? extends Selectable> selectables)
      Main entry point to start writing a query.

      Same as source(Tables.Table, List, Expression) but without the filter expression. As a result the entire table will be fetched if no subsequent PipelineStage.filter(Function) stage is applied before the final PipelineStage.stream(Function) operation.

      Since:
      14.0 - Caribou Lou
      See Also:

    • source

      PipelineStage source(Tables.Table table)
      Main entry point to start writing a query.

      Same as source(Tables.Table, List) but without the specification of the selectable expression. As a result all the available columns of the table will be fetched if no subsequent PipelineStage.retainColumns(Function) or PipelineStage.removeColumns(Function) stage is applied before the final PipelineStage.stream(Function) operation.

      Since:
      14.0 - Caribou Lou
      See Also:

    • tables

      Tables tables()
      Provides access to reference of all available Tables.Table.

      These references are used to query the desired table.

      For example: 

      
 def tables = api.queryApi().tables()
 def tProduct = tables.products()
 def tCosts = tables.productExtension("Costs")
 def tCountries = tables.companyParameterTable("Countries")
      Returns:
      an object giving access to all the available tables.
      Since:
      14.0 - Caribou Lou

    • exprs

      Exprs exprs()
      Provides expression building methods

      For example a logic conjuction can be built as follows: 

      
 def qapi = api.queryApi()
 def p = qapi.tables().products()
 def labelEndsWithFooAndSkuStartsWithBar = qapi.exprs().and(p.label().like("%foo"), p.sku().like("bar%"))
      Returns:
      an object giving access to expression building methods
      Since:
      14.0 - Caribou Lou

    • orders

      Orders orders()
      Provides order statement building methods

      For example an ascending alphabetical ordering by sku can be built with: 

      
 def qapi = api.queryApi()
 def skuAscendingOrder = api.orders().ascNullsLast(qapi.tables().products().sku())
      Returns:
      an object giving access to order statement building methods
      Since:
      14.0 - Caribou Lou