Using TwigStringColumn seems to break search query

[cupracer]

Hi, I just noticed that my table search stops using LIKE '%sometext%' and starts using a strict = 'sometext' in queries as soon as I add a TwigStringColumn to a table.

Working example excerpt:

$table = $dataTableFactory->create()
    ->add('name', TextColumn::class)
    ->createAdapter(ORMAdapter::class, [
        'entity' => Brand::class,
    ])
    ->handleRequest($request);

Results in:

SELECT s0_.id AS id_0, s0_.name AS name_1 FROM supplies_brand s0_ WHERE s0_.name LIKE '%TEST%' LIMIT 10;

Switching from TextColumn to TwigStringColumn:

$table = $dataTableFactory->create()
    ->add('name', TwigStringColumn::class, [
        'template' => sprintf(
            '<a href="%s">{{ value }}</a>',
            '{{ url(\'app_supplies_brand_show\', {id: row.id}) }}'
        )
    ])
    ->createAdapter(ORMAdapter::class, [
        'entity' => Brand::class,
    ])
    ->handleRequest($request);

Results in:

SELECT s0_.id AS id_0, s0_.name AS name_1 FROM supplies_brand s0_ WHERE s0_.name = 'TEST' LIMIT 10;

This example was shortened to a single field, but the same type of query is generated for all searchable fields.

Background: My current task is to show a column with the object's name as a hyperlink which uses its ID. I don't understand why TwigStringColumn modifies the SQL query. Is this a bug or expected behaviour? Thanks.

[cupracer]

I just learned that it's possible to access other columns/properties with TextColumn as well when using the second parameter of a render function:

->add('name', TextColumn::class, [
    'label' => 'label.name',
    'render' => function($value, Brand $brand) {
        return sprintf(
            '<a href="%s">%s</a>',
            $this->generateUrl('app_supplies_brand_show', ['id' => $brand->getId()]),
            $value
        );
    },
])

This works like a charm for me a does not break the search query. I am already satisfied with that. If the original description is not a bug, this issue can also be closed.

For the sake of completeness, I'd like to mention that I only learned how to use the second parameter of the render function through trial-and-error, since I couldn't find that in the documentation. It is mentioned a single time as $context, but I did not associate this with the currently used object. Unless I was just too blind, it would certainly be a good idea to add the info.

[jarodxxx]

Hi, I got the same issue (id we can call that an issue ^^)

The TwigStringColumn does not apply the LIKE but Equal search.

Is there an opt to choose the search mode ?

[cupracer]

Is there an opt to choose the search mode ?

I ended up using my solution from https://github.com/omines/datatables-bundle/issues/290#issuecomment-1427484088, so I did not test the following and I'm not sure if this approach would work at all, but at least it seems that the TextColumn class sets a LIKE operator here:

https://github.com/omines/datatables-bundle/blob/2174bebee71eabaccb849ab43308c2afeea447df/src/Column/TextColumn.php#L37-L48

This method is not directly reachable, but the AbstractColumn class defines this public method: https://github.com/omines/datatables-bundle/blob/2174bebee71eabaccb849ab43308c2afeea447df/src/Column/AbstractColumn.php#L42-L54

It might be worth testing to put the operator = 'LIKE' property into an $options array and to call initialize() on a new TwigStringColumn object.

[curry684]

The Twig columns do not use LIKE by default because, unlike the TextColumn, they are not inherently expected to be used to just show the field value but greatly enrich it with additional content or mutations. In general, LIKE would have more unexpected behavior in most use cases. You can just override the operator default for a single column.

Having said that, we did document on purpose that, for most cases, rendering a ton of Twig templates per row for many rows can quickly become sluggish at least, and far better performance is achieved for most simple cases, like creating hyperlinks, by hooking into the render of a TextColumn.

So yes, the solution from https://github.com/omines/datatables-bundle/issues/290#issuecomment-1427484088 is what we actually use ourselves. If you inject the RouterInterface into your table type you can make it a (long) oneliner even:

'render' => fn ($value, Entity $entity) => sprintf('<a href="%s">%s</a>', $this->router->generate('entity.show', [
    'id' => $entity->getId(),
]), $value),

Additionally you could even implement a HyperLinkColumn yourself by adding an option that takes a callable to create a URL to wrap around the raw value. You could then do something as concise as:

->add('name', HyperLinkColumn::class, [
  'generateUrl' => fn(Entity $entity) => $this->router->generate('entity.show', ['id' => $entity->getId()]),
])

(this may actually be a very good idea to add to the bundle as it's perfectly generic)

[github-actions[bot]]

Stale issue message