Click or drag to resize
Query API

To retrieve data from its database, PACKflow uses a dedicated query system and API.

The PFQuery determines what is requested from a target content, which can be any kind of data from user-data to structural data.

This article details the capabilities of that system and explains how queries are created and executed.

This topic contains the following sections:

Data example

The following data will be used in our examples, consider each row as a PFItem of a PFContentType named Planet:

Type

Name

Diameter

Mass (compared to earth)

Has rings

Terrestrial

Mercury

4.878 km

0,055

No

Terrestrial

Venus

12.104 km

0,949

No

Terrestrial

Earth

12.756 km

1,000

No

Terrestrial

Mars

6.787 km

0,107

No

Jovian

Jupiter

142.800 km

318,000

Yes

Jovian

Saturn

120.000 km

95,000

Yes

Jovian

Uranus

51.118 km

15,000

Yes

Jovian

Neptune

49.528 km

17,000

Yes

Basic usage

Here is a classic example of use of a PACKflow query:

C#
protected String Demo()
{                                
    PFQuery query = new PFQuery();

    //Add filters (this method adds filters at the root of the filters tree, and use "AND" by default)
    query.AddFilter(Planet.FieldName_Name, PFQueryFilterOperator.EndsWith, "s");
    query.AddFilter(Planet.FieldName_Diameter, PFQueryFilterOperator.GreaterOrEqual, 10000);

    //Sort results
    query.AddOrder(Planet.FieldName_Name, PFQueryOrderType.Ascending);

    //Execute the query
    List<Planet> planets = Application.ContentType_Planet.Items.Get(query);

    //Return results
    return planets.GetString(", ", c => c.__Name);
}

This code returns "Uranus, Venus".

Creation

The PFQuery object can be created from scratch with a default constructor or using one of the many overloads of the static method PFQuery.CreateQuery. Then many options are available.

Field names

We will often refer to field names in the following sections. There are two kinds of fields:

  • System: these are provided by PACKflow, you can find their names in constants of their parents, like PFItem.FieldName_CreatedAt.

  • Modeling: these are modeled with PACKflow Designer. When the project has been generated, each object's class will contain constants, like Country.FieldName_Continent.

Selection

You can choose which values are returned with two properties: FieldsMode and CustomFields.

The latter is a list of field names and will influence the selection, depending on the chosen PFQueryFieldsMode.

C#
PFQuery query = new PFQuery();
query.FieldsMode = PFQueryFieldsMode.CustomFields;
query.CustomFields = new List<string> { Planet.FieldName_Type, Planet.FieldName_Name };
//Or
query.ChooseFields(PFQueryFieldsMode.CustomFields, new List<string> { Planet.FieldName_Type, Planet.FieldName_Name });

This query will return countries with the values Type and Name.

Note: the default mode is AllFields.

Note: Id and Guid are returned even if not selected.

Sorting

Sorting results is easy, just add a PFQueryOrder to the query.

C#
PFQuery query = new PFQuery();

//Sort results
query.AddOrder(Planet.FieldName_Diameter, PFQueryOrderType.Ascending); //adds a new order
//OR
query.ChangeOrder(Planet.FieldName_Diameter, PFQueryOrderType.Ascending); //clears previous orders and adds a new one
//OR you can also manage orders using the corresponding list:
query.Orders.Add(new PFQueryOrder { FieldName = Planet.FieldName_Diameter, Type = PFQueryOrderType.Ascending });

//Execute the query
List<Planet> planets = Application.ContentType_Planet.Items.Get(query);

//Return results
return planets.GetString(", ", c => c.__Name);

Output: "Mercury, Mars, Venus, Earth, Neptune, Uranus, Saturn, Jupiter".

Filters

Filters are mainly based on three settings: field name, operator and value.

C#
PFQuery query = new PFQuery();

//Add filters
query.AddFilter(Planet.FieldName_HasRings, PFQueryFilterOperator.Equal, true);

//Execute the query
List<Planet> planets = Application.ContentType_Planet.Items.Get(query);

//Return results
return planets.GetString(", ", c => c.__Name);

Output: "Jupiter, Saturn, Uranus, Neptune".

Many PFQueryFilterOperator values are available.

To respect the capabilities of an SQL query, a tree of filters can also be defined:

C#
PFQuery query = new PFQuery();

//Add filters
query.RootNode.Type = PFQueryNodeType.Or;
PFQueryNode terNode = query.RootNode.AddNode(PFQueryNodeType.And);
terNode.AddFilter(Planet.FieldName_Type, PFQueryFilterOperator.Equal, "Terrestrial");
terNode.AddFilter(Planet.FieldName_Mass, PFQueryFilterOperator.GreaterOrEqual, 0.5);
PFQueryNode jovNode = query.RootNode.AddNode(PFQueryNodeType.And);
jovNode.AddFilter(Planet.FieldName_Type, PFQueryFilterOperator.Equal, "Jovian");
jovNode.AddFilter(Planet.FieldName_Mass, PFQueryFilterOperator.GreaterOrEqual, 50);
//Equivalent to the SQL: (Type='Terrestrial' AND Mass>0.5) OR (Type='Jovian' AND Mass>50)

//Execute the query
List<Planet> planets = Application.ContentType_Planet.Items.Get(query);

//Return results
return planets.GetString(", ", c => c.__Name);

Output: "Venus, Earth, Jupiter, Saturn".

Note that the AddFilter method returns a PFQueryFilter that can be customized with more options.

Some types of fields have more specific options, see below.

Paging

Heavily used by our gridviews, pagination allows to load objects by batches.

C#
PFQuery query = new PFQuery();

//Prepare query
query.AddOrder(Planet.FieldName_Name);
query.EnablePaging = true;        
query.PageSize = 4;

//Page 1
query.PageNo = 1;
List<Planet> planets = Application.ContentType_Planet.Items.Get(query);
String pageOne = planets.GetString(", ", c => c.__Name);

//Page 2
query.PageNo = 2;
planets = Application.ContentType_Planet.Items.Get(query);
String pageTwo = planets.GetString(", ", c => c.__Name);

//Return results
return String.Format("{0} / {1}", pageOne, pageTwo);

Output: "Earth, Jupiter, Mars, Mercury / Neptune, Saturn, Uranus, Venus".

Scope

By default when you delete an object in PACKflow, it is not removed from database.

The content is flagged as deleted until the garbage cleaner job deletes it eventually.

The PFQuery allows you to get those flagged content.

C#
query.ResultsScope = PFQueryResultsScope.NotDeleted; //Default value
query.ResultsScope = PFQueryResultsScope.Deleted; //Only the deleted content
query.ResultsScope = PFQueryResultsScope.Both; //All content, flagged deleted or not

Bulk mode

The bulk mode can be set to optimize the query when RLS (read permissions) is more restrictive than the filters.

If enabled, this mode will filter results using the query filters first, and RLS only after.

C#
query.UseBulkMode = true;

Serialization

The query can be (de)serialized in both XML and JSON.

C#
//XML - Serialize query
String xml = query.Serialize();

//XML - Deserialize query (two ways)
query = xml.Deserialize<PFQuery>();
query = PFQuery.CreateQuery(xml);

//JSON - Serialize query
String xml = query.SerializeJson();

//JSON - Deserialize query
query = xml.DeserializeJson<PFQuery>();
Execution

A PFQuery is always executed from the relation representing the children objects of a parent object.

Like in the previous examples, it applies on items, queried from their parent content-type:

C#
PFQuery query;
PFContentType planetContentType;
List<PFItem> planets = planetContentType.Items.Get(query);

But it applies to all persisted objects of PACKflow:

C#
PFSite site;
PFQuery applicationQuery;
PFQuery groupQuery;

//Get an application
PFApplication myApplication = site.Applications.GetOne(applicationQuery);

//Get groups of that application
List<PFGroup> groups = myApplication.Groups.Get(groupQuery);

In the previous code, you can notice an execution returning an unique result. Internally, the results limit of the query is set to 1.

Here is an non-exhaustive schema of the objects persisted in PACKflow:

PFObjects

The PFStructureObject implies strong relations, generating tables for children objects. PFRelationalObject implies the use of existing tables, created from structure objects.

Shortcuts

The creation of a PFQuery is not always necessary as PACKflow provides methods for recurring uses.

Here are some examples with generic objects:

C#
PFSite site;

//Filter users on a given text
List<PFUser> users = site.Users.GetByText("John");

//Get an application by its name.
PFApplication app = site.Applications.GetByName("MyApplication");

//Get a content-type by its id
PFContentType ct = app.ContentTypes.GetById(42);

//Get terrestrial planets
List<PFItem> planets1 = ct.Items.Get(Planet.FieldName_Type, "Terrestrial");

//Get terrestrial planets sorted by name
List<PFItem> planets2 = ct.Items.Get(Planet.FieldName_Type, "Terrestrial", Planet.FieldName_Name);

"planets1" contains "Mercury, Venus, Earth, Mars" while planets2 contains "Earth, Mars, Mercury, Venus".

The previous code uses generic objects. Each application is generated with specific objects, based on the model, inheriting from generic objects. Many helpers are provided, allowing this:

C#
//Application, generally available in pages, context, or even items via ParentApplication.
Application_MyApplication app; 

//Get planets (strongly typed) via the corresponding content-type
List<Planet> planets = app.ContentType_Planet.Items.Get(Planet.FieldName_Type, "Terrestrial");
Grouped queries

All the previous queries are returning objects like items, users, etc. This section details another type of query, returning aggregated data.

Groups

Groups are the equivalent of "GROUP BY" clauses in SQL. They can be managed with the "Groups" collection but you can also add those with the AddGroup method:

C#
query.AddGroup(Planet.FieldName_Type, PFQueryOrderType.Ascending);

Aggregations

Each aggregation adds a new information to the query output. They can be managed with the "Aggregations" collection but you can also add those with the AddAggregation method:

C#
query.AddAggregation("MassAverage", Planet.FieldName_Mass, PFQueryAggregationType.Average);

The first parameter is the name of the aggregation, that name is used later to obtain the corresponding value in the results' data collection.

The second parameter is the aggregated field's name while the latter specifies which kind of aggregation is wanted among the following: Average, Count, Maximum, Minimum, Standard deviation, Sum and Variance.

Selection

As for SQL, selected columns in an aggregated query must be part of a group or aggregation.

For that reason, we recommend the use of the field mode "CustomFields" as you will see in the examples below.

Execution

When a query contains a group or an aggregation, the method GetByGroup MUST be used instead of the Get method.

C#
List<PFGroupedObjects> results = Application.ContentType_Planet.Items.GetByGroup(query);

Please note: if a query is executed with an aggregation and no group, only one result is returned.

Example

Here is a simple example of a grouped query:

C#
PFQuery query = new PFQuery();

//Prepare the query
query.AddGroup(Planet.FieldName_Type, PFQueryOrderType.Ascending);
query.ChooseFields(PFQueryFieldsMode.CustomFields, Planet.FieldName_Type);

//Execute the query 
List<PFGroupedObjects> results = Application.ContentType_Planet.Items.GetByGroup(query);

//Return results
return results.GetString(", ", r => r.Data.GetValueText(Planet.FieldName_Type));

Output: "Jovian, Terrestrial".

Example with aggregation

Now, an example with a global aggregation:

C#
PFQuery query = new PFQuery();

//Prepare the query
query.AddAggregation("TotalMass", Planet.FieldName_Mass, PFQueryAggregationType.Sum);
query.ChooseFields(PFQueryFieldsMode.CustomFields, Planet.FieldName_Mass);

//Execute the query 
PFGroupedObjects result = Application.ContentType_Planet.Items.GetByGroup(query).First();

//Return results
return result.Data.GetValueText("TotalMass");

Output: "447.111".

Group and aggregation

Finally this code returns the average planet diameter by type:

C#
PFQuery query = new PFQuery();

//Prepare the query
query.AddGroup(Planet.FieldName_Type, PFQueryOrderType.Ascending);
query.AddAggregation("AverageDiameter", Planet.FieldName_Diameter, PFQueryAggregationType.Average);
query.ChooseFields(PFQueryFieldsMode.CustomFields, Planet.FieldName_Type, Planet.FieldName_Diameter);

//Execute the query 
List<PFGroupedObjects> results = Application.ContentType_Planet.Items.GetByGroup(query);

//Return results
return results.GetString(", ", r => String.Format("{0}={1}",
    r.Data.GetValueText(Planet.FieldName_Type), r.Data.GetValueText("AverageDiameter")));

Output: "Jovian=90861.5, Terrestrial=9131.25".

Multi-content queries

This section explains how queries can be executed on multiple parent objects.

In addition to planets, we specify a new Moon content with the following data:

Planet (Choice field)

Name

Diameter

Earth

Moon

3.476 km

Mars

Deimos

8 km

Mars

Phobos

28 km

Jupiter

Callisto

4.800 km

Jupiter

Europa

3.126 km

Jupiter

Ganymede

5.276 km

Jupiter

Io

3.629 km

Saturn

Enceladus

498 km

Saturn

Iapetus

1.436 km

Uranus

Oberon

1.526 km

Uranus

Titania

1.578 km

Neptune

Triton

2.705 km

Content-Types

To execute such a query, we need a List of PFContentType. In our example, we select the content-types Planet and Moon:

C#
List<PFContentType> cts = Application.ContentTypes.GetByNames("Planet", "Moon");
//OR
List<PFContentType> cts = new List<PFContentType> { Application.ContentType_Planet, Application.ContentType_Moon };

Selection

Using the default selection mode (AllFields) is not recommended as it is unstable in some situations.

Also, the selection of columns sharing the name but not the type will not work.

On the other hand, selecting columns that are not present in all content-types is authorized.

C#
PFQuery query = new PFQuery();
List<PFContentType> cts = new List<PFContentType> { Application.ContentType_Planet, Application.ContentType_Moon };                                

//Prepare the query
query.ChooseFields(PFQueryFieldsMode.CustomFields, "Name", "Planet");
//OR
query.ChooseFields(PFQueryFieldsMode.CommonFieldsPlusCustomFields, "Planet");

//Execute the query 
List<PFItem> results = cts.GetItems(query);

//Return results
return results.GetString(", ", c =>
    String.Format("{0} ({1})", c.Data.GetValueText("Name"),
    c is Moon ? c.Data.GetValueChoice("Planet").GetDisplayValue(PFFieldChoiceValue_DisplaySeparatorType.Comma) : "-"));

Output: "Mercury (-), Venus (-), Earth (-), Mars (-), Jupiter (-), Saturn (-), Uranus (-), Neptune (-), Moon (Earth), Deimos (Mars), Phobos (Mars), Callisto (Jupiter), Europa (Jupiter), Ganymede (Jupiter), Io (Jupiter), Enceladus (Saturn), Iapetus (Saturn), Oberon (Uranus), Titania (Uranus), Triton (Neptune)".

Filters

Filters can be set globally or by content-type.

C#
PFQuery query = new PFQuery();
List<PFContentType> cts = new List<PFContentType> { Application.ContentType_Planet, Application.ContentType_Moon };
query.ChooseFields(PFQueryFieldsMode.CustomFields, "Name", "Planet");

//Prepare the query
query.AddFilter("Name", PFQueryFilterOperator.Contains, "a");

PFQueryFilter planetFilter = query.AddFilter("Mass", PFQueryFilterOperator.Greater, 1);
planetFilter.RestrictedToParents.Add(Application.ContentType_Planet.Guid);

PFQueryFilter moonFilter = query.AddFilter("Diameter", PFQueryFilterOperator.Greater, 1500);
moonFilter.RestrictedToParents.Add(Application.ContentType_Moon.Guid);

//Execute the query 
List<PFItem> results = cts.GetItems(query);

//Return results
return results.GetString(", ", c => c.Data.GetValueText("Name"));

Output: "Saturn, Uranus, Callisto, Europa, Ganymede, Titania".

Other settings

Orders, groups and aggregations are also supported by Multi-Content queries. But the concerned fields must be common to all content and must share the same type.

C#
PFQuery query = new PFQuery();
List<PFContentType> cts = new List<PFContentType> { Application.ContentType_Planet, Application.ContentType_Moon };

//Prepare the query
query.AddAggregation("MaxDiameter", "Diameter", PFQueryAggregationType.Maximum);
query.AddAggregation("MinDiameter", "Diameter", PFQueryAggregationType.Minimum);

//Execute the query 
PFGroupedObjects result = cts.GetItemsByGroup(query).First(); //ByGroup because of the aggregation. No group=>one result.

//Return results
return String.Format("Min:{0}km - Max:{1}km", result.Data.GetValueText("MinDiameter"), result.Data.GetValueText("MaxDiameter"));

Output: "Min:8km - Max:142800km ".

Specialized filters

The generic PFQueryFilter handles all types of fields. However, some of them have specialized settings.

Date

The class PFQueryFilter_DateTime contains an option to generate a value on execution, based on a specified offset (TimeSpan).

The following code will filter the results created in the last hour, depending on the time of execution.

C#
query.RootNode.Filters.Add(new PFQueryFilter_DateTime
{
    FieldName = PFItem.FieldName_CreatedAt,
    Operator = PFQueryFilterOperator.Greater,
    UseNow = true,
    DateTime_Offset = new TimeSpan(1, 0, 0)
});

Note: the DateTime_Offset can be used separately. If UseNow is set to false, the offset is applied on the FieldValue.

User

The class PFQueryFilter_User provides the possibility to filter an User field on the current user.

The following code will filter the results having the user executing the query as creator.

C#
query.RootNode.Filters.Add(new PFQueryFilter_User
{
    FieldName = PFItem.FieldName_CreatedBy,
    Operator = PFQueryFilterOperator.Equal,
    UseCurrentUser = true
});
JavaScript API

Our query API is also available in JavaScript! The JavaScript PFQuery is compatible with our SignalR and REST services available from client side.

C#
$(document).ready(function () {
    var query = new Packflow.PFQuery();
    query.AddFilter2("Diameter", Packflow.PFQueryFilterOperator.Greater, 5000);
    query.AddFilter2(MyApplicationFields.Planet.Diameter, Packflow.PFQueryFilterOperator.Less, 15000);
    query.AddOrder("Name");
    var appHub = new MyApplication.ApplicationHub();
    appHub.Planet_GetByQuery(query, function (results) {
        for (var i = 0; i < results.length; i++) {
            console.log(results[i].Name);
        }
    });
});

Output: Earth Mars Venus

All applications are provided with intellisense.

Javascript Intellisense 1
Javascript Intellisense 2
Javascript Intellisense 3
See Also