# Query interface and Native Queries

{% hint style="success" %}
This is the official documentation of the `forestadmin/laravel-forestadmin` v2+ and `forestadmin/symfony-forestadmin` PHP agents.
{% endhint %}

Forest Admin can connect to any data source, as long as it can be represented as a collection of records that have a common structure.

To achieve that, Forest Admin needs to abstract away data source differences: each connector "speaks" the language of a given API on one side and exposes the Forest Admin Query Interface on the other.

This interface is called the `Forest Admin Query Interface`, it is *not* a full-featured ORM: its objective is to be "just enough" to fuel Forest Admin.

## Choosing how to query your data

The Forest Admin Query Interface is used to implement all native features of the admin panel, however, when writing custom code ([creating new actions](https://docs.forestadmin.com/developer-guide-agents-php/agent-customization/actions), [fields](https://docs.forestadmin.com/developer-guide-agents-php/agent-customization/fields), ...), you can either access your data using the Forest Admin Query Interface or using the native driver.

The choice is yours, and you will probably use both depending on the situation.

| -                                                                  | Forest Admin Query Interface                         | Native Driver                             |
| ------------------------------------------------------------------ | ---------------------------------------------------- | ----------------------------------------- |
| Code consistency                                                   | 👍 Use the same query interface for all data sources | 👎 Different API for each database / SaaS |
| Customizations can be queried (computed field, relationships, ...) | 👍 Yes                                               | 👎 No                                     |
| Features                                                           | 👎 Common denominator is exposed                     | 👍 All features of the underlying API     |
| In-app deployments                                                 | 👎 Difficult to reuse your existing code             | 👍 Re-use your existing code              |
| Learning curve                                                     | 👎 The interface is Forest Admin specific            | 👍 You already know how to write SQL      |
| Native support for filters from the UI                             | 👍 Yes                                               | 👎 No                                     |
| Total                                                              | 3 x 👍 + 3 x 👎                                      | 3 x 👍 + 3 x 👎                           |

## In practice

### Querying with the native driver

As the name implies, native drivers have different interfaces for each data source.

{% tabs %}
{% tab title="SQL" %}

```php
use ForestAdmin\AgentPHP\DatasourceCustomizer\CollectionCustomizer;
use ForestAdmin\AgentPHP\DatasourceCustomizer\Context\CollectionCustomizationContext;
use ForestAdmin\AgentPHP\DatasourceToolkit\Components\Query\ConditionTree\Operators;

$client = new PDO('pgsql:host=localhost;dbname=myDb;', 'username', 'password');

$forestAgent->customizeCollection(
    'Customer',
    function (CollectionCustomizer $builder) {
        $builder->addSegment(
            'highPrice',
            function (CollectionCustomizationContext $context) {
                $query = $client->prepare(
                    'SELECT product_id, COUNT(*) FROM orders
                     GROUP BY product_id
                     ORDER BY count DESC
                     LIMIT 10;'
                );
                $query->execute();
                $rows = $query->fetchAll(PDO::FETCH_ASSOC);

                return [
                	'field'    => 'id',
                	'operator' => Operators::IN,
                	'value'    => array_map(fn ($r) => $r['product_id'], $rows)
                ];
            }
        );
    }
);
```

{% endtab %}

{% tab title="Laravel QueryBuilder" %}

```php
use ForestAdmin\AgentPHP\DatasourceCustomizer\CollectionCustomizer;

->customizeCollection('Category', function (CollectionCustomizer $builder) {
            $builder->addSegment(
                'highPrice',
                function () {
                    $query = DB::table('orders')
                        ->select('product_id')
                        ->groupBy('product_id')
                        ->orderByRaw('count(*) DESC')
                        ->limit(10)
                        ->get();

                    return [
                        'field'     => 'id',
                        'operator'  => 'In',
                        'value'     => collect($query)->pluck('product_id')->toArray(),
                    ];
                }
            );
        });
```

{% endtab %}

{% tab title="Symfony QueryBuilder" %}

```php
use ForestAdmin\AgentPHP\DatasourceCustomizer\CollectionCustomizer;

$forestAgent->customizeCollection(
    'Customer',
    function (CollectionCustomizer $builder) {
        $builder->addSegment(
            'highPrice',
            function ($context) {
                    /** @var \Doctrine\ORM\EntityManager $em */
                    $em = $context->getCollection()->getNativeDriver();

                    $qb = $em->createQueryBuilder()
                        ->select('p.id as product_id', 'count(o.id) as nb')
                        ->from(\App\Entity\Order::class, 'o')
                        ->innerJoin(\App\Entity\Product::class, 'p', 'WITH', 'o.product = p.id')
                        ->groupBy('p.id')
                        ->orderBy('nb', 'DESC')
                        ->setMaxResults(10);
                    $query = $qb->getQuery();

                    // OR with DQL string
                    // $query = $em->createQuery('
                    //    SELECT p.id as product_id, count(o.id) as nb
                    //    FROM App\Entity\Order o
                    //    INNER JOIN App\Entity\Product p WITH o.product = p.id
                    //    GROUP BY p.id
                    //    ORDER BY nb DESC')

                    // OR
                    // $conn = $context->getCollection()->getNativeDriver()->getConnection();
                    // $sql = '
                    //      SELECT p.id as product_id, count(o.id) as nb
                    //      FROM Order o
                    //      INNER JOIN Product p ON o.product = p.id
                    //      GROUP BY p.id
                    //      ORDER BY nb DESC';
                    // $query = $conn->executeQuery($sql);
                    // then use $query->fetchAllAssociative();

                    $ids = array_reduce($query->getResult(), function ($result, $item) {
                        $result[] = $item['product_id'];

                        return $result;
                    }, []);

                    return [
                        'field'     => 'id',
                        'operator'  => 'In',
                        'value'     => $ids,
                    ];
                }
        );
    }
);
```

{% endtab %}
{% endtabs %}

### Querying with the Forest Admin Query Interface

Queries can be executed directly, by calling the methods exposed by `context.dataSource` and `context.collection`.

```php
use ForestAdmin\AgentPHP\DatasourceCustomizer\CollectionCustomizer;
use ForestAdmin\AgentPHP\DatasourceCustomizer\Context\CollectionCustomizationContext;
use ForestAdmin\AgentPHP\DatasourceToolkit\Components\Query\Aggregation;
use ForestAdmin\AgentPHP\DatasourceToolkit\Components\Query\Filters\Filter;
use ForestAdmin\AgentPHP\DatasourceToolkit\Components\Query\ConditionTree\Operators;

$forestAgent->customizeCollection(
    'Customer',
    function (CollectionCustomizer $builder) {
        $builder->addSegment(
            'mySegment',
            function (CollectionCustomizationContext $context) {
                $rows = $context->getDatasource()->getCollection('Order')->aggregate(
                    new Filter(),
                    new Aggregation(operation: 'Count', groups: [['field' => 'category_id']]),
                    10
                );

                return [
                	'field'    => 'id',
                	'operator' => Operators::IN,
                	'value'    => array_map(fn ($r) => $r['product_id'], $rows)
                ];
            }
        );
    }
);
```

<details>

<summary>Data Source Interface</summary>

```php
use ForestAdmin\AgentPHP\DatasourceToolkit\Components\Caller;
use ForestAdmin\AgentPHP\DatasourceToolkit\Components\Charts\Chart;
use Illuminate\Support\Collection as IlluminateCollection;

interface DatasourceContract
{
    /** Retrieve list of all collection within the data source */
    public function getCollections(): IlluminateCollection;

    /** Retrieve list of all charts within the data source */
    public function getCharts(): IlluminateCollection;

    /** Get collection by name */
    public function getCollection(string $name): CollectionContract;

    /** Add collection to the data source */
    public function addCollection(CollectionContract $collection): void;

    /** Render the chart given by name */
    public function renderChart(Caller $caller, string $name): Chart|array;
}
```

</details>

<details>

<summary>Collection Interface</summary>

Parameters are explained in depth on the following pages:

* [Fields and projections](https://docs.forestadmin.com/developer-guide-agents-php/data-sources/getting-started/queries/fields-projections)
* [Filters](https://docs.forestadmin.com/developer-guide-agents-php/data-sources/getting-started/queries/filters)
* [Aggregations](https://docs.forestadmin.com/developer-guide-agents-php/data-sources/getting-started/queries/aggregations)

```php
use ForestAdmin\AgentPHP\DatasourceToolkit\Components\Caller;
use ForestAdmin\AgentPHP\DatasourceToolkit\Components\Query\Aggregation;
use ForestAdmin\AgentPHP\DatasourceToolkit\Components\Query\Filters\Filter;
use ForestAdmin\AgentPHP\DatasourceToolkit\Components\Query\Filters\PaginatedFilter;
use ForestAdmin\AgentPHP\DatasourceToolkit\Components\Query\Projection\Projection;

interface CollectionContract
{
    public function getDataSource(): DatasourceContract;

    public function getName(): string;

    /** Execute the action given by name  */
    public function execute(Caller $caller, string $name, array $formValues, ?Filter $filter = null);

    /** Get the form of the action given by name */
    public function getForm(Caller $caller, string $name, ?array $formValues = null, ?Filter $filter = null): array;

    /** Create new records */
    public function create(Caller $caller, array $data);

    /** List records matching filter */
    public function list(Caller $caller, PaginatedFilter $filter, Projection $projection): array;

    /** Update records matching filter */
    public function update(Caller $caller, Filter $filter, array $patch);

    /** Delete records matching filter */
    public function delete(Caller $caller, Filter $filter): void;

    /** Compute aggregated version of records matching filter */
    public function aggregate(Caller $caller, Filter $filter, Aggregation $aggregation, ?int $limit = null, ?string $chartType = null);

    /** Render chart for a given record */
    public function renderChart(Caller $caller, string $name, array $recordId);
}

```

</details>


---

# 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://docs.forestadmin.com/developer-guide-agents-php/data-sources/getting-started/queries.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.