[Bug] Not auto appended attributes not working with fetch

[WrdVdb]

Bug report

What I did

I added a relationship field using the fetch, but I wanted to show an attribute of the model not an actual field in the database. This does not work because the select blade is using pluck.

CRUD::field('rel_id')
            ->label('Rel field')
            ->type('relationship')
            ->entity('relTest')
            ->ajax(true)
            ->attribute('extra_attribute_on_the_model');

I could fix this using the $appends to add the 'extra_attribute_on_the_model' on the model, but this is a huge overhead because of the extra joins.

What I expected to happen

Be able to use attributes in the fetch on the field.

What happened

The field tries to pluck the attribute, but this does not exist.

What I've already tried to fix it

I modified the vendor/backpack/pro/src/Http/Controllers/Operations/FetchOperation.php with an extra config 'attributes':
I added the fetchGet function to remove redundancy (search vs no search)

<?php

namespace Backpack\Pro\Http\Controllers\Operations;

use Illuminate\Support\Facades\Route;
use Illuminate\Support\Str;

trait FetchOperation
{
    /**
     * Define which routes are needed for this operation.
     *
     * @param  string  $segment  Name of the current entity (singular). Used as first URL segment.
     * @param  string  $routeName  Prefix of the route name.
     * @param  string  $controller  Name of the current CrudController.
     */
    protected function setupFetchOperationRoutes($segment, $routeName, $controller)
    {
        // get all method names on the current model that start with "fetch" (ex: fetchCategory)
        // if a method that looks like that is present, it means we need to add the routes that fetch that entity
        preg_match_all('/(?<=^|;)fetch([^;]+?)(;|$)/', implode(';', get_class_methods($this)), $matches);

        if (count($matches[1])) {
            foreach ($matches[1] as $methodName) {
                Route::post($segment.'/fetch/'.Str::kebab($methodName), [
                    'as'        => $segment.'.fetch'.Str::studly($methodName),
                    'uses'      => $controller.'@fetch'.$methodName,
                    'operation' => 'FetchOperation',
                ]);
            }
        }
    }

    protected function setupFetchOperationDefaults() {
        $this->crud->setOperationSetting('searchOperator', config('backpack.operations.fetch.searchOperator', 'LIKE'));
    }

    /**
     * Gets items from database and returns to selects.
     *
     * @param  string|array  $arg
     * @return \Illuminate\Http\JsonResponse|Illuminate\Database\Eloquent\Collection|Illuminate\Pagination\LengthAwarePaginator
     */
    private function fetch($arg)
    {
        // get the actual words that were used to search for an item (the search term / search string)
        $search_string = request()->input('q') ?? false;

        // if the Class was passed as the sole argument, use that as the configured Model
        // otherwise assume the arguments are actually the configuration array
        $config = [];

        if (! is_array($arg)) {
            if (! class_exists($arg)) {
                return response()->json(['error' => 'Class: '.$arg.' does not exists'], 500);
            }
            $config['model'] = $arg;
        } else {
            $config = $arg;
        }

        $model_instance = new $config['model']();
        // set configuration defaults
        $config['paginate'] = isset($config['paginate']) ? $config['paginate'] : 10;
        $config['searchable_attributes'] = $config['searchable_attributes'] ?? $model_instance->identifiableAttribute();
        // if a closure that has been passed as "query", use the closure - otherwise use the model
        $config['query'] = isset($config['query']) && is_callable($config['query']) ? $config['query']($model_instance) : $model_instance; 


        // FetchOperation sends an empty query to retrieve the default entry for select when field is not nullable.
        // Also sends an empty query in case we want to load all entities to emulate non-ajax fields
        // when using InlineCreate.
        /*
        return $config['query']->get()->map(function($item) {
            return ['id' => $item->id, 'name' => $item->name, 'full_name' => $item->full_name];
        });
        */

        if ($search_string === false) {
            return $this->fetchGet($config);
        }

        $textColumnTypes = ['string', 'json_string', 'text', 'longText', 'json_array', 'json', 'varchar', 'char'];

        $searchOperator = $config['searchOperator'] ?? $this->crud->getOperationSetting('searchOperator');

        // if the query builder brings any where clause already defined by the user we must
        // ensure that the where prevails and we should only use our search as a complement to the query constraints.
        // e.g user want only the active products, so in fetch they would return something like:
        // .... 'query' => function($model) { return $model->where('active', 1); }
        // So it reads: SELECT ... WHERE active = 1 AND (XXX = x OR YYY = y) and not SELECT ... WHERE active = 1 AND XXX = x OR YYY = y;
        if (! empty($config['query']->getQuery()->wheres)) {
            $config['query'] = $config['query']->where(function ($query) use ($model_instance, $config, $search_string, $textColumnTypes, $searchOperator) {

                foreach ((array) $config['searchable_attributes'] as $k => $searchColumn) {
                    $operation = ($k == 0) ? 'where' : 'orWhere';
                    $columnType = $model_instance->getColumnType($searchColumn);

                    if (in_array($columnType, $textColumnTypes)) {
                        $tempQuery = $query->{$operation}($searchColumn, $searchOperator, '%'.$search_string.'%');
                    } else {
                        $tempQuery = $query->{$operation}($searchColumn, $search_string);
                    }
                }
                // If developer provide an empty searchable_attributes array it means they don't want us to search
                // in any specific column, or try to guess the column from model identifiableAttribute.
                // In that scenario we will not have any $tempQuery here, so we just return the query, is up to the developer
                // to do their own search.
                return $tempQuery ?? $query;
            });
        } else {
            foreach ((array) $config['searchable_attributes'] as $k => $searchColumn) {
                $operation = ($k == 0) ? 'where' : 'orWhere';
                $columnType = $model_instance->getColumnType($searchColumn);

                if (in_array($columnType, $textColumnTypes)) {
                    $config['query'] = $config['query']->{$operation}($searchColumn, $searchOperator, '%'.$search_string.'%');
                } else {
                    $config['query'] = $config['query']->{$operation}($searchColumn, $search_string);
                }
            }
        }
        
        // return the results with or without pagination
        return $this->fetchGet($config);
    }

    private function fetchGet($config){
         if($config['paginate'] !== false) {
            $get = $config['query']->simplePaginate($config['paginate']);
         }else{
            $get = $config['query']->get();
         }
         
         if(isset($config['attributes'])){
            if(!is_array($config['attributes'])){
                
                $config['attributes'] = [$config['attributes']];
            }
            $get = $get->map(function($item) use($config) {
                $attributes = [];
                $attributes['id'] = $item->{$item->getKeyName()};
                foreach($config['attributes'] as $attribute){
                    $attributes[$attribute] = $item->{$attribute};
                }
                return $attributes;
            });
         }

         return $get;
    }
}

If I add the 'extra_attribute_on_the_model' or multiple attributes ['extra_attribute_on_the_model','another_extra_attribute_on_the_model'] to the fetch config, this now works with mapping the extra attribute to the result collection.

Backpack, Laravel, PHP, DB version

When I run php artisan backpack:version the output is:

### PHP VERSION:
8.3.8

### PHP EXTENSIONS:
Core, date, libxml, openssl, pcre, zlib, filter, hash, json, pcntl, random, Reflection, SPL, session, standard, sodium, mysqlnd, PDO, xml, calendar, ctype, curl, dom, mbstring, FFI, fileinfo, ftp, gd, gettext, iconv, exif, mysqli, pdo_dblib, pdo_mysql, Phar, posix, readline, shmop, SimpleXML, sockets, sysvmsg, sysvsem, sysvshm, tidy, tokenizer, xmlreader, xmlwriter, xsl, zip, mailparse, pdo_sqlsrv, Zend OPcache

### LARAVEL VERSION:
11.21.0.0

### BACKPACK PACKAGE VERSIONS:
backpack/basset: 1.3.6
backpack/crud: 6.7.33
backpack/generators: v4.0.5
backpack/pro: 2.2.17
backpack/theme-tabler: 1.2.12

[WrdVdb]

Any thoughts about this one?
I like the fetch attribute instead of making api's for each ajax field. But not being able to use a not default appended attributes is a dealbreaker.

[pxpm]

Hey @WrdVdb sorry this one slipped through the cracks.

I've added the ability to define the 'append_attributes' to fetch configuration that should solve your issue. Docs for it here: Laravel-Backpack/docs#620

Please allow our private repository to fetch the updated version and you should be able to get 2.2.30 of backpack/pro that has that feature.

Cheers

[WrdVdb]

@pxpm Tested and everthing works perfectly! Thanks for the update.

[WrdVdb]

@pxpm After using this for a while, one thing would be very useful. We can now show the correct attribute, but we cannot search it as a whole.

For example (fictive example)

We have a product dropdown with a product with the unique code 'ABC-PROD01' where ABC is the code of the producttype and PROD01 is the productcode, the combination is unique. Everywhere the product should be shown as 'ABC-PROD01'. We can search PROD01, or even ABC when using a join in de query of the fetch. But we cannot find the product when searching for the complete code 'ABC-PROD01'.

I fixed this locally by adding

'searchable_attributes' => ['products.name', \Illuminate\Support\Facades\DB::raw("concat(producttypes.code,'-',products.code)")],

But then I get a error on $model_instance->getColumnType($searchColumn);

By checking if the $searchColumn is a string before this function, the problem is solved:

--- /dev/null
+++ ../src/Http/Controllers/Operations/FetchOperation.php
@@ -91,7 +91,11 @@
             $config['query'] = $config['query']->where(function ($query) use ($model_instance, $config, $search_string, $textColumnTypes, $searchOperator) {
                 foreach ((array) $config['searchable_attributes'] as $k => $searchColumn) {
                     $operation = ($k == 0) ? 'where' : 'orWhere';
-                    $columnType = $model_instance->getColumnType($searchColumn);
+                    if(is_string($searchColumn)){
+                        $columnType = $model_instance->getColumnType($searchColumn);
+                    } else {
+                        $columnType = 'string';
+                    }
 
                     if (in_array($columnType, $textColumnTypes)) {
                         $tempQuery = $query->{$operation}($searchColumn, $searchOperator, '%'.$search_string.'%');
@@ -109,7 +113,11 @@
         } else {
             foreach ((array) $config['searchable_attributes'] as $k => $searchColumn) {
                 $operation = ($k == 0) ? 'where' : 'orWhere';
-                $columnType = $model_instance->getColumnType($searchColumn);
+                if(is_string($searchColumn)){
+                    $columnType = $model_instance->getColumnType($searchColumn);
+                }else{
+                    $columnType = 'string';
+                }
 
                 if (in_array($columnType, $textColumnTypes)) {
                     $config['query'] = $config['query']->{$operation}($searchColumn, $searchOperator, '%'.$search_string.'%');

Or maybe add the check in the getColumnType function?

Is it possible to add this?

Thanks in advance!

[pxpm]

Hey @WrdVdb thanks for the suggestion.

I was about to do this change, but while testing my solution I think it's not needed as you can convert the raw expression in a string by yourself. Something like:

\Illuminate\Support\Facades\DB::raw("concat(producttypes.code,'-',products.code)")->getValue(\Illuminate\Support\Facades\DB::getQueryGrammar())

Alternatively, you can write the plain string ? $searchableAttributes= ['column1', 'concat(producttypes.code,'-',products.code)'] ?

Cheers

[WrdVdb]

Hey @pxpm , I tried this but this gives the following error:

Unknown column 'concat(producttypes.code,'-',products.code)' in 'where clause'

These columns in the array are not raw, but escaped?

[pxpm]

Thanks @WrdVdb

I've added the ability for you to use raw expressions 🙏 Hope everything is working as intended now 👍

Please allow a few moments for our private repository to pick the changes and you can get 2.2.32 of backpack/pro with the fix.

Cheers