QUERY BY ATTRIBUTE

QUERY BY ATTRIBUTE looks for records matching the query string defined using the objectField, attributePath, queryOp and value parameters, and returns a selection of records for aTable.

Note: For more information on Object fields (new in 4D v15), please refer to the Objet section of the Design Reference manual.

QUERY BY ATTRIBUTE changes the current selection of aTable for the current process and makes the first record of the new selection the current record. If the aTable parameter is omitted, the command applies to the default table. If no default table has been set, an error occurs.

The optional conjOp parameter is used to join QUERY BY ATTRIBUTE calls when defining multiple queries. The conjunction operators available are the same as the ones for the QUERY command:

The conjOp parameter is not used for the first QUERY BY ATTRIBUTE call of a multiple query, or if the query is a simple query. If you omit it within a multiple query, the AND (&) operator is used by default.

In objectField, pass the Object field whose attribute(s) you want to query. If it belongs to a One table related to aTable with an automatic or manual relation, the objectField may belong to another table.

In attributePath, pass the name or path of the attribute whose values you want to compare. You can pass a single attribute, for example "age": in this case all attributes with this name will be compared in the record. You can also pass a path, for example "children.girls.age", in which case only matching attributes will be compared in the record.
If an attribute "x" is an array, QUERY BY ATTRIBUTE will search records which contain an attribute "x" in which at least one element matches the criteria. To search in array attributes, it is necessary to indicate to the QUERY BY ATTRIBUTE command that attribute "x" is an array by appending "[]" to its name in attributePath (see example 3).

Notes:

• Keep in mind that attribute names are case-sensitive: you can have different "MyAtt" and "myAtt" attribute names in the same record.
• Attribute names are trimmed to eliminate extra spaces. For example, " my first attribute .my second attribute " is interpreted as "my first attribute.my second attribute". 

The queryOp parameter is the comparison operator that is applied between objectField and value. You can pass one of the symbols shown here:

Note: It is also possible to specify the comparison operator as an text expression instead of a symbol. See the QUERY command description for more information.

value is the data against which attributePath will be compared. The value can be any expression that evaluates to the same data type as attributePath. The value is evaluated once, at the beginning of the query. The value is not evaluated for each record. To query for a string contained 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@". Note that in this case, the search only partially benefits from the index (compactness of data storage).

Here is the structure of a query by attribute:

Note: An implicit criteria for all operators (except #) is that the Object field contains an attribute. However, for the # operator, it can be undefined (see below).

With object fields, the "#" operator should be seen as selecting records where "no attribute" of the field contains the value searched for. In this context, 4D considers in a similar manner:

• fields where the value of the attribute is different from the value searched for,
• fields where the attribute is not present (or contains a Null value).

For example, the following query returns records for people who have a dog whose name is not Rex, as well as records for people who do not have a dog, or who have a dog with no name:

Another example: this query will return all records for which [Table]ObjectField contains an object which contains an attribute1 attribute which is itself an object containing an attribute2 attribute whose value is not value, as well as records where the object field does not contain attribute1 or attribute2):

This principle also applies to array attributes. For example:

This query will return records for people who do not have an address in Paris.

To specifically obtain records where the attribute is undefined, you can use an empty object (see example 2).

Here are the rules for building multiple queries by attribute:

• The first query argument must not contain a conjunction.
• Each successive query argument can begin with a conjunction. If you omit it, the AND (&) operator is used by default.
• The first query and every other query, except the last, must use the * parameter.
• QUERY BY ATTRIBUTE can be mixed with QUERY commands (see example).
• To perform the query, do not specify the * parameter in the last QUERY BY ATTRIBUTE command. Alternatively, you can execute the QUERY command without any parameters other than the table.

Note: Each table maintains its own currently-built query. This means that you can create multiple queries simultaneously, one for each table.

No matter which way a query has been defined:

• If the actual query operation is going to take some time to be performed, 4D automatically displays a message containing a progress thermometer. These messages can be turned on and off by using the MESSAGES ON and MESSAGES OFF commands. If a progress thermometer is displayed, the user can click on the Stop button to interrupt the query. If the query is completed, OK is set to 1. Otherwise, if the query is interrupted, OK is set to 0 (zero).
• If any indexed object fields are specified, the query is optimized every time that it is possible (indexed fields are searched first) resulting in a query that takes the least amount of time possible.

Dates are stored in objects according to database settings; by default, the time zone is taken into account (see the JSON use local time selector in the SET DATABASE PARAMETER command).

!1973-05-22! -> "1973-05-21T23:00:00.000Z"

This setting is also taken into account during queries, so you do not have to worry about it if you always use your database at the same place and if settings are the same on all machines that access the data. In this case, the following query will correctly return records whose Birthday attribute equals !1973-05-22! (saved as "1973-05-21T23:00:00.00Z"):

If you do not want to use the GMT settings, you can modify these settings using the following instruction:

Keep in mind that the scope of this setting is the process only. If you execute this instruction, then October 1st, 1965 will be stored "1965-10-01T00:00:00.000Z" but you will need to set the same parameter before launching your queries:

In this example, the "age" attribute is either a string or an integer and we want to find people whose age is between 20 and 29. The first two lines query the attribute as an integer (>=20 and < 30) and the last ones query the field as a string (starts with "2" but is different from "2".)

The QUERY BY ATTRIBUTE command can be used to find records where certain attributes are defined (or are not defined). To do this, you have to use an empty object.

Note: This specific syntax is not supported with array type attributes. Searching for NULL values in array elements will give invalid results.

You want to search a field containing array attributes. With the following two records:

Record1:
[People]name: "martin"
[People]OB_Field:
    "locations" : [ {
                "kind":"office",
                "city":"paris"
            } ]

Record2:
[People]name: "smith"
[People]OB_Field:
    "locations" : [ {
                "kind":"home",
                "city":"lyon"
            } , {
                "kind":"office",
                "city":"paris"
            } ]

... QUERY BY ATTRIBUTE will find people with a location in "paris" using this statement:

Note: If you defined several criteria on the same array attribute, the matched criteria will not necessarily apply to the same array element. In the following example, the query returns "smith" because it has a "locations" element whose "kind" is "home" and a "locations" element whose "city" is "paris", even if it's not the same element: