npm package: @getsupervisor/api-query-builder
Description: Provides an advanced tool for dynamic SQL query construction, designed to facilitate integration into applications that require sophisticated filtering and search capabilities.
Latest version: 1.3.4

# Query Builder

Provides an advanced tool for dynamic SQL query construction, designed to facilitate integration into applications that require sophisticated filtering and search capabilities.

## Introduction

The `@barrerajulio/api-query-builder` library offers a robust solution for developers needing to dynamically and efficiently construct SQL filters. This module enables the creation of complex search conditions using simple and expressive syntax, applicable in the `where` parameter for various operations.

Usage examples:

```sql
where=eq(field,value)
where=and(eq(field,value),eq(field1,value1))
where=or(eq(field,value),eq(field1,value1))
```

## Table Of Contents

- [Query Builder](#query-builder)
  - [Introduction](#introduction)
  - [Table Of Contents](#table-of-contents)
  - [Features](#features)
  - [Requirements](#requirements)
  - [Installation](#installation)
    - [Clone The Repository](#clone-the-repository)
    - [Navigate To The Project Directory](#navigate-to-the-project-directory)
    - [Install Dependencies](#install-dependencies)
  - [Usage](#usage)
    - [TypeORM integration](#typeorm-integration)
  - [Supported Operators](#supported-operators)
    - [`And`](#and)
    - [`Or`](#or)
    - [`Between`](#between)
    - [`Eq`](#eq)
    - [`Ilike`](#ilike)
    - [`In`](#in)
    - [`IsNull`](#isnull)
    - [`Like`](#like)
    - [`Lt`](#lt)
    - [`Lte`](#lte)
    - [`Mt`](#mt)
    - [`Mte`](#mte)
    - [`Not`](#not)
  - [Advanced Example](#advanced-example)
  - [Development](#development)
  - [Contributions](#contributions)
  - [License](#license)

## Features

- **Dynamic Construction**: Programmatically generates SQL queries, dynamically adapting conditions based on application requirements.
- **Multiple Operator Compatibility**: Support for a wide range of logical and comparative operators (AND, OR, NOT, etc.).
- **Intuitive Syntax**: User interface designed to be easy to understand and use, facilitating the creation of complex search conditions.
- **Extensibility**: Ability to combine and nest operators to build advanced queries, maintaining flexibility in their definition.

## Requirements

- **Node.js**: Recommended compatible versions (>=14.0.0).
- **TypeScript**: It is preferable to use TypeScript to take advantage of static typing.

## Installation

Follow the steps below to install and configure the library in your project:

### Clone The Repository

```bash
git clone https://github.com/barrerajulio/api-query-builder.git
```

### Navigate To The Project Directory

```bash
cd api-query-builder
```

### Install Dependencies

Use `npm` or `yarn` to install the project's dependencies:

```bash
npm install @barrerajulio/api-query-builder
```

or

```bash
yarn add @barrerajulio/api-query-builder
```

## Usage

To use the library in your project, first import the `Query` class and use it to build your queries:

```javascript
import { Query } from '@barrerajulio/api-query-builder';

const query = new Query(qb => qb.eq('field', 'value'));
console.log(`Query: ${query.build()}`);
```

This example demonstrates how to construct a basic query. You can expand this query using the various supported operators.

### TypeORM integration

If you need to materialize the parsed expression into TypeORM constructs, import the optional TypeORM bundle. It lives under a separate entry point so the core package remains frontend friendly:

```typescript
import { QueryBuilder, FindWhereQueryBuilder } from '@barrerajulio/api-query-builder/typeorm';

const builder = new QueryBuilder();
const brackets = builder.build('and(eq(user.name,Foo),eq(user.status,ACTIVE))', {
    allowedFields: ['user.name', 'user.status'],
});

const findWhere = new FindWhereQueryBuilder();
const conditions = findWhere.build('or(eq(id,1),eq(id,2))', {
    allowedFields: ['id'],
});
```

To keep the optional dependency tree small, TypeORM is declared as an optional peer dependency. Install it alongside this package in backend projects:

```bash
yarn add typeorm @barrerajulio/api-query-builder
```

## Supported Operators

Below are the operators you can use to build queries:

### `And`

The `and` operator allows you to combine multiple conditions, ensuring that all must be met for the filter to be valid.

Example:

```javascript
new Query(
    qb => qb.and(
        qb.eq('field1', 'value1'),
        qb.eq('field2', 'value2')
    )
);
```

### `Or`

The `or` operator allows any of the combined conditions to be met.

Example:

```javascript
new Query(
    qb => qb.or(
        qb.mt('field1', 10),
        qb.mte('field2', 20)
    )
);
```

### `Between`

This operator is useful for comparing ranges, such as dates or numeric values.

Example:

```javascript
new Query(
    qb => qb.between('field1', 10, 20)
);
```

### `Eq`

Compares the value of a field to a specific value.

Example:

```javascript
new Query(
    qb => qb.eq('field1', 'value1')
);
```

### `Ilike`

Performs a partial comparison, ignoring case sensitivity.

Example:

```javascript
new Query(
    qb => qb.ilike('field1', '%value1%')
);
```

### `In`

Checks if a field matches any of the values in a list.

Example:

```javascript
new Query(
    qb => qb.in('field1', 'value1', 'value2', 'value3')
);
```

### `IsNull`

Validates that the field is null.

Example:

```javascript
new Query(
    qb => qb.isNull('field1')
);
```

### `Like`

Performs a partial comparison, considering case sensitivity.

Example:

```javascript
new Query(
    qb => qb.like('field1', '%value1%')
);
```

### `Lt`

Compares if a field is less than a given value.

Example:

```javascript
new Query(
    qb => qb.lt('field1', 10)
);
```

### `Lte`

Compares if a field is less than or equal to a given value.

Example:

```javascript
new Query(
    qb => qb.lte('field1', 10)
);
```

### `Mt`

Compares if a field is greater than a given value.

Example:

```javascript
new Query(
    qb => qb.mt('field1', 10)
);
```

### `Mte`

Compares if a field is greater than or equal to a given value.

Example:

```javascript
new Query(
    qb => qb.mte('field1', 10)
);
```

### `Not`

Inverts the result of a given condition.

Example:

```javascript
new Query(
    qb => qb.not(qb.eq('field1', 'value1'))
);
```

## Advanced Example

The library allows queries to be constructed iteratively, offering great flexibility for complex scenarios.

```javascript
const { Query } = require('@barrerajulio/api-query-builder');

let query = new Query(qb => 
    qb.and(
        qb.eq('field1', 'value1'),
        qb.or(
            qb.eq('field2', 'value2'),
            qb.and(
                qb.eq('field3', 'value3'),
                qb.eq('field4', 'value4')
            )
        )
    )
);

const isPrivate = true;

if (isPrivate) {
    query = query.and(qb => qb.eq('field5', 'value5'));
}
```

This example demonstrates how to construct an initial query and then modify it based on external conditions, enabling the creation of adaptive and contextual queries.

## Development

If you want to contribute to the development of this project, follow these steps:

1. Fork the repository.
2. Create a new branch (`feature/new-functionality`).
3. Implement your changes.
4. Submit a pull request for review.

## Contributions

Contributions are welcome. Please follow the guidelines below to collaborate effectively:

1. Fork the repository if you are not an internal collaborator.
2. Create a new branch (`feature/new-functionality`).
3. Implement your changes and make descriptive commits.
4. Submit a pull request detailing the changes and their purposes.

## License

This project is under a private license. For more details, see the `LICENSE` file included in the repository.

All rights reserved