PyroCMS › [table-builder] - In $filters is it possible to define text field type for relationship field

[table-builder] - In $filters is it possible to define text field type for relationship field

Created 7 years ago by lckamal

I would like to search from relationship field in table filters. Is this supported by default? eg. I have a profiles stream which references to users. so I would like to search from users display_name field.

piterden — 7 years ago Best Answer

        'user'   => [
            'filter'      => 'search',
            'query'       => RelationFilterQuery::class,
            'fields'      => [
                'username',
                'email',
                'display_name',
                'first_name',
                'last_name',
            ],
            'config' => [
                'placeholder' => 'Search by user data...',
                'prefix'      => 'user_',
            ]
        ],

Link

piterden — 7 years ago

<?php namespace Defr\DefaultModule\Project\Table\Filter;

use Anomaly\Streams\Platform\Ui\Table\Component\Filter\Contract\FilterInterface;
use Anomaly\Streams\Platform\Ui\Table\TableBuilder;
use Illuminate\Contracts\Container\Container;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Relations\Relation;

/**
 * Class RelationFilterQuery
 */
class RelationFilterQuery
{

    /**
     * IoC app
     *
     * @var Container
     */
    protected $container;

    /**
     * Create an instance of RelationFilterQuery class
     *
     * @param Container $container The container
     */
    public function __construct(Container $container)
    {
        $this->container = $container;
    }

    /**
     * Handle the filter.
     *
     * @param Builder         $query
     * @param FilterInterface $filter
     */
    public function handle(
        Builder $query,
        TableBuilder $builder,
        FilterInterface $filter
    )
    {
        $model    = $builder->getTableModel();
        $relation = $model->{$filter->getSlug()}();

        if (!($relation instanceof Relation))
        {
            return;
        }

        $relationTableName = $relation->getRelated()->getTableName();

        $fields = $filter->getFields();

        if (!in_array('id', $fields))
        {
            $fields[] = 'id';
        }

        $columns = $query->getQuery()->columns;

        foreach ($fields as $key)
        {
            $field     = $relationTableName.'_'.$key;
            $columns[] = $relationTableName.'.'.$key.' AS '.$field;
        }

        $query->select($columns);

        $query->leftJoin(
            $relationTableName,
            $relationTableName.'.id',
            '=',
            $model->getTableName().'.'.$relation->getForeignKey()
        );

        foreach ($fields as $key)
        {
            $field = $relationTableName.'.'.$key;

            $query->orWhere($field, 'LIKE', "%{$filter->getValue()}%");
        }
    }
}

Link

ryanthompson — 7 years ago

Similar to what @piterden said but the query would go in a text type filter which is generic. You'd define everything about it and the query class would probably join or something. But yes you can.

That might be a good thing to add to core actually. Make your own for the time being but if you would please add an issue to make this. Ill get it knocked out within a week or two.

Link