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