# Interacting with data

{% hint style="warning" %}
In order to continue, you must understand [Mandarine Query Language (MQL)](https://mandarineframework.gitbook.io/mandarine-ts/mandarine-data/mandarine-query-language), [Models](https://mandarineframework.gitbook.io/mandarine-ts/mandarine-data/orm/models) & [Repositories](https://mandarineframework.gitbook.io/mandarine-ts/mandarine-data/orm/repositories)
{% endhint %}

## Main

Mandarine repositories work directly with [MQL](https://mandarineframework.gitbook.io/mandarine-ts/mandarine-data/mandarine-query-language), since *MQL* serves as the primary query statement writer.

## @CustomQuery decorator

The `@CustomQuery` decorator allows you to write SQL queries in your repositories. While it is true that *MQL & Mandarine's ORM* aim to remove as much SQL & database code from your application's layer, it is also true that some use cases may require the use of complex SQL queries. 

**Syntax:**

```typescript
@CustomQuery(query: string, secure?: boolean)
```

* `query`: Query to execute
* `secure`: whether the query has secured parameters such as **$1**, **$2**, ...
  * Default: True

## Usage

{% tabs %}
{% tab title="Users Model" %}

```typescript
// usersModel.ts

import { Table, Id, GeneratedValue, Column } from "https://deno.land/x/mandarinets/mod.ts";

@Table({ schema: "public" })
class Users {

    @Id()
    @GeneratedValue({strategy: "SEQUENCE"})
    @Column()
    public id: number;

    @Column()
    public firstname: string;

    @Column()
    public lastname: string;

    @Column()
    public country: string;

    constructor(firstname?: string, lastname?: string, country?: string) {
        this.firstname = firstname;
        this.lastname = lastname;
        this.country = country;
    }

}
```

{% endtab %}

{% tab title="Repository" %}

```typescript
// usersRepository.ts

import { Repository, MandarineRepository } from "https://deno.land/x/mandarinets/mod.ts";
import { Users } from "./usersModel.ts";

@Repository()
abstract class UsersRepository extends MandarineRepository<Users> {

    constructor() {
        super(Users);
    }
    
    public findByCountry(country: string) {}
    public findByFirstnameAndCountry(firstname: string, country: string) {}
    public countByCountry(country: string) {}
    public existsByLastname(lastname: string) {}

    @CustomQuery("SELECT * FROM public.users WHERE country = $1 AND firstname = 'Andres'")
    public myCustomQuery(country: string) {}
}
```

{% endtab %}

{% tab title="Test Controller" %}

```typescript
// controller.ts

import { Controller, GET } from "https://deno.land/x/mandarinets/mod.ts";
import { Users } from "./usersModel.ts";
import { UsersRepository } from "./usersRepository.ts";

@Controller()
export class MyController {

    constructor(private readonly repository: UsersRepository) {}

    @GET('/add-people')
    public async handleAddPeople() {
        let user1 = new Users("Andres", "Pirela", "United States");
        let user2 = new Users("Anastasia", "Skymonov", "Russia");
        let user3 = new Users("Bill", "Gates", "United States");

        await this.repository.save(user1);
        await this.repository.save(user2);
        await this.repository.save(user3);

        return true;
    }

    @GET('/get-people-from-united-states')
    public async handleGetPeopleFromUnitedStates() {
        return await this.repository.findByCountry("United States");
    }

    @GET('/count-all')
    public async handleCountAll() {
        return await this.repository.countAll();
    }

    @GET('/count-russia')
    public async handleCountRussia() {
        return await this.repository.countByCountry("Russia");
    }

    @GET('/find-anastasia')
    public async handleFindAnastasia() {
        return await this.repository.findByFirstnameAndCountry("Anastasia", "Russia");
    }

    @GET('/exists-by-last-name')
    public async existsByLastname() {
        return await this.repository.existsByLastname("Gates");
    }

    @GET('/custom-query')
    public async customQueryHandler() {
        return await this.repository.myCustomQuery("United States");
    }
}
```

{% endtab %}

{% tab title="Mandarine's MVC test application" %}

```typescript
import { MandarineCore } from "https://deno.land/x/mandarinets/mod.ts";

import { UsersRepository } from "./usersRepository.ts";
import { MyController } from "./controller.ts";

const controllers = [MyController];
const repositories = [UsersRepository];

new MandarineCore().MVC().run();
```

{% endtab %}

{% tab title="Results" %}

```
# http://127.0.0.1:8080/get-people-from-united-states
# ==> [{"id":1,"firstname":"Andres","lastname":"Pirela","country":"United States"},{"id":3,"firstname":"Bill","lastname":"Gates","country":"United States"}]

##########

# http://127.0.0.1:8080/count-all
# ==> 3

##########

# http://127.0.0.1:8080/count-russia
# ==> 1

##########

# http://127.0.0.1:8080/find-anastasia
# ==> [{"id":2,"firstname":"Anastasia","lastname":"Skymonov","country":"Russia"}]

##########

# http://127.0.0.1:8080/exists-by-last-name
# ==> true

##########

# http://127.0.0.1:8080/custom-query
# ==> [{"id":1,"firstname":"Andres","lastname":"Pirela","country":"United States"}]


```

{% endtab %}
{% endtabs %}

### Note

* They keyword "**await**" should be used for all repository methods