4APQL - 4ALLPORTAL Query Language

4APQL is a 4ALLPORTAL specific query language similar to SQL and uses most of its syntax. 4APQL is reduced to 4ALLPORTAL relevant queries, tough, and offers some additional options, e.g. square brackets [ ] for dimensions.

4APQL can be used in the 4ALLPORTAL GUI instead of SQL conditions, e.g., to limit role rights conditions based, to define dashboard widget contents, or to specify webhook triggers. 4APQL is also used in backend processes, e.g., to perform searches.

Supported Operators and Commands

To use 4APQL in the GUI, you can use the following operators (listed in order of their precedence):

Name	Description
( [	for grouping for dimensions
< > >= <= = != IN NOT IN LIKE NOT LIKE IS IS NOT MATCH	less than greater than greater than or equal less than or equal equal not equal in not in like not like is is not for full text searches
AND	and
OR	or

Like in SQL, you have further options to built your query:

• Grouping: you can group parameters using ( )
• Wildcard: you can use LIKE with % to represent any number of previous/following characters, or LIKE with _ to represent one single character

Precedence

The precedence is the order in which the supported operators are evaluated. The list above shows the precedence of 4APQL operators. Operators listed first in the list have a higher precedence. Operators listed together have the same precedence. Operators of the same precedence are processed from left to right.

Internal Functions

You can use internal functions to retrieve data on the current user or role, or on an object from a specified module.

currentUser(<fieldname>)

The function currentUser(<fieldname>) returns the value of the specified field of the current user. This field can be any field of the user object, e.g., firstname, lastname, email, or a related field, e.g. file_created_by.

If called without a parameter, the user id is responded, e.g. created_by = currentUser().

currentRole(<fieldname>)

The function currentRole(<fieldname>) returns the value of the specified field of the current role.

If called without a parameter, the role id is responded, e.g. owner_role = currentRole().

getObject(<modulename>, <objectid>, <fieldname>)

The function getObject(<modulename>, <objectid>, <fieldname>) returns the value of a given field in the specified module for the object id, e.g. getObject('file', '<uuid>', 'name') = 'test.jpg'.

getRank(<field>, ..., <searchExpression>)

The function getRank(<field>, ..., <searchExpression>) calculates a relevance score for one or more fields against a search expression. Higher scores indicate a closer match.

The last argument must be a string literal (the search expression). All preceding arguments are field references to score against. At least one field and the search expression are required.

Only string fields are supported as arguments — numeric, date, or other typed fields are not valid.

Scoring weights per field:

Match type	Weight
Exact match	8
Starts with expression followed by a space	4
Starts with expression	2
Contains expression	1

Scores across all specified fields are summed. The result is a numeric value suitable for sorting by relevance, e.g. sort=-getRank(name; 'sample').

Example — return relevance score alongside name, sorted by relevance:

fields=getRank(name, 'sample')&fields=name&sort=-getRank(name; 'sample')

Example — score across multiple fields:

getRank(name, description, 'sample')

Please note

• The functions can be used either in the select or the where clause of a query.

• List fields or relations that result in lists are currently not supported.

• URL query parameter encoding

  When a function is used as a fields query parameter, use semicolons as argument separators. HTTP clients and Spring's parameter binding split fields values on commas, which breaks comma-separated function arguments.

  If only one fields expression is needed:

  ?fields=getRank(name; 'sample')

  If multiple fields expressions are needed, use separate &fields= parameters:

  ?fields=getRank(name, 'sample')&fields=name

Example Statements

• Limit a webhook trigger

  • mimetype = 'image/jpeg' limits to only jpeg images
  • mimetype LIKE 'image/%' limits to all image types
  • mount = 'data' AND parent_path = 'flyer_24' limits to the specified folder
  • mount = 'data' AND (parent_path = 'flyer_24' OR parent_path LIKE 'flyer_/%') limits to the specified folders

• Define a module's dashboard widget

  • created_by = currentUser() returns all files that the current user has created
  • created_by = '70394110-d808-4697-bb57-14a5cf36f0ge' returns all files that the specified user has created
  • file.id = currentUser('file_mod_by') returns all files that the current user has modified
  • created_time > '2023-05-01T10:10:01.803Z' returns all files that were created after the specified date