Filtering by calculated fields

[jakagacic]

Hi,

we have been using your bundle for a while and it's great, but today I came across a problem of filtering by calculated field. I would like to filter the list of my Offer entities by dynamic field tags. Tags are detected by some complex logic and not stored to database, but added on the fly. I found a way to do the filtering by extending the Paknahad\JsonApiBundle\Helper\ResourceCollection, but the bundle does not allow me to add ?filter[tag]=tagName to request because it is not an actual field in the table. The error message I am getting is:

"message": "No entity found for entity App\Entity\Offer and field tags",

Is there a way to achieve custom filters?

Thanks

[paknahad]

Hi,

Actually, at this moment there is no way. what do you think about adding some parameters to achieve this? something like this:

# config/services.yaml
parameters:
    jsonapi-bundle.filtering.custom-fields:
        App/Entity/EntityName:
           tags:
               type: string

and return these fields before this: https://github.com/paknahad/jsonapi-bundle/blob/34ecf127a2f38e9aad4a0ecaea03bf2462e751a3/src/Helper/FieldManager.php#L240-L243

[jakagacic]

Thank you for your answer!

I tries the solution you suggested and it did get me pass this error, but the code now breaks inside doctrine:

"message": "[Semantical Error] line 0, col 185 near 'tag': Error: Class App\Entity\Offer has no field or association named tag",

I am aware this is because it does not know how to query the calculated value, but I am not sure what would be the correct way to write the queries for custom filtering. So far, I extended the Paknahad\JsonApiBundle\Helper\ResourceCollection and override the generateQuery method to return the correct query based on the requested tag. It does work, but it does not seem clean.

Do you have a better idea?

[paknahad]

I've developed a new feature for filtering by subQueries. by this feature, you would be able to add subQueries to filtering. please check out the "filtering_supports_subquery" branch and let me know it can be useful for you?

for instance: if you had an authors table and 2 fields "firstName" and "lastName", and you want to have a filter by "fullName", then you had to define a new method in AuthorsRepository like this:

    public function filterByFullName()
    {
        $qb = $this->createQueryBuilder('af');

        $qb
            ->select('CONCAT(af.fistName, af.lastName)')
            ->where('af.id = r.id');

        return [
            SubQueryManager::TYPE => 'string',
            SubQueryManager::DQL => $qb->getDQL(),
        ];
    }

then:

/authors?filter[fullName]=any name

@mnugter could you please check it out?

[mnugter]

I read through the branch and it seems like a cool feature! Code seems good and it's a clean addition (not breaking any current functionalities).

I'm not 100% sure yet on the location of the filterBy method. This would pollute the EntityRepository with functions that are not directly useable on the repository (you cannot call $repository->filterByFullname() as it does not give a relevant return value).

Could this have a place in the transformers? They do handle everything related to a request and you have them for each endpoint / entity. Otherwise you could introduce a filterTransformer class?

[akalineskou]

For anyone else trying to find a solution, this is the one I used.

In my entity I have the field date, and I want to filter from-to date (&filter[dateFrom]=2019-09-26&filter[dateTo]=2019-09-27)

I've overriden the finder serivce config\services.yaml

paknahad_json_api.helper_filter.finder:
    class: App\JsonApi\Helper\Finder
    tags: ['paknahad.json_api.finder']

App\JsonApi\Helper\Finder.php

<?php
declare(strict_types=1);

namespace App\JsonApi\Helper;

use Paknahad\JsonApiBundle\Helper\Filter\Finder as BaseFinder;

class Finder extends BaseFinder
{
    /**
     * @inheritDoc
     */
    protected function setCondition(string $field, string $value): void
    {
        if (!in_array($field, ['dateFrom', 'dateTo'], true)) {
            parent::setCondition($field, $value);
            return;
        }

        $this->fieldManager->addField('date');

        $this->query->andWhere(sprintf(
            '%s %s= :%s',
            $this->fieldManager->getQueryFieldName('date'),
            $field === 'dateFrom' ? '>' : '<',
            $field
        ));
        $this->query->setParameter($field, $value);
    }
}

Please note that you have to override the existing finder service, else if you just tag your custom finder (as per the documentation) it wont work, as they both will add the fields (the original finder will add dateFrom, dateTo which dont exist on the entity)