ufTable (sophisticated) filtering

How do I...
#1

I use a ufTable which shows some compound information in the cells.

As you can see in the screenshot below, the column Asset, while it is assigned to asset_id field, goes ahead and also provides the asset_name. Similar for the Start and End columns, the month initials are shown for better readability.

uftable-filter-sorting

This extra info is, sadly, not filterable yet. e.g. The Asset column strictly filters on the asset_id, and ignores the `asset_name’.

Is this even possible?

Thanks

#2

You’ll want to use a custom Sprunje filter here. The name of your ufTable column does not necessarily need to correspond to a database table column. You can define a custom field name instead (e.g. asset_info) and that will be submitted in the request instead:

GET http://localhost/userfrosting/public/api/items?size=5&page=0&filters[asset_info]=asc

By default, Sprunje will try to filter on a database column with the same name, which would cause an error because there is no asset_info column in the base query table. So, we define a custom filter in our Sprunje which maps this new property identifier to a custom Eloquent clause:

    /**
     * Filter LIKE the asset_id or asset_name
     *
     * @param Builder $query
     * @param mixed $value
     * @return $this
     */
    protected function filterAssetInfo($query, $value)
    {
        $query->like('asset_id', $value)
              ->orLike('asset_name', $value);
        return $this;
    }

Don’t forget to add asset_info to your $filterable whitelist. Sprunje will automatically take the asset_info query parameter that gets sent from your ufTable, and map it to this filterAssetInfo method.

1 Like
#3

Thanks. What if the asset_name is in a different table? Say. table assets, field name.

Also, is it possible to do it without hardcoding the table name directly, thus introducing tight coupling?

Thanks

#4

If you’ve already defined the relationship between the main entity and the related assets table, you can use Eloquent’s whereHas clause. You’ll use whereHas on the name of the relationship (not the table), and then pass a closure for the second argument that does the filtering:

    /**
     * Filter LIKE the asset_id or asset_name
     *
     * @param Builder $query
     * @param mixed $value
     * @return $this
     */
    protected function filterAssetInfo($query, $value)
    {

        // Allow searching for entities which DON'T have any associated assets
        if ((string) $value === '0') {
            $query->doesntHave('assets');
            return $this;
        }

        // Assume `assets` is a relation on the `assets` table, which has `id` and `name` columns.
        $query->whereHas('assets', function ($subQuery) use ($value) {
            $subQuery->like('id', $value)
                     ->orLike('name', $value);
        });
        return $this;
    }

Alternatively, if you’re using select and leftJoin in your base query, you could probably just filter directly on those columns since they’re already being retrieved.

1 Like