# Examples and templates

1. Create a view that displays all directory items in code order.

```typescript
const selectAllUsers = await databaseManager.queryBuilder<Users[]>()
    .createView('allUsersView')
    .select()
    .from('users')
    .execute();
```

2. Create a selection query with logical operations under record selection conditions.

```typescript
const selectQueryExample = await databaseManager.queryBuilder<Tasks[]>()
    .select(['task_id', 'title', 'status'])
    .from('tasks')
    .where({
        conditions: {
            is_completed: { eq: false },
            price: { gt: 50 }
        },
        logicalOperator: 'and'
    })
    .orderBy('due_date', 'ASC')
    .execute();
```

3. Subqueries.

```typescript
const subquery = databaseManager.queryBuilder()
    .select(['username'])
    .from('users')
    .where({ conditions: { is_active: { eq: true } } })
    .build(true, false, false);

const mainQuery = await databaseManager.queryBuilder()
    .select(['user_id', 'username'])
    .from('users')
    .where({ conditions: { username: { in: subquery } } })
    .parametrize([subquery])
    .execute()
```

4. Joining tables.

```typescript
const queryWithLeftJoin = await databaseManager.queryBuilder()
    .select(['users.username', 'sections.name'])
    .from('users')
    .leftJoin('sections', {
        column: 'users.user_id', // основна колонка в таблиці Users
        operator: '=',
        value: 'sections.user_id' // колонка в таблиці Sections
    })
    .orderBy('users.username', 'ASC')
    .execute()
```

5. Connection.

```typescript
const query1 = databaseManager.queryBuilder()
    .select(['user_id', 'username'])
    .from('users')
    .where({ conditions: { is_active: { eq: true } } });

const query2 = databaseManager.queryBuilder()
    .select(['user_id', 'username'])
    .from('users')
    .where({ conditions: { is_active: { eq: false } } });

const exampleOfUnionQuery = await query1.union(query2).parametrize([true, false]).execute();
const exampleOfUnionAllQuery = await query1.unionAll(query2).parametrize([true, false]).execute();
```

6. Create a simple grouping query using the Sum statistical function.

<pre class="language-typescript"><code class="lang-typescript"><strong>const sumQuery = await databaseManager.queryBuilder()
</strong>    .select(['SUM(price) AS total_price'])
    .from('tasks')
    .execute();
</code></pre>

7. Creating a grouping query using the HAVING clause.

```typescript
const havingQuery = await databaseManager.queryBuilder()
    .select(['user_id', 'COUNT(*) AS task_count'])
    .from('tasks')
    .groupBy(['user_id'])
    .having({ conditions: { 'COUNT(*)': { gt: 5 } } })
    .execute();
```

8. Create a group request.

```typescript
const groupByQuery = await databaseManager.queryBuilder()
    .select(['user_id', 'COUNT(*) AS task_count'])
    .from('tasks')
    .groupBy(['user_id'])
    .execute();
```

9. Create an update request.

```typescript
const updateQueryExample = await databaseManager.queryBuilder()
    .update({ is_completed: true }, 'tasks')
    .where({ conditions: { task_id: { eq: 1 } }, logicalOperator: 'and' })
    .execute();
```

10. Create a removal request.

```typescript
const deleteQueryExample = await databaseManager.queryBuilder()
    .delete('tasks')
    .where({ conditions: { title: { eq: 'Завдання 2' } }})
    .execute();
```

11. Create an add request.

```typescript
const insertQueryExample = await databaseManager.queryBuilder()
    .insert({
        task_id: 1,
        title: 'Новий таск',
        description: 'Опис нового таску',
        is_completed: false,
        due_date: '2024-12-31',
        price: 100.50
    }, 'tasks')
    .execute();
```

12. Creating triggers.

```typescript
import {Trigger, TriggerEventsTypes, TriggerTimingsTypes, Table} from "@myroslavshymon/orm";

@Trigger({
    name: 'before_update_task',
    event: TriggerEventsTypes.UPDATE,
    timing: TriggerTimingsTypes.BEFORE,
    triggerFunction: `
        BEGIN
            IF NEW.is_completed THEN
                NEW.completed_at = CURRENT_TIMESTAMP;
            END IF;
            RETURN NEW;
        END;
    `,
    triggerFunctionName: 'set_completion_time'
})
@Table({name: 'tasks'})
export class Tasks {
    // поля таблиці tasks
}
```

13. Creation of indexes.

<pre class="language-typescript"><code class="lang-typescript">import {Index, Column, PrimaryGeneratedColumn, Table} from "@myroslavshymon/orm";

<strong>@Index({indexName: 'user_index', columns: ['username', 'email'], options: {isUnique: true}})
</strong>@Table({name: 'users'})
export class Users {
    @PrimaryGeneratedColumn({type: 'BIGINT'})
    user_id: number;

    @Column({options: {dataType: "VARCHAR", length: 255}})
    username: string;

    @Column({options: {dataType: "VARCHAR", length: 255}})
    email: string;

    @Column({options: {dataType: "VARCHAR", length: 255}})
    password: string;

    @Column({options: {dataType: 'BOOLEAN', defaultValue: false}})
    is_active: boolean;
}
</code></pre>

14. Caching.

```typescript
const selectUsersQueryCached = await databaseManager.queryBuilder<Users[]>()
    .select(['user_id', 'username', 'email'])
    .from('users')
    .orderBy('username', 'ASC')
    .cache({ ttl: 60000, key: 'users_list' });
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://myroslavs-organization.gitbook.io/orm/examples-and-templates.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.