[Bug] Migrating from 5.3.x to 5.4.2 breaks count of list table

[Mirko-Tebaldi]

Bug report

What I did

I just upgraded to 5.4.2 from 5.3.12

What I expected to happen

Nothing else than good things

What happened

In a list I got this error that was not happening previously

Illuminate\Database\QueryException:
SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in field list is ambiguous (SQL: select count(*) as total_rows from (select `id` from `joborders` left join `customers` on `joborders`.`customer_id` = `customers`.`id`) as `joborders`)

  at C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Database\Connection.php:759
  at Illuminate\Database\Connection->runQueryCallback('select count(*) as total_rows from (select `id` from `joborders` left join `customers` on `joborders`.`customer_id` = `customers`.`id`) as `joborders`', array(), object(Closure))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Database\Connection.php:719)
  at Illuminate\Database\Connection->run('select count(*) as total_rows from (select `id` from `joborders` left join `customers` on `joborders`.`customer_id` = `customers`.`id`) as `joborders`', array(), object(Closure))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Database\Connection.php:455)
  at Illuminate\Database\Connection->cursor('select count(*) as total_rows from (select `id` from `joborders` left join `customers` on `joborders`.`customer_id` = `customers`.`id`) as `joborders`', array(), true)
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Database\Query\Builder.php:2815)
  at Illuminate\Database\Query\Builder->Illuminate\Database\Query\{closure}()
  at Generator->valid()
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Collections\LazyCollection.php:435)
  at Illuminate\Support\LazyCollection->first()
     (C:\laragon\www\project_name\vendor\backpack\crud\src\app\Library\CrudPanel\Traits\Query.php:328)
  at Backpack\CRUD\app\Library\CrudPanel\CrudPanel->getCountFromQuery(object(Builder))
     (C:\laragon\www\project_name\vendor\backpack\crud\src\app\Library\CrudPanel\Traits\Query.php:271)
  at Backpack\CRUD\app\Library\CrudPanel\CrudPanel->getQueryCount()
     (C:\laragon\www\project_name\vendor\backpack\crud\src\app\Library\CrudPanel\Traits\Query.php:284)
  at Backpack\CRUD\app\Library\CrudPanel\CrudPanel->getFilteredQueryCount()
     (C:\laragon\www\project_name\vendor\backpack\crud\src\app\Http\Controllers\Operations\ListOperation.php:101)
  at App\Http\Controllers\Admin\JoborderCrudController->search()
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Routing\Controller.php:54)
  at Illuminate\Routing\Controller->callAction('search', array())
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Routing\ControllerDispatcher.php:43)
  at Illuminate\Routing\ControllerDispatcher->dispatch(object(Route), object(JoborderCrudController), 'search')
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Routing\Route.php:260)
  at Illuminate\Routing\Route->runController()
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Routing\Route.php:205)
  at Illuminate\Routing\Route->run()
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Routing\Router.php:725)
  at Illuminate\Routing\Router->Illuminate\Routing\{closure}(object(Request))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Pipeline\Pipeline.php:141)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(object(Request))
     (C:\laragon\www\project_name\vendor\backpack\crud\src\app\Http\Controllers\CrudController.php:44)
  at Backpack\CRUD\app\Http\Controllers\CrudController->Backpack\CRUD\app\Http\Controllers\{closure}(object(Request), object(Closure))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Pipeline\Pipeline.php:162)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(object(Request))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Foundation\Http\Middleware\TransformsRequest.php:21)
  at Illuminate\Foundation\Http\Middleware\TransformsRequest->handle(object(Request), object(Closure))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Foundation\Http\Middleware\ConvertEmptyStringsToNull.php:31)
  at Illuminate\Foundation\Http\Middleware\ConvertEmptyStringsToNull->handle(object(Request), object(Closure))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Pipeline\Pipeline.php:180)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(object(Request))
     (C:\laragon\www\project_name\app\Http\Middleware\CheckIfAdmin.php:66)
  at App\Http\Middleware\CheckIfAdmin->handle(object(Request), object(Closure))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Pipeline\Pipeline.php:180)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(object(Request))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Routing\Middleware\SubstituteBindings.php:50)
  at Illuminate\Routing\Middleware\SubstituteBindings->handle(object(Request), object(Closure))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Pipeline\Pipeline.php:180)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(object(Request))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Foundation\Http\Middleware\VerifyCsrfToken.php:78)
  at Illuminate\Foundation\Http\Middleware\VerifyCsrfToken->handle(object(Request), object(Closure))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Pipeline\Pipeline.php:180)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(object(Request))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\View\Middleware\ShareErrorsFromSession.php:49)
  at Illuminate\View\Middleware\ShareErrorsFromSession->handle(object(Request), object(Closure))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Pipeline\Pipeline.php:180)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(object(Request))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Session\Middleware\StartSession.php:121)
  at Illuminate\Session\Middleware\StartSession->handleStatefulRequest(object(Request), object(Store), object(Closure))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Session\Middleware\StartSession.php:64)
  at Illuminate\Session\Middleware\StartSession->handle(object(Request), object(Closure))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Pipeline\Pipeline.php:180)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(object(Request))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Cookie\Middleware\AddQueuedCookiesToResponse.php:37)
  at Illuminate\Cookie\Middleware\AddQueuedCookiesToResponse->handle(object(Request), object(Closure))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Pipeline\Pipeline.php:180)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(object(Request))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Cookie\Middleware\EncryptCookies.php:67)
  at Illuminate\Cookie\Middleware\EncryptCookies->handle(object(Request), object(Closure))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Pipeline\Pipeline.php:180)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(object(Request))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Pipeline\Pipeline.php:116)
  at Illuminate\Pipeline\Pipeline->then(object(Closure))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Routing\Router.php:726)
  at Illuminate\Routing\Router->runRouteWithinStack(object(Route), object(Request))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Routing\Router.php:703)
  at Illuminate\Routing\Router->runRoute(object(Request), object(Route))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Routing\Router.php:667)
  at Illuminate\Routing\Router->dispatchToRoute(object(Request))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Routing\Router.php:656)
  at Illuminate\Routing\Router->dispatch(object(Request))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Foundation\Http\Kernel.php:190)
  at Illuminate\Foundation\Http\Kernel->Illuminate\Foundation\Http\{closure}(object(Request))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Pipeline\Pipeline.php:141)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(object(Request))
     (C:\laragon\www\project_name\vendor\livewire\livewire\src\DisableBrowserCache.php:19)
  at Livewire\DisableBrowserCache->handle(object(Request), object(Closure))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Pipeline\Pipeline.php:180)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(object(Request))
     (C:\laragon\www\project_name\vendor\barryvdh\laravel-debugbar\src\Middleware\InjectDebugbar.php:66)
  at Barryvdh\Debugbar\Middleware\InjectDebugbar->handle(object(Request), object(Closure))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Pipeline\Pipeline.php:180)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(object(Request))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Foundation\Http\Middleware\TransformsRequest.php:21)
  at Illuminate\Foundation\Http\Middleware\TransformsRequest->handle(object(Request), object(Closure))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Foundation\Http\Middleware\ConvertEmptyStringsToNull.php:31)
  at Illuminate\Foundation\Http\Middleware\ConvertEmptyStringsToNull->handle(object(Request), object(Closure))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Pipeline\Pipeline.php:180)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(object(Request))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Foundation\Http\Middleware\TransformsRequest.php:21)
  at Illuminate\Foundation\Http\Middleware\TransformsRequest->handle(object(Request), object(Closure))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Foundation\Http\Middleware\TrimStrings.php:40)
  at Illuminate\Foundation\Http\Middleware\TrimStrings->handle(object(Request), object(Closure))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Pipeline\Pipeline.php:180)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(object(Request))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Foundation\Http\Middleware\ValidatePostSize.php:27)
  at Illuminate\Foundation\Http\Middleware\ValidatePostSize->handle(object(Request), object(Closure))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Pipeline\Pipeline.php:180)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(object(Request))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Foundation\Http\Middleware\PreventRequestsDuringMaintenance.php:86)
  at Illuminate\Foundation\Http\Middleware\PreventRequestsDuringMaintenance->handle(object(Request), object(Closure))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Pipeline\Pipeline.php:180)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(object(Request))
     (C:\laragon\www\project_name\vendor\fruitcake\laravel-cors\src\HandleCors.php:38)
  at Fruitcake\Cors\HandleCors->handle(object(Request), object(Closure))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Pipeline\Pipeline.php:180)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(object(Request))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Http\Middleware\TrustProxies.php:39)
  at Illuminate\Http\Middleware\TrustProxies->handle(object(Request), object(Closure))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Pipeline\Pipeline.php:180)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(object(Request))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Pipeline\Pipeline.php:116)
  at Illuminate\Pipeline\Pipeline->then(object(Closure))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Foundation\Http\Kernel.php:165)
  at Illuminate\Foundation\Http\Kernel->sendRequestThroughRouter(object(Request))
     (C:\laragon\www\project_name\vendor\laravel\framework\src\Illuminate\Foundation\Http\Kernel.php:134)
  at Illuminate\Foundation\Http\Kernel->handle(object(Request))
     (C:\laragon\www\project_name\public\index.php:52)                

What I've already tried to fix it

I am trying to rollback a minor at time to understand when and what the upgrade broke my code

Is it a bug in the latest version of Backpack?

After I run composer update backpack/crud the bug... is it still there?

Yes

Backpack, Laravel, PHP, DB version

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

### PHP VERSION:
PHP 8.1.2 (cli) (built: Jan 19 2022 10:13:52) (NTS Visual C++ 2019 x64)
Copyright (c) The PHP Group
Zend Engine v4.1.2, Copyright (c) Zend Technologies

### LARAVEL VERSION:
v9.35.1@79aed20f54b6ab75f926bf7d0dd7a5043ceb774a

### BACKPACK VERSION:
5.4.2@da63612d0c9a674c79bd50f31d76ae9ada7eaac8

[welcome[bot]]

Hello there! Thanks for opening your first issue on this repo!

Just a heads-up: Here at Backpack we use Github Issues only for tracking bugs. Talk about new features is also acceptable. This helps a lot in keeping our focus on improving Backpack. If you issue is not a bug/feature, please help us out by closing the issue yourself and posting in the appropriate medium (see below). If you're not sure where it fits, it's ok, a community member will probably reply to help you with that.

Backpack communication channels:

• Bug Reports, Feature Requests - Github Issues (here);
• Quick help (How do I do X) - Gitter Chatroom;
• Long questions (I have done X and Y and it won't do Z wtf) - Stackoverflow, using the backpack-for-laravel tag;
• Showing off something you've made, asking for opinion on Backpack/Laravel matters - Reddit;

Please keep in mind Backpack offers no official / paid support. Whatever help you receive here, on Gitter, Slack or Stackoverflow is thanks to our awesome awesome community members, who give up some of their time to help their peers. If you want to join our community, just start pitching in. We take pride in being a welcoming bunch.

Thank you!

-- Justin Case The Backpack Robot

[realtebo]

Downgrade to "backpack/crud": "5.4.1",

• Downgrading backpack/crud (5.4.2 => 5.4.1)
• Upgrading league/flysystem (3.7.0 => 3.8.0)

Failed. Same issue

Downgrade to "backpack/crud": "5.4.0",

• Downgrading backpack/crud (5.4.1 => 5.4.0)

Failed. Same Issue

Downgrade to "backpack/crud": "5.3.13",

• Downgrading backpack/crud (5.4.0 => 5.3.13)
  • Downgrading backpack/pro (1.3.0 => 1.2.8)

Issue not present.

So 5.4.0 create some kind of regression.

[realtebo]

I suggest investigating this function, but I'm not so sure

https://github.com/Laravel-Backpack/CRUD/compare/5.3.13...5.4.0#diff-7b3eda922f455b17224d490ff2fb7ea813665afbc56acdcb6b0af29e70e8f9efR293

Our specific use case is particular: our customer wants table 'joborders' ordered by customer name, that is an attribute in a related table

So we added this to our Controller

    $this->crud->addColumn([
        'name' => 'company_name',
        'entity' => 'customer',
        'attribute' => 'company_name',
        'label' => 'Cliente',
        'type' => 'relationship',
        'orderable'  => true,
        'orderLogic' => function ($query, $column, $columnDirection) {
            return $query
                ->leftJoin('customers', 'joborders.customer_id', '=', 'customers.id')
                ->orderBy('customers.company_name', $columnDirection)
                ->select('joborders.*')
                ;
        }
    ]);

[realtebo]

I also tried

$this->crud->model->setKeyName("joborders.id");

But it generates a query with doubled table name, damn ...

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'joborders.joborders.id' in 'order clause' (SQL: select `joborders`.*, `customers`.`company_name` from `joborders` left join `customers` on `joborders`.`customer_id` = `customers`.`id` order by `customers`.`company_name` asc, joborders.joborders.id DESC limit 10)

[realtebo]

Fix suggested:

File: vendor/backpack/crud/src/app/Library/CrudPanel/Traits/Query.php

Change ROW 326 from

$outerQuery = $outerQuery->fromSub($subQuery->select($this->model->getKeyName()), $this->model->getTableWithPrefix());

to

$outerQuery = $outerQuery->fromSub($subQuery->select($this->model->getTableWithPrefix() . "." . $this->model->getKeyName()), $this->model->getTableWithPrefix());

I have NO idea of collateral effects of this.

[pxpm]

No side effects that's the perfect solution, I should had implemented it that way, I am working on it and will submit the PR in a few moments.

Thanks and sorry for the bad experience @realtebo 🙏

[Mirko-Tebaldi]

thanks for very fast fixing.

Can I suggest to add a test, in your suite, to handle situation where a list operation is populated and sorted with something from a related field?

[tabacitu]

Can I suggest to add a test, in your suite, to handle situation where a list operation is populated and sorted with something from a related field?

I second that 😀

[tabacitu]

PS. Pedro's submitted the fix and it's merged and tagged, so a composer update should fix it for everybody.