search

freescout-help-desk / freescout

FreeScout — Free self-hosted help desk & shared mailbox (Zendesk / Help Scout alternative)
https://freescout.net
GNU Affero General Public License v3.0
3.07k stars 503 forks source link

Sent Folder module fails with Postgres #2342

Closed radiowavesurfer closed 2 years ago

radiowavesurfer commented 2 years ago

Hi. When running with a Postgres database, clicking on the "Sent" folder in the UI produces "Whoops something went wrong".

Full error log is attached below, but in short it's another case of Postgres being more strict than MySQL about queries that use GROUP BY.

The query in question is in the hooks function of Modules/SentFolder/Providers/SentFolderServiceProvider.php, under \Eventy::addFilter('folder.conversations_query'

I've attempted a fix by altering line 63 to use an aggregate function, max(threads.created_at), as follows: 

                return Conversation::select(['conversations.*', \DB::raw('max(threads.created_at) as last_user_reply_at')])

This seems to be working ok for me.

Thanks.

Full log from the original error below:

SQLSTATE[42803]: Grouping error: 7 ERROR: column "threads.created_at" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select "conversations".*, "threads"."created_at" as "last_us...
                                  ^ (SQL: select "conversations".*, "threads"."created_at" as "last_user_reply_at" from "conversations" inner join "threads" on "conversations"."id" = "threads"."conversation_id" and "threads"."type" = 2 and "threads"."created_by_user_id" = 1 where "mailbox_id" = 1 and "conversations"."state" = 2 group by "conversations"."id" order by "last_user_reply_at" desc limit 50 offset 0) {"userId":1,"email":"nickbrown@petersen-stainless.co.uk","exception":"[object] (Illuminate\\Database\\QueryException(code: 42803): SQLSTATE[42803]: Grouping error: 7 ERROR:  column \"threads.created_at\" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select \"conversations\".*, \"threads\".\"created_at\" as \"last_us...
                                  ^ (SQL: select \"conversations\".*, \"threads\".\"created_at\" as \"last_user_reply_at\" from \"conversations\" inner join \"threads\" on \"conversations\".\"id\" = \"threads\".\"conversation_id\" and \"threads\".\"type\" = 2 and \"threads\".\"created_by_user_id\" = 1 where \"mailbox_id\" = 1 and \"conversations\".\"state\" = 2 group by \"conversations\".\"id\" order by \"last_user_reply_at\" desc limit 50 offset 0) at /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664, Doctrine\\DBAL\\Driver\\PDO\\Exception(code: 42803): SQLSTATE[42803]: Grouping error: 7 ERROR:  column \"threads.created_at\" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select \"conversations\".*, \"threads\".\"created_at\" as \"last_us...
                                  ^ at /var/www/html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18, PDOException(code: 42803): SQLSTATE[42803]: Grouping error: 7 ERROR:  column \"threads.created_at\" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select \"conversations\".*, \"threads\".\"created_at\" as \"last_us...
                                  ^ at /var/www/html/overrides/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:115)
[stacktrace]
#0 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php(624): Illuminate\\Database\\Connection->runQueryCallback('select \"convers...', Array, Object(Closure))
#1 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php(333): Illuminate\\Database\\Connection->run('select \"convers...', Array, Object(Closure))
#2 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(1719): Illuminate\\Database\\Connection->select('select \"convers...', Array, true)
#3 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(1704): Illuminate\\Database\\Query\\Builder->runSelect()
#4 /var/www/html/vendor/watson/rememberable/src/Query/Builder.php(56): Illuminate\\Database\\Query\\Builder->get(Array)
#5 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(483): Watson\\Rememberable\\Query\\Builder->get(Array)
#6 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(467): Illuminate\\Database\\Eloquent\\Builder->getModels(Array)
#7 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(713): Illuminate\\Database\\Eloquent\\Builder->get(Array)
#8 /var/www/html/app/Http/Controllers/MailboxesController.php(468): Illuminate\\Database\\Eloquent\\Builder->paginate(50)
#9 [internal function]: App\\Http\\Controllers\\MailboxesController->view('1', '175')
#10 /var/www/html/overrides/laravel/framework/src/Illuminate/Routing/Controller.php(54): call_user_func_array(Array, Array)
#11 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php(45): Illuminate\\Routing\\Controller->callAction('view', Array)
#12 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Route.php(212): Illuminate\\Routing\\ControllerDispatcher->dispatch(Object(Illuminate\\Routing\\Route), Object(App\\Http\\Controllers\\MailboxesController), 'view')
#13 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Route.php(169): Illuminate\\Routing\\Route->runController()
#14 /var/www/html/overrides/laravel/framework/src/Illuminate/Routing/Router.php(658): Illuminate\\Routing\\Route->run()
#15 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(30): Illuminate\\Routing\\Router->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#16 /var/www/html/app/Http/Middleware/CustomHandle.php(22): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#17 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): App\\Http\\Middleware\\CustomHandle->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#18 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#19 /var/www/html/app/Http/Middleware/LogoutIfDeleted.php(28): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#20 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): App\\Http\\Middleware\\LogoutIfDeleted->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#21 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#22 /var/www/html/app/Http/Middleware/Localize.php(27): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#23 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): App\\Http\\Middleware\\Localize->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#24 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#25 /var/www/html/app/Http/Middleware/HttpsRedirect.php(49): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#26 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): App\\Http\\Middleware\\HttpsRedirect->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#27 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#28 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Middleware/SubstituteBindings.php(41): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#29 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): Illuminate\\Routing\\Middleware\\SubstituteBindings->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#30 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#31 /var/www/html/overrides/laravel/framework/src/Illuminate/Auth/Middleware/Authenticate.php(45): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#32 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): Illuminate\\Auth\\Middleware\\Authenticate->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#33 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#34 /var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/VerifyCsrfToken.php(67): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#35 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): Illuminate\\Foundation\\Http\\Middleware\\VerifyCsrfToken->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#36 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#37 /var/www/html/vendor/laravel/framework/src/Illuminate/View/Middleware/ShareErrorsFromSession.php(49): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#38 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): Illuminate\\View\\Middleware\\ShareErrorsFromSession->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#39 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#40 /var/www/html/app/Http/Middleware/TokenAuth.php(31): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#41 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): App\\Http\\Middleware\\TokenAuth->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#42 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#43 /var/www/html/vendor/laravel/framework/src/Illuminate/Session/Middleware/StartSession.php(63): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#44 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): Illuminate\\Session\\Middleware\\StartSession->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#45 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#46 /var/www/html/vendor/laravel/framework/src/Illuminate/Cookie/Middleware/AddQueuedCookiesToResponse.php(37): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#47 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): Illuminate\\Cookie\\Middleware\\AddQueuedCookiesToResponse->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#48 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#49 /var/www/html/vendor/laravel/framework/src/Illuminate/Cookie/Middleware/EncryptCookies.php(59): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#50 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): Illuminate\\Cookie\\Middleware\\EncryptCookies->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#51 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#52 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(102): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#53 /var/www/html/overrides/laravel/framework/src/Illuminate/Routing/Router.php(660): Illuminate\\Pipeline\\Pipeline->then(Object(Closure))
#54 /var/www/html/overrides/laravel/framework/src/Illuminate/Routing/Router.php(635): Illuminate\\Routing\\Router->runRouteWithinStack(Object(Illuminate\\Routing\\Route), Object(Illuminate\\Http\\Request))
#55 /var/www/html/overrides/laravel/framework/src/Illuminate/Routing/Router.php(601): Illuminate\\Routing\\Router->runRoute(Object(Illuminate\\Http\\Request), Object(Illuminate\\Routing\\Route))
#56 /var/www/html/overrides/laravel/framework/src/Illuminate/Routing/Router.php(590): Illuminate\\Routing\\Router->dispatchToRoute(Object(Illuminate\\Http\\Request))
#57 /var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(176): Illuminate\\Routing\\Router->dispatch(Object(Illuminate\\Http\\Request))
#58 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(30): Illuminate\\Foundation\\Http\\Kernel->Illuminate\\Foundation\\Http\\{closure}(Object(Illuminate\\Http\\Request))
#59 /var/www/html/app/Http/Middleware/TerminateHandler.php(12): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#60 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): App\\Http\\Middleware\\TerminateHandler->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#61 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#62 /var/www/html/app/Http/Middleware/ResponseHeaders.php(11): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#63 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): App\\Http\\Middleware\\ResponseHeaders->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#64 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#65 /var/www/html/vendor/fideloper/proxy/src/TrustProxies.php(56): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#66 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): Fideloper\\Proxy\\TrustProxies->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#67 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#68 /var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php(30): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#69 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): Illuminate\\Foundation\\Http\\Middleware\\TransformsRequest->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#70 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#71 /var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php(30): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#72 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): Illuminate\\Foundation\\Http\\Middleware\\TransformsRequest->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#73 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#74 /var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/ValidatePostSize.php(27): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#75 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): Illuminate\\Foundation\\Http\\Middleware\\ValidatePostSize->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#76 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#77 /var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/CheckForMaintenanceMode.php(46): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#78 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): Illuminate\\Foundation\\Http\\Middleware\\CheckForMaintenanceMode->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#79 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#80 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(102): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#81 /var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(151): Illuminate\\Pipeline\\Pipeline->then(Object(Closure))
#82 /var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(116): Illuminate\\Foundation\\Http\\Kernel->sendRequestThroughRouter(Object(Illuminate\\Http\\Request))
#83 /var/www/html/public/index.php(83): Illuminate\\Foundation\\Http\\Kernel->handle(Object(Illuminate\\Http\\Request))
#84 {main}
"}
freescout-helpdesk commented 2 years ago

Fixed in Sent Folder Module v1.0.4