Comment on page
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.
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");
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'");
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");
The following will return the same list sorted by
UserName.const query = wire.data.select()
.from("Users")
.orderBy("UserName");
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")
The following query will return the top
10 records sorted by UserName.const query = wire.data.select().top(10)
.from("Users")
.orderBy("UserName");
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.
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);
Last modified 3mo ago