LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@miriwo(大川 峻)inBOOM TECH CAFE

laravel Column not found: 1054 Unknown column 'deleted_at' in 'where clause'のエラーが出た

Last updated at Posted at 2023-08-15

概要

  • テストを実行したらColumn not found: 1054 Unknown column 'deleted_at' in 'where clause'というエラーが出た。一瞬詰まったので情報を載せておく。

エラー内容

  • どこかのテーブルにdeleted_atのカラムが無い旨のエラー
  • おそらくどこかのテーブルにdeleted_atが無いのに無理やり呼び出そうとしているっぽい。
  • ただ、テーブル名が明示的に出ていないことと、PDO（PHP Data Objects）の例外なことが解せない。モデルのインスタンスからdeleted_atにアクセスできない場合は別のエラーが出るはずである。
  • PDOはDBにアクセスするためのPHPの軽量な拡張

エラー原因

  • 後述する付録にStackTraceの内容を記載したがどうやらusersテーブルにdeleted_atカラムが無いようだ。

  • Userモデルにuse SoftDeletes;とマイグレーションのup側に$table->softDeletes();を追記しマイグレーションを行った。

  • PDOの例外が発生した理由は専用のリクエストクラスのrulesメソッドの中で下記のようにバリデーションルールを設定していたからだった。

    'userId' => [
    'required',
    'integer',
    'exists:users,id,deleted_at,NULL'
],

エラー原因を取り除いたのにエラーが解消しない

  • エラー原因を取り除き、マイグレーションを行ったがエラーが解決しなかった。おそらくDBのスキーマがどこかにキャッシュされてしまっている。

  • 下記コマンドを実行し、キャッシュクリアを行って本エラーは解消した。

    php artisan cache:clear

追記（2023/08/16）

  • PDO（PHP Data Objects）の例外なことが解せない。と書いたが特にそんなことはなかった。
  • テーブルにカラムが存在しないけどinsertしようとした場合などにPDOの例外が発生することは普通にある。

付録

  • エラー時のStackTraceをすべてこちらに載せておく。
[previous exception] [object] (PDOException(code: 42S22): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'deleted_at' in 'where clause' at /app/vendor/laravel/framework/src/Illuminate/Database/Connection.php:414)
[stacktrace]
#0 /app/vendor/laravel/framework/src/Illuminate/Database/Connection.php(414): PDO->prepare('select count(*)...')
#1 /app/vendor/laravel/framework/src/Illuminate/Database/Connection.php(753): Illuminate\\Database\\Connection->Illuminate\\Database\\{closure}('select count(*)...', Array)
#2 /app/vendor/laravel/framework/src/Illuminate/Database/Connection.php(720): Illuminate\\Database\\Connection->runQueryCallback('select count(*)...', Array, Object(Closure))
#3 /app/vendor/laravel/framework/src/Illuminate/Database/Connection.php(405): Illuminate\\Database\\Connection->run('select count(*)...', Array, Object(Closure))
#4 /app/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2718): Illuminate\\Database\\Connection->select('select count(*)...', Array, false)
#5 /app/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2707): Illuminate\\Database\\Query\\Builder->runSelect()
#6 /app/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(3247): Illuminate\\Database\\Query\\Builder->Illuminate\\Database\\Query\\{closure}()
#7 /app/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2706): Illuminate\\Database\\Query\\Builder->onceWithColumns(Array, Object(Closure))
#8 /app/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(3174): Illuminate\\Database\\Query\\Builder->get(Array)
#9 /app/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(3102): Illuminate\\Database\\Query\\Builder->aggregate('count', Array)
#10 /app/vendor/laravel/framework/src/Illuminate/Validation/DatabasePresenceVerifier.php(54): Illuminate\\Database\\Query\\Builder->count()
#11 /app/vendor/laravel/framework/src/Illuminate/Validation/Concerns/ValidatesAttributes.php(880): Illuminate\\Validation\\DatabasePresenceVerifier->getCount('users', 'id', 3, NULL, NULL, Array)
#12 /app/vendor/laravel/framework/src/Illuminate/Validation/Concerns/ValidatesAttributes.php(851): Illuminate\\Validation\\Validator->getExistCount(NULL, 'users', 'id', 3, Array)
#13 /app/vendor/laravel/framework/src/Illuminate/Validation/Validator.php(616): Illuminate\\Validation\\Validator->validateExists('userId', 3, Array, Object(Illuminate\\Validation\\Validator))
#14 /app/vendor/laravel/framework/src/Illuminate/Validation/Validator.php(422): Illuminate\\Validation\\Validator->validateAttribute('userId', 'Exists')
#15 /app/vendor/laravel/framework/src/Illuminate/Validation/Validator.php(453): Illuminate\\Validation\\Validator->passes()
#16 /app/vendor/laravel/framework/src/Illuminate/Validation/ValidatesWhenResolvedTrait.php(31): Illuminate\\Validation\\Validator->fails()
#17 /app/vendor/laravel/framework/src/Illuminate/Foundation/Providers/FormRequestServiceProvider.php(30): Illuminate\\Foundation\\Http\\FormRequest->validateResolved()
#18 /app/vendor/laravel/framework/src/Illuminate/Container/Container.php(1279): Illuminate\\Foundation\\Providers\\FormRequestServiceProvider->Illuminate\\Foundation\\Providers\\{closure}(Object(App\\Http\\Requests\\User\\ChangeTypeRequest), Object(Illuminate\\Foundation\\Application))
#19 /app/vendor/laravel/framework/src/Illuminate/Container/Container.php(1243): Illuminate\\Container\\Container->fireCallbackArray(Object(App\\Http\\Requests\\User\\ChangeTypeRequest), Array)
#20 /app/vendor/laravel/framework/src/Illuminate/Container/Container.php(1229): Illuminate\\Container\\Container->fireAfterResolvingCallbacks('App\\\\Http\\\\Reques...', Object(App\\Http\\Requests\\User\\ChangeTypeRequest))
#21 /app/vendor/laravel/framework/src/Illuminate/Container/Container.php(790): Illuminate\\Container\\Container->fireResolvingCallbacks('App\\\\Http\\\\Reques...', Object(App\\Http\\Requests\\User\\ChangeTypeRequest))
#22 /app/vendor/laravel/framework/src/Illuminate/Foundation/Application.php(933): Illuminate\\Container\\Container->resolve('App\\\\Http\\\\Reques...', Array, true)
#23 /app/vendor/laravel/framework/src/Illuminate/Container/Container.php(706): Illuminate\\Foundation\\Application->resolve('App\\\\Http\\\\Reques...', Array)
#24 /app/vendor/laravel/framework/src/Illuminate/Foundation/Application.php(918): Illuminate\\Container\\Container->make('App\\\\Http\\\\Reques...', Array)
#25 /app/vendor/laravel/framework/src/Illuminate/Routing/ResolvesRouteDependencies.php(85): Illuminate\\Foundation\\Application->make('App\\\\Http\\\\Reques...')
#26 /app/vendor/laravel/framework/src/Illuminate/Routing/ResolvesRouteDependencies.php(50): Illuminate\\Routing\\ControllerDispatcher->transformDependency(Object(ReflectionParameter), Array, Object(stdClass))
#27 /app/vendor/laravel/framework/src/Illuminate/Routing/ResolvesRouteDependencies.php(29): Illuminate\\Routing\\ControllerDispatcher->resolveMethodDependencies(Array, Object(ReflectionMethod))
#28 /app/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php(59): Illuminate\\Routing\\ControllerDispatcher->resolveClassMethodDependencies(Array, Object(App\\Http\\Controllers\\User\\ChangeTypeController), '__invoke')
#29 /app/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php(40): Illuminate\\Routing\\ControllerDispatcher->resolveParameters(Object(Illuminate\\Routing\\Route), Object(App\\Http\\Controllers\\User\\ChangeTypeController), '__invoke')
#30 /app/vendor/laravel/framework/src/Illuminate/Routing/Route.php(259): Illuminate\\Routing\\ControllerDispatcher->dispatch(Object(Illuminate\\Routing\\Route), Object(App\\Http\\Controllers\\User\\ChangeTypeController), '__invoke')
#31 /app/vendor/laravel/framework/src/Illuminate/Routing/Route.php(205): Illuminate\\Routing\\Route->runController()
#32 /app/vendor/laravel/framework/src/Illuminate/Routing/Router.php(798): Illuminate\\Routing\\Route->run()
#33 /app/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(141): Illuminate\\Routing\\Router->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#34 /app/vendor/laravel/framework/src/Illuminate/Routing/Middleware/SubstituteBindings.php(50): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#35 /app/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(180): Illuminate\\Routing\\Middleware\\SubstituteBindings->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#36 /app/vendor/laravel/framework/src/Illuminate/Routing/Middleware/ThrottleRequests.php(126): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#37 /app/vendor/laravel/framework/src/Illuminate/Routing/Middleware/ThrottleRequests.php(92): Illuminate\\Routing\\Middleware\\ThrottleRequests->handleRequest(Object(Illuminate\\Http\\Request), Object(Closure), Array)
#38 /app/vendor/laravel/framework/src/Illuminate/Routing/Middleware/ThrottleRequests.php(54): Illuminate\\Routing\\Middleware\\ThrottleRequests->handleRequestUsingNamedLimiter(Object(Illuminate\\Http\\Request), Object(Closure), 'api', Object(Closure))
#39 /app/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(180): Illuminate\\Routing\\Middleware\\ThrottleRequests->handle(Object(Illuminate\\Http\\Request), Object(Closure), 'api')
#40 /app/vendor/laravel/framework/src/Illuminate/Auth/Middleware/Authenticate.php(45): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#41 /app/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(180): Illuminate\\Auth\\Middleware\\Authenticate->handle(Object(Illuminate\\Http\\Request), Object(Closure), 'sanctum')
#42 /app/vendor/laravel/sanctum/src/Http/Middleware/EnsureFrontendRequestsAreStateful.php(33): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#43 /app/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(141): Laravel\\Sanctum\\Http\\Middleware\\EnsureFrontendRequestsAreStateful->Laravel\\Sanctum\\Http\\Middleware\\{closure}(Object(Illuminate\\Http\\Request))
#44 /app/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(116): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#45 /app/vendor/laravel/sanctum/src/Http/Middleware/EnsureFrontendRequestsAreStateful.php(32): Illuminate\\Pipeline\\Pipeline->then(Object(Closure))
#46 /app/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(180): Laravel\\Sanctum\\Http\\Middleware\\EnsureFrontendRequestsAreStateful->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#47 /app/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(116): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#48 /app/vendor/laravel/framework/src/Illuminate/Routing/Router.php(797): Illuminate\\Pipeline\\Pipeline->then(Object(Closure))
#49 /app/vendor/laravel/framework/src/Illuminate/Routing/Router.php(776): Illuminate\\Routing\\Router->runRouteWithinStack(Object(Illuminate\\Routing\\Route), Object(Illuminate\\Http\\Request))
#50 /app/vendor/laravel/framework/src/Illuminate/Routing/Router.php(740): Illuminate\\Routing\\Router->runRoute(Object(Illuminate\\Http\\Request), Object(Illuminate\\Routing\\Route))
#51 /app/vendor/laravel/framework/src/Illuminate/Routing/Router.php(729): Illuminate\\Routing\\Router->dispatchToRoute(Object(Illuminate\\Http\\Request))
#52 /app/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(200): Illuminate\\Routing\\Router->dispatch(Object(Illuminate\\Http\\Request))
#53 /app/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(141): Illuminate\\Foundation\\Http\\Kernel->Illuminate\\Foundation\\Http\\{closure}(Object(Illuminate\\Http\\Request))
#54 /app/app/Http/Middleware/LogRequestAndResponse.php(24): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#55 /app/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(180): App\\Http\\Middleware\\LogRequestAndResponse->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#56 /app/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php(21): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#57 /app/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/ConvertEmptyStringsToNull.php(31): Illuminate\\Foundation\\Http\\Middleware\\TransformsRequest->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#58 /app/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(180): Illuminate\\Foundation\\Http\\Middleware\\ConvertEmptyStringsToNull->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#59 /app/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php(21): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#60 /app/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TrimStrings.php(40): Illuminate\\Foundation\\Http\\Middleware\\TransformsRequest->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#61 /app/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(180): Illuminate\\Foundation\\Http\\Middleware\\TrimStrings->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#62 /app/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/ValidatePostSize.php(27): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#63 /app/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(180): Illuminate\\Foundation\\Http\\Middleware\\ValidatePostSize->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#64 /app/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/PreventRequestsDuringMaintenance.php(86): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#65 /app/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(180): Illuminate\\Foundation\\Http\\Middleware\\PreventRequestsDuringMaintenance->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#66 /app/vendor/laravel/framework/src/Illuminate/Http/Middleware/HandleCors.php(62): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#67 /app/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(180): Illuminate\\Http\\Middleware\\HandleCors->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#68 /app/vendor/laravel/framework/src/Illuminate/Http/Middleware/TrustProxies.php(39): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#69 /app/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(180): Illuminate\\Http\\Middleware\\TrustProxies->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#70 /app/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(116): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#71 /app/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(175): Illuminate\\Pipeline\\Pipeline->then(Object(Closure))
#72 /app/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(144): Illuminate\\Foundation\\Http\\Kernel->sendRequestThroughRouter(Object(Illuminate\\Http\\Request))
#73 /app/vendor/laravel/framework/src/Illuminate/Foundation/Testing/Concerns/MakesHttpRequests.php(563): Illuminate\\Foundation\\Http\\Kernel->handle(Object(Illuminate\\Http\\Request))
#74 /app/vendor/laravel/framework/src/Illuminate/Foundation/Testing/Concerns/MakesHttpRequests.php(529): Illuminate\\Foundation\\Testing\\TestCase->call('POST', '/api/user/chang...', Array, Array, Array, Array, '{\"userId\":3,\"us...')
#75 /app/vendor/laravel/framework/src/Illuminate/Foundation/Testing/Concerns/MakesHttpRequests.php(367): Illuminate\\Foundation\\Testing\\TestCase->json('POST', '/api/user/chang...', Array, Array, 0)
#76 /app/tests/TestCase.php(30): Illuminate\\Foundation\\Testing\\TestCase->postJson('/api/user/chang...', Array)
#77 /app/tests/Feature/App/Http/Controllers/User/ChangeTypeControllerTest.php(54): Tests\\TestCase->sendRequest('/api/user/chang...', 'post', Array, Object(App\\Models\\User))
#78 /app/vendor/phpunit/phpunit/src/Framework/TestCase.php(1040): Tests\\Feature\\Http\\Controllers\\User\\ChangeTypeControllerTest->test_ユーザータイプ更新_正常系()
#79 /app/vendor/laravel/framework/src/Illuminate/Foundation/Testing/TestCase.php(173): PHPUnit\\Framework\\TestCase->runTest()
#80 /app/vendor/phpunit/phpunit/src/Framework/TestCase.php(635): Illuminate\\Foundation\\Testing\\TestCase->runTest()
#81 /app/vendor/phpunit/phpunit/src/Framework/TestRunner.php(101): PHPUnit\\Framework\\TestCase->runBare()
#82 /app/vendor/phpunit/phpunit/src/Framework/TestCase.php(462): PHPUnit\\Framework\\TestRunner->run(Object(Tests\\Feature\\Http\\Controllers\\User\\ChangeTypeControllerTest))
#83 /app/vendor/phpunit/phpunit/src/Framework/TestSuite.php(351): PHPUnit\\Framework\\TestCase->run()
#84 /app/vendor/phpunit/phpunit/src/TextUI/TestRunner.php(63): PHPUnit\\Framework\\TestSuite->run()
#85 /app/vendor/phpunit/phpunit/src/TextUI/Application.php(143): PHPUnit\\TextUI\\TestRunner->run(Object(PHPUnit\\TextUI\\Configuration\\Configuration), Object(PHPUnit\\Runner\\ResultCache\\DefaultResultCache), Object(PHPUnit\\Framework\\TestSuite))
#86 /app/vendor/phpunit/phpunit/phpunit(99): PHPUnit\\TextUI\\Application->run(Array)
#87 {main}
"}
1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?