Join not working unless hard code id number-CodePudding

This works if I use the id hard coded but what I am trying to do is select the profile.id where profile.user_id is equal to users.id. When I try that, it says invalid integer and if I dd($profile_id) I get an empty array.

$profile_id = DB::table('profile')
        ->join('users', 'profile.user_id', '=', 'users.id') 
        ->select('profile.id')
        ->where('profile.user_id',9)
        ->get();

I changed the code to the following and get this error:

General error: 1366 Incorrect integer value: '[{"id":12}]' for column 'profile_id'

Here is my migration for the application table

Schema::create('pilgrim_application', function (Blueprint $table) {
            $table->id();
                       
            $table->integer('user_id')->constrained('users')->onDelete('cascade');
            $table->integer('profile_id')->constrained('profile')->onDelete('cascade');
            $table->string('nickname');
            $table->string('marital_status');
            $table->string('special_needs');
            $table->text('why_attend');
            $table->string('pcontact');
            $table->string('phome');
            $table->string('pcell');
            $table->string('acontact');
            $table->string('ahome');
            $table->string('acell');
            $table->date('date_signed');
            $table->timestamps();
        });

Here is what I changed my code to on the store method

$user_id = Auth::user()->id;
        $profile_id = DB::table('profile')
        ->select('profile.id')
        ->where('profile.user_id',$user_id)
        ->get();

        $this->validate($request, [
            'nickname' => 'max:255|min:4',
            'why_attend' => 'required',
            'pcontact' => 'required|min:2|max:255',
            'phome' => 'required|min:4|max:255',
            'pcell' => 'required|min:4|max:255',
            'acontact' => 'required|min:4|max:255',
            'ahome' => 'required|min:4|max:255',
            'acell' => 'required|min:4|max:255',
            'date_signed' => 'required|date',
          ]);

          $pilgrimapp = new PilgrimApp;
          $pilgrimapp->user_id = $user_id;
          $pilgrimapp->profile_id = $profile_id;
          $pilgrimapp->nickname = $request->nickname;
          $pilgrimapp->marital_status = $request->marital_status;

        //   $sn_checkbox = implode(",", $request->get('special_needs'));
          $pilgrimapp->special_needs = $request->special_needs;
          $pilgrimapp->why_attend = $request->why_attend;
          $pilgrimapp->pcontact = $request->pcontact;
          $pilgrimapp->phome = $request->phome;
          $pilgrimapp->pcell = $request->pcell;
          $pilgrimapp->acontact = $request->acontact;
          $pilgrimapp->ahome = $request->ahome;
          $pilgrimapp->acell = $request->acell;
          $pilgrimapp->date_signed = $request->date_signed;
          $pilgrimapp->save();

CodePudding user response：

to compare between columns, not column and value, you should use whereColumn:

The whereColumn method may be used to verify that two columns are equal

$profile_id = DB::table('profile')
        ->join('users', 'profile.user_id', '=', 'users.id') 
        ->select('profile.id')
        ->whereColumn('profile.user_id','users.id')
        ->get();

anyway, this where is useless, since this condition already has been set in join clause.

CodePudding user response：

are you sure there is a profile.user_id ???