search

kcal-app / kcal

the personal food nutrition journal
Mozilla Public License 2.0
295 stars 27 forks source link

SQL error #64

Closed drizt closed 7 months ago

drizt commented 7 months ago

I use mariadb database and got such exception.

Illuminate\Database\QueryException:
SQLSTATE[42000]: Syntax error or access violation: 1055 'kcal.tags.name' isn't in GROUP BY (SQL: select `id`, `name`, count(*) as total from `tags` inner join `taggables` on `taggables`.`tag_id` = `id` where `taggables`.`taggable_type` = App\Models\Recipe group by `id` order by json_unquote(json_extract(`name`, '$."en"')) asc)

  at /home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Database/Connection.php:760
  at Illuminate\Database\Connection->runQueryCallback()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Database/Connection.php:720)
  at Illuminate\Database\Connection->run()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Database/Connection.php:405)
  at Illuminate\Database\Connection->select()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php:2705)
  at Illuminate\Database\Query\Builder->runSelect()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php:2694)
  at Illuminate\Database\Query\Builder->Illuminate\Database\Query\{closure}()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php:3230)
  at Illuminate\Database\Query\Builder->onceWithColumns()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php:2693)
  at Illuminate\Database\Query\Builder->get()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php:710)
  at Illuminate\Database\Eloquent\Builder->getModels()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php:694)
  at Illuminate\Database\Eloquent\Builder->get()
     (/home/taurus/develop/kcal/app/Models/Traits/Ingredient.php:59)
  at App\Models\Recipe::getTagTotals()
     (/home/taurus/develop/kcal/app/Http/Controllers/RecipeController.php:29)
  at App\Http\Controllers\RecipeController->index()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Routing/Controller.php:54)
  at Illuminate\Routing\Controller->callAction()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php:43)
  at Illuminate\Routing\ControllerDispatcher->dispatch()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Routing/Route.php:259)
  at Illuminate\Routing\Route->runController()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Routing/Route.php:205)
  at Illuminate\Routing\Route->run()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Routing/Router.php:798)
  at Illuminate\Routing\Router->Illuminate\Routing\{closure}()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:141)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
     (/home/taurus/develop/kcal/app/Http/Middleware/DisableBrowserCache.php:27)
  at App\Http\Middleware\DisableBrowserCache->handle()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:180)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
     (/home/taurus/develop/kcal/vendor/spatie/laravel-csp/src/AddCspHeaders.php:13)
  at Spatie\Csp\AddCspHeaders->handle()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:180)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Routing/Middleware/SubstituteBindings.php:50)
  at Illuminate\Routing\Middleware\SubstituteBindings->handle()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:180)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Auth/Middleware/Authenticate.php:44)
  at Illuminate\Auth\Middleware\Authenticate->handle()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:180)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/VerifyCsrfToken.php:78)
  at Illuminate\Foundation\Http\Middleware\VerifyCsrfToken->handle()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:180)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/View/Middleware/ShareErrorsFromSession.php:49)
  at Illuminate\View\Middleware\ShareErrorsFromSession->handle()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:180)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Session/Middleware/StartSession.php:121)
  at Illuminate\Session\Middleware\StartSession->handleStatefulRequest()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Session/Middleware/StartSession.php:64)
  at Illuminate\Session\Middleware\StartSession->handle()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:180)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Cookie/Middleware/AddQueuedCookiesToResponse.php:37)
  at Illuminate\Cookie\Middleware\AddQueuedCookiesToResponse->handle()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:180)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Cookie/Middleware/EncryptCookies.php:67)
  at Illuminate\Cookie\Middleware\EncryptCookies->handle()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:180)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:116)
  at Illuminate\Pipeline\Pipeline->then()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Routing/Router.php:797)
  at Illuminate\Routing\Router->runRouteWithinStack()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Routing/Router.php:776)
  at Illuminate\Routing\Router->runRoute()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Routing/Router.php:740)
  at Illuminate\Routing\Router->dispatchToRoute()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Routing/Router.php:729)
  at Illuminate\Routing\Router->dispatch()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php:190)
  at Illuminate\Foundation\Http\Kernel->Illuminate\Foundation\Http\{closure}()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:141)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php:21)
  at Illuminate\Foundation\Http\Middleware\TransformsRequest->handle()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/ConvertEmptyStringsToNull.php:31)
  at Illuminate\Foundation\Http\Middleware\ConvertEmptyStringsToNull->handle()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:180)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php:21)
  at Illuminate\Foundation\Http\Middleware\TransformsRequest->handle()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TrimStrings.php:40)
  at Illuminate\Foundation\Http\Middleware\TrimStrings->handle()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:180)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/ValidatePostSize.php:27)
  at Illuminate\Foundation\Http\Middleware\ValidatePostSize->handle()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:180)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/PreventRequestsDuringMaintenance.php:86)
  at Illuminate\Foundation\Http\Middleware\PreventRequestsDuringMaintenance->handle()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:180)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
     (/home/taurus/develop/kcal/vendor/fruitcake/laravel-cors/src/HandleCors.php:38)
  at Fruitcake\Cors\HandleCors->handle()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:180)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Http/Middleware/TrustProxies.php:39)
  at Illuminate\Http\Middleware\TrustProxies->handle()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:180)
  at Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:116)
  at Illuminate\Pipeline\Pipeline->then()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php:165)
  at Illuminate\Foundation\Http\Kernel->sendRequestThroughRouter()
     (/home/taurus/develop/kcal/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php:134)
  at Illuminate\Foundation\Http\Kernel->handle()
     (/home/taurus/develop/kcal/public/index.php:51)

For myself I fixed it here

    public static function getTagTotals(string $locale = null): DatabaseCollection {
        $locale = $locale ?? app()->getLocale();
        return Tag::query()->join('taggables', 'taggables.tag_id', '=', 'id')
            ->select(['id', 'name', DB::raw('count(*) as total')])
            ->where('taggables.taggable_type', '=', static::class)
            ->groupBy(['id', 'name'])
            ->orderBy("name->{$locale}")
            ->get();
    }

Error means when you use GROUP BY you can select only aggregate columns or columns from GROUP BY.

cdubz commented 7 months ago

Thanks for identifying the issue and fix!