モデルの参照先テーブルを変更

前提・実現したいこと

初めての質問ですが、よろしくお願いいたします。

○問題点
M_Userモデルの参照先をMySQLのm_userテーブルを参照する用に変更する。

モデルの参照テーブルを変更するために
protected $table = 'm_user';
をモデルに記載しているのに参照先が変わらない。

別でM_Circleモデルがあり、そちらは
protected $table = 'm_circle';
で参照先が切り替わりました。

発生している問題・エラーメッセージ

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'USER_ID' in 'where clause' (SQL: select count(*) as aggregate from `users` where `USER_ID` = メールアドレス)

該当のソースコード

ファイル名：M_User.php

PHP
1<?php
2
3namespace App;
4
5use Illuminate\Contracts\Auth\MustVerifyEmail;
6// use Illuminate\Database\Eloquent\Model;
7use Illuminate\Foundation\Auth\USER as Authenticatable;
8use Illuminate\Notifications\Notifiable;
9
10/**
11 * @property int $USER_NO
12 * @property string $USER_ID
13 * @property string $PASSWORD
14.
15.
16.
17
18 */
19class M_User extends Authenticatable
20{
21    use Notifiable;
22    /**
23     * The table associated with the model.
24     *
25     * @var string
26     */
27    protected $table = 'm_user';
28
29    /**
30     * The primary key for the model.
31     *
32     * @var string
33     */
34    protected $primaryKey = 'USER_NO';
35
36    /**
37     * Indicates if the IDs are auto-incrementing.
38     *
39     * @var bool
40     */
41    public $incrementing = false;
42
43    /**
44     * @var array
45     */
46    protected $fillable = ['USER_ID', 'PASSWORD',];
47
48    /**
49     * The attributes that should be hidden for arrays.
50     *
51     * @var array
52     *
53     */
54    protected $hidden = [
55        'PASSWORD',
56    ];
57
58    /**
59     * Get the password for the user.
60     *
61     * @return string
62     */
63    public function getAuthPassword()
64    {
65        return $this->PASSWORD;
66    }
67}
68

ファイル名：auth.php

PHP
1<?php
2
3return [
4
5    /*
6    |--------------------------------------------------------------------------
7    | Authentication Defaults
8    |--------------------------------------------------------------------------
9    |
10    | This option controls the default authentication "guard" and password
11    | reset options for your application. You may change these defaults
12    | as required, but they're a perfect start for most applications.
13    |
14    */
15
16    'defaults' => [
17        'guard' => 'web',
18        'passwords' => 'm_user',
19    ],
20    // 'defaults' => [
21    //     'guard' => 'web',
22    //     'passwords' => 'users',
23    // ],
24
25    /*
26    |--------------------------------------------------------------------------
27    | Authentication Guards
28    |--------------------------------------------------------------------------
29    |
30    | Next, you may define every authentication guard for your application.
31    | Of course, a great default configuration has been defined for you
32    | here which uses session storage and the Eloquent user provider.
33    |
34    | All authentication drivers have a user provider. This defines how the
35    | users are actually retrieved out of your database or other storage
36    | mechanisms used by this application to persist your user's data.
37    |
38    | Supported: "session", "token"
39    |
40    */
41
42    'guards' => [
43        'web' => [
44            'driver' => 'session',
45            'provider' => 'm_user',
46        ],
47        // 'web' => [
48        //     'driver' => 'session',
49        //     'provider' => 'users',
50        // ],
51
52        'api' => [
53            'driver' => 'token',
54            'provider' => 'm_user',
55            'hash' => false,
56        ],
57        // 'api' => [
58        //     'driver' => 'token',
59        //     'provider' => 'users',
60        //     'hash' => false,
61        // ],
62    ],
63
64    /*
65    |--------------------------------------------------------------------------
66    | User Providers
67    |--------------------------------------------------------------------------
68    |
69    | All authentication drivers have a user provider. This defines how the
70    | users are actually retrieved out of your database or other storage
71    | mechanisms used by this application to persist your user's data.
72    |
73    | If you have multiple user tables or models you may configure multiple
74    | sources which represent each model / table. These sources may then
75    | be assigned to any extra authentication guards you have defined.
76    |
77    | Supported: "database", "eloquent"
78    |
79    */
80
81    'providers' => [
82        'm_user' => [
83            'driver' => 'eloquent',
84            'model' => App\M_User::class,
85        ],
86
87        // 'users' => [
88        //     'driver' => 'database',
89        //     'table' => 'users',
90        // ],
91    ],
92
93    /*
94    |--------------------------------------------------------------------------
95    | Resetting Passwords
96    |--------------------------------------------------------------------------
97    |
98    | You may specify multiple password reset configurations if you have more
99    | than one user table or model in the application and you want to have
100    | separate password reset settings based on the specific user types.
101    |
102    | The expire time is the number of minutes that the reset token should be
103    | considered valid. This security feature keeps tokens short-lived so
104    | they have less time to be guessed. You may change this as needed.
105    |
106    */
107
108    'passwords' => [
109        'users' => [
110            'provider' => 'm_user',
111            'table' => 'password_resets',
112            'expire' => 60,
113            'throttle' => 60,
114        ],
115        // 'users' => [
116        //     'provider' => 'users',
117        //     'table' => 'password_resets',
118        //     'expire' => 60,
119        //     'throttle' => 60,
120        // ],
121    ],
122
123    /*
124    |--------------------------------------------------------------------------
125    | Password Confirmation Timeout
126    |--------------------------------------------------------------------------
127    |
128    | Here you may define the amount of seconds before a password confirmation
129    | times out and the user is prompted to re-enter their password via the
130    | confirmation screen. By default, the timeout lasts for three hours.
131    |
132    */
133
134    'password_timeout' => 10800,
135
136];
137

ファイル名：RegisterController.php

PHP
1<?php
2
3namespace App\Http\Controllers\Auth;
4
5use App\Http\Controllers\Controller;
6use App\Providers\RouteServiceProvider;
7use App\M_User;
8use Illuminate\Foundation\Auth\RegistersUsers;
9use Illuminate\Support\Facades\Hash;
10use Illuminate\Support\Facades\Validator;
11
12class RegisterController extends Controller
13{
14    /*
15    |--------------------------------------------------------------------------
16    | Register Controller
17    |--------------------------------------------------------------------------
18    |
19    | This controller handles the registration of new users as well as their
20    | validation and creation. By default this controller uses a trait to
21    | provide this functionality without requiring any additional code.
22    |
23    */
24
25    use RegistersUsers;
26
27    /**
28     * Where to redirect users after registration.
29     *
30     * @var string
31     */
32    protected $redirectTo = RouteServiceProvider::HOME;
33
34    /**
35     * Create a new controller instance.
36     *
37     * @return void
38     */
39    public function __construct()
40    {
41        $this->middleware('guest');
42    }
43
44    protected function guard()
45    {
46        return Auth::guard('PASSWORD');
47    }
48
49    /**
50     * Get a validator for an incoming registration request.
51     *
52     * @param  array  $data
53     * @return \Illuminate\Contracts\Validation\Validator
54     */
55    protected function validator(array $data)
56    {
57        return Validator::make($data, [
58            'USER_ID' => ['required', 'string', 'email', 'max:255', 'unique:users'],
59            'PASSWORD' => ['required', 'string', 'min:8', 'confirmed'],
60        ]);
61    }
62
63    /**
64     * Create a new user instance after a valid registration.
65     *
66     * @param  array  $data
67     * @return \App\User
68     */
69    protected function create(array $data)
70    {
71        return M_User::create([
72            'USER_ID' => $data['email'],
73            'PASSWORD' => Hash::make($data['password']),
74        ]);
75    }
76}

試したこと

・とにかくタイプミスがないか確認
・同じような問題に遭遇した人の記事探す（いくつか見つかるが、モデルに$table変数を再代入して切り替わっているため分からず...）

補足情報（FW/ツールのバージョンなど）

PHP:7.4.9
Laravel:7.0
MySQL:8.0

足りない情報あるかもしれません。
すぐに追加します！

行動規範の内容に同意します

回答3件

ベストアンサー

RegisterController.phpのvalidotorメソッドを修正して解決しました！
m-oguraさん代替案をありがとうございました！

投稿2020/09/20 09:15

WebDeveloperKei

総合スコア21

回答ありがとうございます。
m-ogura様の実装していただいたことは冒頭に記載の通りM_Circleテーブルで実現できました。
今回は認証テーブルなので少し事情が異なるかと思います。
M_UserモデルはAuthenticatableを継承することで認証機能を引き継いだかたちにしたいと思っています。
しかし、M_Userモデルの参照テーブルが変わらない次第です。

エラーメッセージに出力されているSQLを実行している箇所は特定できてますでしょうか。

こちらに関してはvendorの奥深い箇所にあるかと思いますが、直接は編集したくないと思っております。

投稿2020/09/19 04:37

編集2020/09/19 04:38

WebDeveloperKei

総合スコア21

退会済みユーザー

2020/09/19 20:19 編集

根本的な解決ではありませんが、DBにusersというViewを作成してはいかがでしょうか。 create view users as select * from m_user; Authenticatableを継承すると$tableが効かない謎は残りますが、妥協案としてご検討ください。尚、「laravel migration view」でぐぐると、migrationでViewを作成する方法がヒットします。

行動規範の内容に同意します

最低限の実装で検証したところ、$tableで指定した通りにm_userを参照できました。
エラーメッセージに出力されているSQLを実行している箇所は特定できてますでしょうか。

2020_09_18_222557_create_m_user.php

PHP
1<?php
2
3use Illuminate\Database\Migrations\Migration;
4use Illuminate\Database\Schema\Blueprint;
5use Illuminate\Support\Facades\Schema;
6
7class CreateMUser extends Migration
8{
9    /**
10     * Run the migrations.
11     *
12     * @return void
13     */
14    public function up()
15    {
16        Schema::create('m_user', function (Blueprint $table) {
17            $table->integer('USER_NO');
18            $table->string('USER_ID');
19            $table->string('PASSWORD');
20            $table->primary('USER_NO');
21        });
22    }
23
24    /**
25     * Reverse the migrations.
26     *
27     * @return void
28     */
29    public function down()
30    {
31        Schema::dropIfExists('m_user');
32    }
33}

M_UsersSeeder.php

PHP
1<?php
2
3use Illuminate\Database\Seeder;
4
5class M_UsersSeeder extends Seeder
6{
7    /**
8     * Run the database seeds.
9     *
10     * @return void
11     */
12    public function run()
13    {
14        DB::table('m_user')->insert([
15            'USER_NO' => 1,
16            'USER_ID' => 'a',
17            'PASSWORD' => 'A',
18        ]);
19        DB::table('m_user')->insert([
20            'USER_NO' => 2,
21            'USER_ID' => 'b',
22            'PASSWORD' => 'B',
23        ]);
24        DB::table('m_user')->insert([
25            'USER_NO' => 3,
26            'USER_ID' => 'c',
27            'PASSWORD' => 'C',
28        ]);
29    }
30}

M_User.php

PHP
1<?php
2
3namespace App;
4
5use Illuminate\Database\Eloquent\Model;
6
7class M_User extends Model
8{
9    protected $table = 'm_user';
10    protected $primaryKey = 'USER_NO';
11    public $incrementing = false;
12}

UserController.php

PHP
1<?php
2
3namespace App\Http\Controllers;
4
5use Illuminate\Http\Request;
6
7class UserController extends Controller
8{
9    public function GetQuery(Request $request) {
10        \DB::enableQueryLog();
11        $m_user = \App\M_User::where('USER_NO', $request['id'])->get();
12        return \DB::getQueryLog();
13    }
14    public function GetUser(Request $request) {
15        return \App\M_User::where('USER_NO', $request['id'])->get();
16    }
17}

web.php

PHP
1<?php
2
3use Illuminate\Support\Facades\Route;
4
5/*
6|--------------------------------------------------------------------------
7| Web Routes
8|--------------------------------------------------------------------------
9|
10| Here is where you can register web routes for your application. These
11| routes are loaded by the RouteServiceProvider within a group which
12| contains the "web" middleware group. Now create something great!
13|
14*/
15
16Route::get('/', function () {
17    return view('welcome');
18});
19Route::get('/query', 'UserController@GetQuery');
20Route::get('/user', 'UserController@GetUser');