<commit_before># Select using Query Builder

* [What is `QueryBuilder`](#what-is-querybuilder)
* [Important note when using the `QueryBuilder`](#important-note-when-using-the-querybuilder)
* [How to create and use a `QueryBuilder`](#how-to-create-and-use-a-querybuilder)
* [Getting values using QueryBuilder](#getting-values-using-querybuilder)
* [What are aliases for?](#what-are-aliases-for)
* [Using parameters to escape data](#using-parameters-to-escape-data)
* [Adding `WHERE` expression](#adding-where-expression)
* [Adding `HAVING` expression](#adding-having-expression)
* [Adding `ORDER BY` expression](#adding-order-by-expression)
* [Adding `GROUP BY` expression](#adding-group-by-expression)
* [Adding `LIMIT` expression](#adding-limit-expression)
* [Adding `OFFSET` expression](#adding-offset-expression)
* [Joining relations](#joining-relations)
* [Inner and left joins](#inner-and-left-joins)
* [Join without selection](#join-without-selection)
* [Joining any entity or table](#joining-any-entity-or-table)
* [Joining and mapping functionality](#joining-and-mapping-functionality)
* [Getting the generated query](#getting-the-generated-query)
* [Getting raw results](#getting-raw-results)
* [Streaming result data](#streaming-result-data)
* [Using pagination](#using-pagination)
* [Set locking](#set-locking)
* [Use custom index](#use-custom-index)
* [Max execution time](#max-execution-time)
* [Partial selection](#partial-selection)
* [Using subqueries](#using-subqueries)
* [Hidden Columns](#hidden-columns)
* [Querying Deleted rows](#querying-deleted-rows)

## What is `QueryBuilder`

`QueryBuilder` is one of the most powerful features of TypeORM -
it allows you to build SQL queries using elegant and convenient syntax,
execute them and get automatically transformed entities.

Simple example of `QueryBuilder`:

```typescript
const firstUser = await connection
    .getRepository(User)
    .createQueryBuilder("user")
    .where("user.id = :id", { id: 1 })
    .getOne();
```

It builds the following SQL query:

```sql
SELECT
    user.id as userId,
    user.firstName as userFirstName,
    user.lastName as userLastName
FROM users user
WHERE user.id = 1
```

and returns you an instance of `User`:

```
User {
    id: 1,
    firstName: "Timber",
    lastName: "Saw"
}
```

## Important note when using the `QueryBuilder`

When using the `QueryBuilder`, you need to provide unique parameters in your `WHERE` expressions. **This will not work**:

```TypeScript
const result = await getConnection()
    .createQueryBuilder('user')
    .leftJoinAndSelect('user.linkedSheep', 'linkedSheep')
    .leftJoinAndSelect('user.linkedCow', 'linkedCow')
    .where('user.linkedSheep = :id', { id: sheepId })
    .andWhere('user.linkedCow = :id', { id: cowId });
```

... but this will:

```TypeScript
const result = await getConnection()
    .createQueryBuilder('user')
    .leftJoinAndSelect('user.linkedSheep', 'linkedSheep')
    .leftJoinAndSelect('user.linkedCow', 'linkedCow')
    .where('user.linkedSheep = :sheepId', { sheepId })
    .andWhere('user.linkedCow = :cowId', { cowId });
```

Note that we uniquely named `:sheepId` and `:cowId` instead of using `:id` twice for different parameters.

## How to create and use a `QueryBuilder`

There are several ways how you can create a `Query Builder`:

* Using connection:

    ```typescript
    import {getConnection} from "typeorm";

    const user = await getConnection()
        .createQueryBuilder()
        .select("user")
        .from(User, "user")
        .where("user.id = :id", { id: 1 })
        .getOne();
    ```

* Using entity manager:

    ```typescript
    import {getManager} from "typeorm";

    const user = await getManager()
        .createQueryBuilder(User, "user")
        .where("user.id = :id", { id: 1 })
        .getOne();
    ```

* Using repository:

    ```typescript
    import {getRepository} from "typeorm";

    const user = await getRepository(User)
        .createQueryBuilder("user")
        .where("user.id = :id", { id: 1 })
        .getOne();
    ```

There are 5 different `QueryBuilder` types available:

* `SelectQueryBuilder` - used to build and execute `SELECT` queries. Example:

    ```typescript
    import {getConnection} from "typeorm";

    const user = await getConnection()
        .createQueryBuilder()
        .select("user")
        .from(User, "user")
        .where("user.id = :id", { id: 1 })
        .getOne();
    ```

* `InsertQueryBuilder` - used to build and execute `INSERT` queries. Example:

    ```typescript
    import {getConnection} from "typeorm";

    await getConnection()
        .createQueryBuilder()
        .insert()
        .into(User)
        .values([
            { firstName: "Timber", lastName: "Saw" },
            { firstName: "Phantom", lastName: "Lancer" }
         ])
        .execute();
    ```

* `UpdateQueryBuilder` - used to build and execute `UPDATE` queries. Example:

    ```typescript
    import {getConnection} from "typeorm";

    await getConnection()
        .createQueryBuilder()
        .update(User)
        .set({ firstName: "Timber", lastName: "Saw" })
        .where("id = :id", { id: 1 })
        .execute();
    ```
* `DeleteQueryBuilder` - used to build and execute `DELETE` queries. Example:

    ```typescript
    import {getConnection} from "typeorm";

    await getConnection()
        .createQueryBuilder()
        .delete()
        .from(User)
        .where("id = :id", { id: 1 })
        .execute();
    ```

* `RelationQueryBuilder` - used to build and execute relation-specific operations [TBD].

You can switch between different types of query builder within any of them,
once you do, you will get a new instance of query builder (unlike all other methods).

## Getting values using `QueryBuilder`

To get a single result from the database,
for example to get a user by id or name, you must use `getOne`:

```typescript
const timber = await getRepository(User)
    .createQueryBuilder("user")
    .where("user.id = :id OR user.name = :name", { id: 1, name: "Timber" })
    .getOne();
```

`getOneOrFail` will get a single result from the database, but if
no result exists it will throw an `EntityNotFoundError`:

```typescript
const timber = await getRepository(User)
    .createQueryBuilder("user")
    .where("user.id = :id OR user.name = :name", { id: 1, name: "Timber" })
    .getOneOrFail();
```

To get multiple results from the database,
for example, to get all users from the database, use `getMany`:

```typescript<commit_msg>docs: update select-query-builder RelationQueryBuilder with a example (#8658)<commit_after># Select using Query Builder

* [What is `QueryBuilder`](#what-is-querybuilder)
* [Important note when using the `QueryBuilder`](#important-note-when-using-the-querybuilder)
* [How to create and use a `QueryBuilder`](#how-to-create-and-use-a-querybuilder)
* [Getting values using QueryBuilder](#getting-values-using-querybuilder)
* [What are aliases for?](#what-are-aliases-for)
* [Using parameters to escape data](#using-parameters-to-escape-data)
* [Adding `WHERE` expression](#adding-where-expression)
* [Adding `HAVING` expression](#adding-having-expression)
* [Adding `ORDER BY` expression](#adding-order-by-expression)
* [Adding `GROUP BY` expression](#adding-group-by-expression)
* [Adding `LIMIT` expression](#adding-limit-expression)
* [Adding `OFFSET` expression](#adding-offset-expression)
* [Joining relations](#joining-relations)
* [Inner and left joins](#inner-and-left-joins)
* [Join without selection](#join-without-selection)
* [Joining any entity or table](#joining-any-entity-or-table)
* [Joining and mapping functionality](#joining-and-mapping-functionality)
* [Getting the generated query](#getting-the-generated-query)
* [Getting raw results](#getting-raw-results)
* [Streaming result data](#streaming-result-data)
* [Using pagination](#using-pagination)
* [Set locking](#set-locking)
* [Use custom index](#use-custom-index)
* [Max execution time](#max-execution-time)
* [Partial selection](#partial-selection)
* [Using subqueries](#using-subqueries)
* [Hidden Columns](#hidden-columns)
* [Querying Deleted rows](#querying-deleted-rows)

## What is `QueryBuilder`

`QueryBuilder` is one of the most powerful features of TypeORM -
it allows you to build SQL queries using elegant and convenient syntax,
execute them and get automatically transformed entities.

Simple example of `QueryBuilder`:

```typescript
const firstUser = await connection
    .getRepository(User)
    .createQueryBuilder("user")
    .where("user.id = :id", { id: 1 })
    .getOne();
```

It builds the following SQL query:

```sql
SELECT
    user.id as userId,
    user.firstName as userFirstName,
    user.lastName as userLastName
FROM users user
WHERE user.id = 1
```

and returns you an instance of `User`:

```
User {
    id: 1,
    firstName: "Timber",
    lastName: "Saw"
}
```

## Important note when using the `QueryBuilder`

When using the `QueryBuilder`, you need to provide unique parameters in your `WHERE` expressions. **This will not work**:

```TypeScript
const result = await getConnection()
    .createQueryBuilder('user')
    .leftJoinAndSelect('user.linkedSheep', 'linkedSheep')
    .leftJoinAndSelect('user.linkedCow', 'linkedCow')
    .where('user.linkedSheep = :id', { id: sheepId })
    .andWhere('user.linkedCow = :id', { id: cowId });
```

... but this will:

```TypeScript
const result = await getConnection()
    .createQueryBuilder('user')
    .leftJoinAndSelect('user.linkedSheep', 'linkedSheep')
    .leftJoinAndSelect('user.linkedCow', 'linkedCow')
    .where('user.linkedSheep = :sheepId', { sheepId })
    .andWhere('user.linkedCow = :cowId', { cowId });
```

Note that we uniquely named `:sheepId` and `:cowId` instead of using `:id` twice for different parameters.

## How to create and use a `QueryBuilder`

There are several ways how you can create a `Query Builder`:

* Using connection:

    ```typescript
    import {getConnection} from "typeorm";

    const user = await getConnection()
        .createQueryBuilder()
        .select("user")
        .from(User, "user")
        .where("user.id = :id", { id: 1 })
        .getOne();
    ```

* Using entity manager:

    ```typescript
    import {getManager} from "typeorm";

    const user = await getManager()
        .createQueryBuilder(User, "user")
        .where("user.id = :id", { id: 1 })
        .getOne();
    ```

* Using repository:

    ```typescript
    import {getRepository} from "typeorm";

    const user = await getRepository(User)
        .createQueryBuilder("user")
        .where("user.id = :id", { id: 1 })
        .getOne();
    ```

There are 5 different `QueryBuilder` types available:

* `SelectQueryBuilder` - used to build and execute `SELECT` queries. Example:

    ```typescript
    import {getConnection} from "typeorm";

    const user = await getConnection()
        .createQueryBuilder()
        .select("user")
        .from(User, "user")
        .where("user.id = :id", { id: 1 })
        .getOne();
    ```

* `InsertQueryBuilder` - used to build and execute `INSERT` queries. Example:

    ```typescript
    import {getConnection} from "typeorm";

    await getConnection()
        .createQueryBuilder()
        .insert()
        .into(User)
        .values([
            { firstName: "Timber", lastName: "Saw" },
            { firstName: "Phantom", lastName: "Lancer" }
         ])
        .execute();
    ```

* `UpdateQueryBuilder` - used to build and execute `UPDATE` queries. Example:

    ```typescript
    import {getConnection} from "typeorm";

    await getConnection()
        .createQueryBuilder()
        .update(User)
        .set({ firstName: "Timber", lastName: "Saw" })
        .where("id = :id", { id: 1 })
        .execute();
    ```
* `DeleteQueryBuilder` - used to build and execute `DELETE` queries. Example:

    ```typescript
    import {getConnection} from "typeorm";

    await getConnection()
        .createQueryBuilder()
        .delete()
        .from(User)
        .where("id = :id", { id: 1 })
        .execute();
    ```

* `RelationQueryBuilder` - used to build and execute relation-specific operations [TBD].
Example:

    ```typescript
    import {getConnection} from "typeorm";

    await getConnection()
        .createQueryBuilder()
        .relation(User,"photos")
        .of(id)
        .loadMany();
    ```

You can switch between different types of query builder within any of them,
once you do, you will get a new instance of query builder (unlike all other methods).

## Getting values using `QueryBuilder`

To get a single result from the database,
for example to get a user by id or name, you must use `getOne`:

```typescript
const timber = await getRepository(User)
    .createQueryBuilder("user")
    .where("user.id = :id OR user.name = :name", { id: 1, name: "Timber" })
    .getOne();
```

`getOneOrFail` will get a single result from the database, but if
no result exists it will throw an `EntityNotFoundError`:

```typescript
const timber = await getRepository(User)
    .createQueryBuilder("user")
    .where("user.id = :id OR user.name = :name", { id: 1, name: "Timber" })
    .getOneOrFail();
```

To get multiple results from the database,
for example, to get all users from the database, use `getMany`:

```typescript
{'fraction_non_alphanumeric': 0.14214876033057852, 'fraction_numerical': 0.0021036814425244178, 'mean_word_length': 3.5979274611398964, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '897511', 'n_tokens_mistral': 4117, 'n_tokens_neox': 3924, 'n_words': 1190}