search

Kyslik / column-sortable

Package for handling column sorting in Laravel 5/6/7/8
MIT License
644 stars 105 forks source link

Not unique table/alias Error #110

Closed MichaelHoughton closed 5 years ago

MichaelHoughton commented 5 years ago

I am trying to filter by a belongsTo column, but I am getting the following error when doing so:

Relation 'fund' is not instance of HasOne or BelongsTo.

The instance does exist. I investigated the issue further, and found that the problem is with the SQL that gets generated:

select count(*) as aggregate from `queries` left join `funds` on `queries`.`fund_id` = `funds`.`id` where `parent_id` is null and (`user_id` = 2 or `draft` = 0) and exists (select * from `users` inner join `query_user` on `users`.`id` = `query_user`.`user_id` where `queries`.`id` = `query_user`.`query_id` and `user_id` = 2) and `queries`.`deleted_at` is null

It appears that the left join for funds is getting mentioned twice in the SQL - which is causing the issue.

Kyslik commented 5 years ago

@MichaelHoughton, can you show the code of the controller (or a method where the issue happens)?

MichaelHoughton commented 5 years ago

Hey @Kyslik - thanks for your fast response.

Sure thing, here is the code:

$queries = Query::sortable(['created_at' => 'desc'])
            ->paginate(50);

In the model file Query.php, I have:

public function fund()
{
    return $this->belongsTo(Fund::class);
}

The URL then is: /queries?sort=fund.name&direction=asc

In Fund.php, I also have:

public $sortable = ['id', 'name'];
Kyslik commented 5 years ago

@MichaelHoughton the code really stops here could you investigate on your own, I mean open up that file in your /vendor and add some dd() statements the most useful would be to dd($e) and report back here whats the stack trace.

A side question what Laravel version do you use?

MichaelHoughton commented 5 years ago

Hey @Kyslik - using the latest version of Laravel (5.7.20)

Here is the stack trace:

Illuminate\Database\QueryException thrown with message "SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'funds' (SQL: select count(*) as aggregate from `queries` left join `funds` on `queries`.`fund_id` = `funds`.`id` left join `funds` on `queries`.`fund_id` = `funds`.`id` where `queries`.`deleted_at` is null)"

Stacktrace:
#72 Illuminate\Database\QueryException in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664
#71 Doctrine\DBAL\Driver\PDOException in /home/michael/public_html/fundview/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:63
#70 PDOException in /home/michael/public_html/fundview/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:61
#69 PDO:prepare in /home/michael/public_html/fundview/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:61
#68 Doctrine\DBAL\Driver\PDOConnection:prepare in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Database/Connection.php:326
#67 Illuminate\Database\Connection:Illuminate\Database\{closure} in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Database/Connection.php:657
#66 Illuminate\Database\Connection:runQueryCallback in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Database/Connection.php:624
#65 Illuminate\Database\Connection:run in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Database/Connection.php:333
#64 Illuminate\Database\Connection:select in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php:2082
#63 Illuminate\Database\Query\Builder:runSelect in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php:2070
#62 Illuminate\Database\Query\Builder:Illuminate\Database\Query\{closure} in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php:2556
#61 Illuminate\Database\Query\Builder:onceWithColumns in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php:2071
#60 Illuminate\Database\Query\Builder:get in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php:2169
#59 Illuminate\Database\Query\Builder:runPaginationCountQuery in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php:2140
#58 Illuminate\Database\Query\Builder:getCountForPagination in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php:745
#57 Illuminate\Database\Eloquent\Builder:paginate in /home/michael/public_html/fundview/app/Http/Controllers/QueriesController.php:47
#56 App\Http\Controllers\QueriesController:index in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Routing/Controller.php:54
#55 call_user_func_array in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Routing/Controller.php:54
#54 Illuminate\Routing\Controller:callAction in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php:45
#53 Illuminate\Routing\ControllerDispatcher:dispatch in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Routing/Route.php:212
#52 Illuminate\Routing\Route:runController in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Routing/Route.php:169
#51 Illuminate\Routing\Route:run in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Routing/Router.php:682
#50 Illuminate\Routing\Router:Illuminate\Routing\{closure} in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:30
#49 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /home/michael/public_html/fundview/app/Http/Middleware/CheckRole.php:27
#48 App\Http\Middleware\CheckRole:handle in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:151
#47 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#46 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /home/michael/public_html/fundview/app/Http/Middleware/AcceptedDisclaimer.php:32
#45 App\Http\Middleware\AcceptedDisclaimer:handle in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:151
#44 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#43 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Routing/Middleware/SubstituteBindings.php:41
#42 Illuminate\Routing\Middleware\SubstituteBindings:handle in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:151
#41 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#40 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/VerifyCsrfToken.php:75
#39 Illuminate\Foundation\Http\Middleware\VerifyCsrfToken:handle in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:151
#38 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#37 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/View/Middleware/ShareErrorsFromSession.php:49
#36 Illuminate\View\Middleware\ShareErrorsFromSession:handle in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:151
#35 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#34 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Session/Middleware/StartSession.php:63
#33 Illuminate\Session\Middleware\StartSession:handle in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:151
#32 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#31 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Cookie/Middleware/AddQueuedCookiesToResponse.php:37
#30 Illuminate\Cookie\Middleware\AddQueuedCookiesToResponse:handle in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:151
#29 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#28 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Cookie/Middleware/EncryptCookies.php:66
#27 Illuminate\Cookie\Middleware\EncryptCookies:handle in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:151
#26 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#25 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:104
#24 Illuminate\Pipeline\Pipeline:then in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Routing/Router.php:684
#23 Illuminate\Routing\Router:runRouteWithinStack in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Routing/Router.php:659
#22 Illuminate\Routing\Router:runRoute in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Routing/Router.php:625
#21 Illuminate\Routing\Router:dispatchToRoute in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Routing/Router.php:614
#20 Illuminate\Routing\Router:dispatch in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php:176
#19 Illuminate\Foundation\Http\Kernel:Illuminate\Foundation\Http\{closure} in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:30
#18 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /home/michael/public_html/fundview/vendor/fideloper/proxy/src/TrustProxies.php:57
#17 Fideloper\Proxy\TrustProxies:handle in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:151
#16 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#15 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php:31
#14 Illuminate\Foundation\Http\Middleware\TransformsRequest:handle in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:151
#13 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#12 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php:31
#11 Illuminate\Foundation\Http\Middleware\TransformsRequest:handle in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:151
#10 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#9 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/ValidatePostSize.php:27
#8 Illuminate\Foundation\Http\Middleware\ValidatePostSize:handle in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:151
#7 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#6 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/CheckForMaintenanceMode.php:62
#5 Illuminate\Foundation\Http\Middleware\CheckForMaintenanceMode:handle in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:151
#4 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#3 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:104
#2 Illuminate\Pipeline\Pipeline:then in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php:151
#1 Illuminate\Foundation\Http\Kernel:sendRequestThroughRouter in /home/michael/public_html/fundview/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php:116
#0 Illuminate\Foundation\Http\Kernel:handle in /home/michael/public_html/fundview/public/index.php:55

Downgrading to an older version of Laravel didn't seem to make a difference.

Thanks again for your help with this. Is there a test I could run which should work with a belongsTo relation? I could see if I could reproduce this in a test.

Kyslik commented 5 years ago

@MichaelHoughton this sure is weird, please do try:

There seems to be a test for this but I need to double-check haven't seen this package's code for a while.

Lastly can you create a dummy repo as minimal example so I can play with it? (Preferably with sqlite3 database included).

MichaelHoughton commented 5 years ago

@Kyslik - I am pleased to report that we can solve this.

I had been running the 5.5 version of column-sortable on my Laravel 5.7 installation.

Upgrading to version 5.7 solved this issue.

Hopefully this can help someone else at some point in the future if reviewing this issue.

Thanks for your help @Kyslik - you pointed me in the right direction!

Kyslik commented 5 years ago

@MichaelHoughton excellent :), happy coding!