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
Basic usage
Creation
Execution
Shortcuts
Grouped queries
Multi-content queries
Specialized filters
JavaScript API
See Also

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:

Copy

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.

Copy

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.

Copy

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.

Copy

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:

Copy

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.

Copy

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.

Copy

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.

Copy

query.UseBulkMode = true;

Serialization

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

Copy

//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:

Copy

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

But it applies to all persisted objects of Packflow:

Copy

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:

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:

Copy

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:

Copy

//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:

Copy

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:

Copy

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.

Copy

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:

Copy

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:

Copy

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:

Copy

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:

Copy

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.

Copy

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.

Copy

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.

Copy

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.

Copy

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.

Copy

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.

Copy

$(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.

Reference