Select Queries

Advanced select data queries for two-dimensional data are created by chaining methods together from the root of wire.data.select. These methods populate the properties of a wire.data.TableQuery object. This object is then passed through the data source to the service provider for processing. The service provider may use the data on this object to create a native query for a data service or it may send the object to the service as-is .

To check for service provider support for table queries, use the allow.tableQuery attribute on the DataSource object servicing the query request. Visit the data source service provider documentation for details on support for table queries.

Below is a list of some of the more popular methods. For details including the full list of methods available for building table queries, visit the wire.data.TableQuery reference.

Basic Filters

The following query will return a list of active users from a data service. Note, in a data service, this could translate to a query on a Users table in a SQL database, a Users resource path with query options on a public API, a Users method on a custom web service, or some other entity reference with a corresponding filter. Visit the service provider documentation for details.

const query = wire.data.select()
    .from("Users")
    .where().eq("Active", true);

The following query will return the same list but adds an additional filter for domain users not in the usa.domain.

const query = wire.data.select()
    .from("Users")
    .where()
        .eq("Active", true)
        .ne("Domain", "usa.domain");

Custom Filters

The following query uses a custom object containing the same Active and Domain filters.

const activeFilter = { "Active": true, Domain: "uk.domain" };

const query = wire.data.select()
    .from("Users")
    .where()
        .custom(activeFilter);

The where method can also take a custom string expression directly as a parameter that will be a pass-through to the data service. This expression should be one that can be identified by the data service without any processing by WireBootstrap.

const query = wire.data.select()
    .from("Users")
    .where("Active=1 AND Domain='uk.domain'");

Select Fields

By default, a data service call will return all fields that result from the query. The select method takes an optional list that allows the fields to be specified.

The following query will return the UserName and FullName for all users.

const query = wire.data.select("UserName", "FullName")
    .from("Users");

The field method can also be used to select an individual field.

const query = wire.data.select()
    .field("UserName")
    .field("FullName")
    .from("Users");

Support for data service specific field references may also be available. See the documentation for the data connector being used to execute queries for details.

The following example is a SQL query to be executed using the WireBootstrap Query Service. The WireBootstrap Query Service is a SQL database data connector. The example query uses a SQL function to return the data for the Field1 field. It also returns a new field that formats the CreatedDate field.

const query = wire.data.select()
    .field("dbo.MySQLFunction() AS Field1")
    .field("FORMAT(CreatedDate, 'dd/MM/yy') AS CreatedDate")
    .from("Users");

Ordering

The following will return the same list sorted by UserName.

const query = wire.data.select()
    .from("Users")    
    .orderBy("UserName");

Distinct

The following will return the distinct list of UserName values from Users in the data service.

const query = wire.data.select("UserName").distinct()
    .from("Users")

Top

The following query will return the top 10 records sorted by UserName.

const query = wire.data.select().top(10)
    .from("Users")
    .orderBy("UserName");

Joins

Table queries support inner, left, right, and value joins.

The following query will return the UserName and FullName for all users by joining the Users entity with the Employee entity using an inner join.

const query = wire.data.select("UserName", "FullName")
    .from("Users")
    .join("Employees", "EmployeeId")
        .on("Users", "EmployeeId");

Some data connectors such as the WireBootstrap Query Service allow an entity qualified reference to be used in building queries.

The example below adds the EmployeeId field from the Users table to the select method. Failure to qualify this field as being in the Users table will results in an ambiguous name column error as the field also exists in the Employees table.

const query = wire.data.select("Users.EmployeeId", "UserName", "FullName")
    .from("Users")
    .join("Employees", "EmployeeId")
        .on("Users", "EmployeeId");

Visit the documentation for the data connector being used in the query for details.

Paging

The following query will retrieve the first virtual page of query results.

const query = wire.data.select()
    .from("Users")
    .page(1);

The number of records per page defaults to 10. To change this, use the second parameter to the page method.

const query = wire.data.select()
    .from("Users")
    .page(1, 20);

For more on table queries, visit the wire.data.TableQuery reference.