# 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](https://docs.wirebootstrap.com/wirebootstrap/reference/wire.data/wire.data.tablequery) object.  This object is then passed through the data source to the [service provider](https://docs.wirebootstrap.com/wirebootstrap/connecting-to-data/data-connectors) 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](https://docs.wirebootstrap.com/wirebootstrap/connecting-to-data/data-sources) 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](https://docs.wirebootstrap.com/wirebootstrap/reference/wire.data/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.

```javascript
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`.

```javascript
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.  

```javascript
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.

```javascript
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.

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

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

```javascript
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](https://docs.wirebootstrap.com/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.

```javascript
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`.

```javascript
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.

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

## Top

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

```javascript
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.

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

Some data connectors such as the [WireBootstrap Query Service](https://docs.wirebootstrap.com/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.

```javascript
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.

```javascript
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.

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

For more on table queries, visit the [wire.data.TableQuery](https://docs.wirebootstrap.com/wirebootstrap/reference/wire.data/wire.data.tablequery) reference.