# List records

FastSchema use RQL to query the records. It supports various query parameters to enable you to filter, sort, and paginate the records.

Source: https://fastschema.com/docs/backend/list-records

FastSchema use RQL to query the records. It supports various query parameters to enable you to filter, sort, and paginate the records.

To list the records, you can send a `GET` request to the `/api/content/{schema}` endpoint. The response will contain the list of records that match the query parameters.

## Pagination

Pagination allows users to retrieve resources in smaller, manageable chunks, reducing the response payload size and improving performance. It provides control over the number of resources returned in a single response, making it easier to navigate through large datasets.

To paginate the records, you can use the `limit` and `page` query parameters. The `limit` parameter specifies the number of records to return, while the `page` parameter specifies the page number.

**Example**

```http
GET /api/content/?filter={"name":{"$like":"%aaa%"}}&limit=2&page=2
```

<Accordions type="single">
<Accordion title="Example response">

```json
{
  "data": {
    "total": 10,
    "per_page": 2,
    "current_page": 2,
    "last_page": 5,
    "items": [
      {
        "id": "550e8400-e29b-41d4-a716-446655440008",
        "name": "Lorem ipsum dolor sit amet, consectetur adipiscing elit",
        "created_at": "2024-05-25T22:39:49Z"
      },
      {
        "id": "550e8400-e29b-41d4-a716-446655440007",
        "name": "Lorem ipsum dolor sit amet, consectetur adipiscing elit",
        "created_at": "2024-05-25T22:39:49Z"
      }
    ]
  }
}
```

</Accordion>
</Accordions>

<Callout type="info" title="Note">

The default `limit` value is `10`, and the default `page` value is `1`. If you do not specify these parameters, the API will return the first 10 records.

</Callout>

## Count

The count API allows users to retrieve the total count of resources that match specified filtering criteria without pagination. This feature is useful for obtaining an accurate count of resources that meet specific conditions, enabling users to perform analytics, generate reports, or gain insights into their data.

**Example**

```http
GET /api/content/post/?filter={"name":{"$like":"%aaa%"}}
```

<Callout type="info" title="Note">

The count API will ignore the `limit`, `page` parameters and does not return the actual records. It only returns the total count of records that match the specified filtering criteria.

</Callout>

## Select

The `select` query parameter allows you to specify which fields to include in the response. You can use the `select` parameter to retrieve only the fields you need, reducing the response payload size.

**Example**

```http
GET /api/content/post/?select=id,name,categories.name
```

<Accordions type="single">
<Accordion title="Example response">

```json
{
  "data": [
    {
      "id": "550e8400-e29b-41d4-a716-446655440001",
      "name": "Post 01",
      "categories": [
        {
          "name": "Category A"
        },
        {
          "name": "Category B"
        }
      ]
    },
    {
      "id": "550e8400-e29b-41d4-a716-446655440002",
      "name": "Post 02",
      "categories": [
        {
          "name": "Category C"
        }
      ]
    }
  ]
}
```

</Accordion>
</Accordions>

`select` also supports selecting the relationship fields. You can use the dot notation to select the fields of the related resources. In the example above, we are selecting the `name` field of the `categories` relationship.

<Callout type="error" title="Note">

The selection of relationship fields does not support nested relationships.

</Callout>

## Sort

The `sort` query parameter allows you to specify the sorting order of the records.

The `sort` parameter accepts a string containing the field name and the sorting direction. By default, the sorting direction is ascending. You can change the sorting direction by adding a prefix (`-` for descending, `+` for ascending).

- **Ascending**: `GET /api/content/post/?sort=name`
- **Descending**: `GET /api/content/post/?sort=-name`

## Filter

The `filter` query parameter allows you to filter the records based on specific criteria.

The `filter` parameter accepts an object containing the filter criteria based on the schema fields.

**Example**

```http
GET /api/content/post/?filter={"name":{"$like":"%post01%"},"categories.name":{"$like":"%category01%"}}
```

### Simple filter object

```json
{
  // (1) Match the field exactly
  "country": "USA",
  // (2) Multiple AND conditions
  "name": {
    "$like": "test%",
    "$neq": "test2"
  }
}
```

A simple filter object contains the field names as keys and the filter criteria as values. The filter criteria can be one of the following:

- A value to match the field exactly. For example, `(1)` filters the records with `country = 'USA'`.
- An object containing multiple `AND` conditions. For example, `(2)` filters the records with `name LIKE 'test%' AND name != 'test2'`.

All the filter criteria in the filter object are combined using the `AND` operator. For example, `(1)` and `(2)` are combined using the `AND` operator.

```sql
country = 'USA' AND (name LIKE 'test%' AND name != 'test2')
```

### Complex filter object

```json {7-32}
{
  "country": "USA",
  "name": {
    "$like": "test%",
    "$neq": "test2"
  },
  "$or": [
    {
      "email": {
        "$like": "test%",
        "$neq": "test2"
      },
      "age": {
        "$lt": 10
      }
    },
    {
      "age": 5
    },
    {
      "$and": [
        {
          "name": {
            "$neq": "test2"
          }
        },
        {
          "age": 15
        }
      ]
    }
  ]
}
```

A complex filter object is similar to a simple filter object but contains additional logical operators (`$or`, `$and`) to combine multiple filter criteria.

`$and` operator combines multiple filter criteria using the `AND` operator.

`$or` operator combines multiple filter criteria using the `OR` operator. For example, the above filter object filters the records with the following conditions:

```sql
country = 'USA' AND (name LIKE 'test%' AND name != 'test2') AND (
  (email LIKE 'test%' AND email != 'test2' AND age < 10) OR
  (age = 5) OR
  (name != 'test2' AND age = 15)
)
```

<Callout type="info" title="Filter by relationship fields">

You can filter the records based on the fields of the related resources. You can use the dot notation to filter the fields of the related resources. For example, to filter the records based on the `name` field of the `categories` relationship:

```json {5-7}
{
  "name": {
    "$like": "%post01%"
  },
  "categories.name": {
    "$like": "%category01%"
  }
}
```

</Callout>

## Supported operators

### $eq

The `$eq` operator matches the field exactly.

### $neq

The `$neq` operator matches the field that is not equal to the specified value.

### $gt and $gte

The `$gt` operator matches the field that is greater than the specified value. The `$gte` operator matches the field that is greater than or equal to the specified value.

### $lt and $lte

The `$lt` operator matches the field that is less than the specified value. The `$lte` operator matches the field that is less than or equal to the specified value.

### $like

The `$like` operator performs a string matching with wildcard support. You can use the `%` wildcard to match any sequence of characters.

### $in

The `$in` operator matches the field that is in the specified array.

```json
{
  "name": {
    "$in": ["John", "Doe"]
  }
}
```

### $nin

The `$nin` operator matches the field that is not in the specified array.

```json
{
  "name": {
    "$nin": ["John", "Doe"]
  }
}
```

### $null

The `$null` operator checks for null values.

```json
{
  "name": {
    "$null": true
  }
}
```

### $and

The `$and` operator combines multiple filter criteria using the `AND` operator.

### $or

The `$or` operator combines multiple filter criteria using the `OR` operator.

<Callout type="info" title="Supported data types">

`$eq`, `$neq`, `$gt`, `$gte`, `$lt`, `$lte` operators support the following data types:

- String
- Number
- Boolean
- Date

</Callout>

## Known issues

When filtering `$neq` operation on a `m2m` relation field, the relation is not filtered properly.

**For example:**

Assume that we have two schemas `post`, `tag` and the `post.tags` field is a `m2m` relation to the `tag.posts` field.

We want to filter the posts that do not have the tag with a specific UUID.

```http
GET http://localhost:8000/api/content/post?select=id \

&filter={"tags.id":{"$neq":"550e8400-e29b-41d4-a716-446655440010"}}
```

The expected result should return the posts that:

- Have no tags.
- Have tags other than the tag with `id: 10001`.

The above request will perform the following SQL query, that is wrong:

```sql
SELECT * FROM `posts`
WHERE `posts`.`id` IN (
  SELECT `posts_tags`.`posts`
  FROM `posts_tags`
  JOIN `tags` AS `t1`
  ON `posts_tags`.`tags` = `t1`.`id`
  WHERE `id` <> 10001
)
ORDER BY `id` DESC
```

<Callout type="error" title="The problem">

The above SQL query try to filter the `tags.id` from the join table `posts_tags`. This is incorrect because the join table is just storing the information for the posts that have the tags.

So, the posts that do not have any tags will not be returned in the result.

</Callout>

The correct SQL query should be:

```sql
SELECT * FROM `posts`
WHERE `posts`.`id` NOT IN (
  SELECT `posts_tags`.`posts`
  FROM `posts_tags`
  JOIN `tags` AS `t1`
  ON `posts_tags`.`tags` = `t1`.`id`
  WHERE `id` = '550e8400-e29b-41d4-a716-446655440010'
)
ORDER BY `id` DESC
```