Can't search a TextColumn by the value of the data option

[ra2410]

Hi, I use the data option of a TextColumn to append the first name to the value of the last name. This is my code:

$table = $this->createDataTable()
    ->add('user',
        TextColumn::class,
        [
            'label' => 'User',
            'data' => function ($value) {
                $lastName = $value->getEmployee()->getLastName();
                $firstName = $value->getEmployee()->getFirstName();
                $user = $lastName . ', ' . mb_strtoupper(mb_substr($firstName, 0, 1));
                return $user;
            },
            'field' => 'e.lastName',
            'globalSearchable' => true,
            'searchable' => true,
            'orderable' => true,
        ]
    )
    ->createAdapter(ORMAdapter::class, [
        'entity' => message::class,
        'query' => function (QueryBuilder $builder) {
            $builder
                ->distinct()
                ->select('m')
                ->addSelect('e')
                ->from(message::class, 'm')
                ->leftJoin('m.employee', 'e');
        },
        'criteria' => [
            new SearchCriteriaProvider(),
        ],
    ]);

However, the search always uses the value of the field option (e.g 'foo') and not the value of the data option (e.g. 'foo, b'.). Without the field option, I get an error when sorting the user column.

How is it possible to include the value of the data option in the search?

[curry684]

As you're only adapting the data formatting, and filtering happens before that in the ORM level, it wouldn't make sense if you could implement this - it's the wrong way around (and would have terrible performance if we were to always implement fetching an entire table and then filter based on how you display it).

So the correct solution is simply to add your own criteria provider. It allows you to flexibly convert the search criteria into querybuilder conditions, thus allowing you to filter both by lastname and firstname, or more complex combined scenarios.

If you look at how the stock SearchCriteriaProvider is implemented your use case should be pretty easy to build.

[ra2410]

Thank you for your answer, but I still have problems.

If I concat firstname and lastname on ORM level like this

 $table = $this->createDataTable()
->add('fullname',
    TextColumn::class,
    [
        'label' => 'fullname',
        'field' => 'fullname',
        'globalSearchable' => true,
        'searchable' => true,
        'orderable' => true,
    ]
)
->createAdapter(ORMAdapter::class, [
    'entity' => message::class,
    'query' => function (QueryBuilder $builder) {
        $builder
            ->distinct()
            ->select('CONCAT(e.firstname, \' \', e.lastname) AS fullname')
            ->from(message::class, 'm')
            ->leftJoin('m.employee', 'e');
    },
    'criteria' => [
        new SearchCriteriaProvider(),
    ],
]);

I get the error message "Field name 'fullname' must consist at least of an alias and a field separated with a period."

If I omit the field option, I get the error message "Cannot read property "id" from an array. Maybe you intended to write the property path as "[id]" instead. "

So I try to use hydrate as follow:

$table = $this->createDataTable()
->add('fullname',
    TextColumn::class,
    [
        'label' => 'fullname',
        'propertyPath' => ['fullname'],
        'globalSearchable' => true,
        'searchable' => true,
        'orderable' => true,
    ]
)
->createAdapter(ORMAdapter::class, [
    'entity' => message::class,
        'hydrate' => Query::HYDRATE_ARRAY,
    'query' => function (QueryBuilder $builder) {
        $builder
            ->select('CONCAT(e.firstname, \' \', e.lastname) AS fullname')
            ->from(message::class, 'm')
            ->leftJoin('m.employee', 'e');
    },
    'criteria' => [
        new SearchCriteriaProvider(),
    ],
]);

I get the right values for fullname, but searching and sorting for fullname doesn't work. I get the following error message:" [Syntax Error] line 0, col -1: Error: Expected Doctrine\ORM\Query\Lexer::T_IDENTIFIER, got end of string".

I don't know, what I'm doing wrong.

[curry684]

I suggested to implement a criteria provider:

So the correct solution is simply to add your own criteria provider.

You are now overriding the query builder thus causing all the other fields like id not to be fetched correctly. You do not need to select a field to filter on it.

[ra2410]

Sorry for the renewed question. I think I still do not understand the Query Builder.

Why filter?

I would like to output all messages with the corresponding fullnames of the employees. In the database I have two columns, one column 'firstname' and one column 'lastname'. In the datatatable, I have only one column 'fullname', in which I want to concat the fields 'firstname' and 'lastname' to 'fullname'. That's why I think I have to write the concat function in the SELECT clause and not in the WHERE clause.

[curry684]

You are now replacing the default automatic query builder with your own, thus causing the error because you're not selecting all the other fields that are needed like the id.

I'm closing this issue as there is no problem in the bundle itself. For general assistance with programming please use Stack Overflow instead of Github issues.