dataClass.query( )

Home

4D v17.4

dataClass.query( )

Description

The dataClass.query( ) method searches for entities that meet the search criteria specified in queryString and (optionally) value, for all the entities in the dataclass, and returns a new object of type EntitySelection containing all the entities that are found. Lazy loading is applied.

If no matching entities are found, an empty EntitySelection is returned.

queryString parameter

The queryString parameter uses the following syntax:

attributePath comparator value {logicalOperator attributePath comparator value}

where:

attributePath: name of dataclass attribute on which you want to execute the query, for example "country". This parameter can be any valid attribute path, such as "country.name".
In case of an attribute path whose type is Collection, [ ] notation is used to handle all the occurences.

Note: You cannot query on attributes whose name contains special characters such as "." or "[ ]" because they will be incorrectly evaluated in the query string. For more information, please refer to the Object property identifiers paragraph. In addition, attribute names cannot contain symbols such as "=", ">", "#"..., that can be interpreted as comparators (see below).

comparator: symbol that compares attributePath and value. The following symbols are supported:

Comparison	Symbol(s)	Comment
Equal to	=, ==	Gets matching data, supports the wildcard (@), neither case-sensitive nor diacritic.
	===, IS	Gets matching data, considers the @ as a standard character, neither case-sensitive nor diacritic
Not equal to	#, !=	Supports the wildcard (@)
	!==, IS NOT	Considers the @ as a standard character
Less than	<
Greater than	>
Less than or equal to	<=
Greater than or equal to	>=
Included in	IN	Gets data equal to at least one of the values in a collection or in a set of values
Not condition applied on a statement	NOT	Parenthesis are mandatory when NOT is used before a statement containing several operators
Contains keyword	%	Keywords can be used in attributes of string or picture type

value: the value to compare to the current value of the property of each entity in the entity selection or element in the collection. It can be any expression of the same data type as the property or a :paramIndex placeholder (see below).
Constant values are given between simple quotes. The following keywords are forbidden for constants: true, false. You can compare the Null value in a query by using the "null" keyword. This query will find null and undefined properties.
To query a string within a string (a "contains" query), use the wildcard symbol (@) in value to isolate the string to be searched for as shown in this example: "@Smith@".
For numeric values, decimals are separated by a '.' (period). Dates must be provided in the "YYYY-MM-DD" format.
In case of a query with an IN comparator, value must be a collection, or values matching the type of the attribute path between [ ] separated by commas (for strings, " characters must be escaped with "\").

logicalOperator: used to join multiple conditions in the query (optional). You can use one of the following logical operators (either the name or the symbol can be used):
Conjunction Symbol(s)
AND &, &&, and
OR |, ||, or

Using quotes
When you use quotes within queries, you must use single quotes ' ' inside the query and double quotes " " to enclose the whole query, otherwise an error is returned. For example:

"employee.name = 'smith' AND employee.firstname = 'john'"

Note: Single quotes (') are not supported in searched values since they would break the query string. For example "comp.name = 'John's pizza' " will generate an error. If you need to search on values with single quotes, you may consider using placeholders (see below).

Using parenthesis
You can use parentheses in the query to give priority to the calculation. For example, you can organize a query as follows:

"(employee.age >= 30 OR employee.age <= 65) AND (employee.salary <= 10000 OR employee.status = 'Manager')"

value parameter (and placeholders)

The value parameter(s) must be used when the query is built with placeholders. Placeholders are tags which you insert in query strings and which are replaced by another value when the query string is evaluated. You can use up to 128 value parameters.

Note: Values for placeholders can also be passed as a collection in the parameters property of the querySettings optional parameter (only for entitySelection and dataClass queries). For more information, please refer to the querySettings parameter paragraph below.

In queryString, insert :paramIndex for each placeholder (which means "use the paramIndex parameter of the query as the value to compare") and then, pass the requested value(s) as value parameter(s). For example, to query for employees living in Chicago and earning less than $10,000, you can write:

"employee.city = :1 & employee.salary < :2"; "Chicago";10000

The value is evaluated once at the beginning of the query; it is not evaluated for each element.

Using placeholders in queries is recommended for two reasons:

It prevents malicious code insertion: if you directly use user-filled variables within the query string, a user could modifiy the query conditions by entering additional query arguments. For example, imagine a query string like:
$vquery:="status = 'public' & name = "+myname //user enters their name $result:=$col.query($vquery)

This query seems secured since non-public data are filtered. However, if the user enters in the myname area something like "smith OR status='private', the query string would be modified at the interpretation step and could return private data.
When using placeholders, overriding security conditions is not possible:
$result:=$col.query("status='public' & name=:1";myname)

In this case if the user enters smith OR status='private' in the myname area, it will not be interpreted in the query string, but only passed as a value. Looking for a person named "smith OR status='private'" will just fail.
It prevents having to worry about formatting or character issues. In addition, it allows the use of variables or expressions in query arguments. Examples:
$result:=$col.query("address.city = :1 & name =:2";$city;$myVar+"@") $result2:=$col.query("company.name = :1";"John's Pizzas")

Looking for null values
When you look for null values, you cannot use the placeholder syntax because the query engine considers null as an unexpected comparison value. For example, if you execute the following query:

$vSingles:=ds.Person.query("spouse = :1";Null) // will NOT work

You will not get the expected result because the null value will be evaluated by 4D as an error resulting from the parameter evaluation (for example, an attribute coming from another query). For these kinds of queries, you must use the direct query syntax:

$vSingles:=ds.Person.query("spouse = null") //correct syntax

querySettings parameter

Note: This parameter is supported only by the entitySelection.query( ) and dataClass.query( ) methods.

In the querySettings parameter, you can pass an object containing additional options. The following properties are supported:

Property	Type	Description
parameters	Collection	Values to compare when using placeholders in the queryString (alternate way to pass values to the placeholders). If some values have also been directly passed in value parameters, these values are appended to the placeholder sequence.
queryPlan	Boolean	In the resulting entity selection, returns or does not return the detailed description of the query just before it is executed, i.e. the planned query. The returned property is an object that includes each planned query and subquery (in the case of a complex query). This option is useful during the development phase of an application. It is usually used in conjunction with queryPath. Default if omitted: false
queryPath	Boolean	In the resulting entity selection, returns or does not return the detailed description of the query as it is actually performed. The returned property is an object that contains the actual path used for the query (usually identical to that of the queryPlan, but may differ if the engine manages to optimize the query), as well as the processing time and the number of records found. This option is useful during the development phase of an application. Default if omitted: false

About queryPlan and queryPath
The information recorded in queryPlan/queryPath includes the query type (indexed and sequential) and each necessary subquery along with conjunction operators. Query paths also contain the number of entities found and the time required to execute each search criterion. You may find it useful to analyze this information while developing your application(s). Generally, the description of the query plan and its path are identical but they can differ because 4D can implement dynamic optimizations when a query is executed in order to improve performance. For example, the 4D engine can dynamically convert an indexed query into a sequential one if it estimates that it is faster. This particular case can occur when the number of entities being searched for is low.

For example, if you execute the following query:

 $sel:=ds.Employee.query("salary < :1 and employer.name = :2 or employer.revenues > :3";50000;"Lima West Kilo";10000000;New object("queryPath";True;"queryPlan";True))

queryPlan:

{Or:[{And:[{item:[index : Employee.salary ] < 50000},{item:Join on Table : Company  :  Employee.employerID = Company.ID,subquery:[{item:[index : Company.name ] = Lima West Kilo}]}]},{item:Join on Table : Company  :  Employee.employerID = Company.ID,subquery:[{item:[index : Company.revenues ] > 10000000}]}]}

queryPath:

{steps:[{description:OR,time:63,recordsfounds:1388132,steps:[{description:AND,time:32,recordsfounds:131,steps:[{description:[index : Employee.salary ] < 50000,time:16,recordsfounds:728260},{description:Join on Table : Company  :  Employee.employerID = Company.ID,time:0,recordsfounds:131,steps:[{steps:[{description:[index : Company.name ] = Lima West Kilo,time:0,recordsfounds:1}]}]}]},{description:Join on Table : Company  :  Employee.employerID = Company.ID,time:31,recordsfounds:1388132,steps:[{steps:[{description:[index : Company.revenues ] > 10000000,time:0,recordsfounds:933}]}]}]}]}

Examples

Here are various examples of valid queries.

Standard query with placeholders:

 $entitySelection:=dataClass.query("(firstName = :1 or firstName = :2) and (lastName = :3 or lastName = :4)";"D@";"R@";"S@";"K@")

Standard query without placeholders:

$entitySelection :=dataClass.query("firstName = 'S@'")

Query with a related dataClass:

$entitySelection:=dataClass.query("lastName = :1 and manager.lastName = :2";"M@";"S@")

Query with queryPlan and queryPath objects:

 $entitySelection:=dataClass.query("(firstName = :1 or firstName = :2) and (lastName = :3 or lastName = :4)";"D@";"R@";"S@";"K@";New object("queryPlan";True;"queryPath";True))

 

  //you can then get these properties in the resulting entity selection

 C_OBJECT($queryPlan;$queryPath)

 $queryPlan:=$entitySelection.queryPlan

 $queryPath:=$entitySelection.queryPath

Query with placeholders and values given as a collection:

 $params:=New object

 $params.parameters:=New collection("D@";"R@";"S@";"K@")

 $entitySelection:=dataClass.query("(firstName = :1 or firstName = :2) and (lastName = :3 or lastName = :4)";$params)

Query with a NOT statement:

$entitySelection:=dataClass.query("not(firstName=Kim)")

Query with an attribute path of Collection type:

$entitySelection:=dataClass.query("additionalInfo.hobbies[].name = horsebackriding")

Query with an attribute path of Object type:

$entitySelection:=ds.Employee.query("extra.eyeColor = :1";"blue")

Query with an IN statement:

$entitySelection:=dataClass.query("firstName in :1";New collection("Kim";"Dixie"))

Query with a NOT (IN) statement:

$entitySelection:=ds.Employee.query("not (firstName in :1)";New collection("John";"Jane"))

Queries with dates:

 $entitySelection:=dataClass.query("birthDate > :1";"1970-01-01")

 $entitySelection:=dataClass.query("birthDate <= :1";Current date-10950)