I have entities with binded comments. A comment can be important depending on a flag.
So, I write a custom dql to get a column indicating if the entity contains at last one important comment (could be a BooleanColumn):
$this->columnBuilder
->add('has_important_comment', Column::class, array(
'title' => 'Has important comment',
'dql' => '(SELECT (CASE WHEN COUNT({c}) > 0 THEN 1 ELSE 0 END) FROM MyBundle:Comment {c} WHERE {c}.entity = entity AND {c}.important = true)',
'searchable' => true,
));
I add a select filter on this column:
$this->columnBuilder
->add('has_important_comment', Column::class, array(
'title' => 'Has important comment',
'dql' => '(SELECT (CASE WHEN COUNT({c}) > 0 THEN 1 ELSE 0 END) FROM MyBundle:Comment {c} WHERE {c}.entity = entity AND {c}.important = true)',
'searchable' => true,
'filter' => [
SelectFilter::class,
[
'search_type' => 'eq',
'select_options' => ['' => 'Any', '1' => 'Yes', '0' => 'No'],
],
],
));
It works as expected. I can filter all entities with at last one important comment or all entities with none important comment.
But with global search and filter, both activated, I've got a bug as the dql is used twice in condition.
{c} is replaced by c_search in the LIKE condition for the global search and again by c_search in the eq/Select condition.