QueryException SQLSTATE[42S22]: Column not found: 1054

0
votes

i have query exception SQLSTATE[42S22]: Column not found: 1054 Champ '3' inconnu dans on clause (SQL: select content, name, to_id from message inner join users on users.id = 3 where to_id = 1)

here is my controller :

public function show(User $user) {

    $users = DB::table('users')
        ->select('name','id')
        ->where('id','!=',auth()->user()->id)
        ->get();

    $messages = DB::table('message')
        ->join('users','users.id','=',$user->id)
        ->select('content','name','to_id')
        ->where('to_id','=',auth()->user()->id)
        ->get();


    return view('conversations/show',compact('users','user','messages'));
}
2
sqllaravel

2 Answers

0
votes

You need to join the tables based on a foreign key. Like:

->join('users', 'users.id', '=', 'message.user_id')

and you need to specify the table in "select" and "where" after joining. Like:

->select('message.content','users.name','messages.to_id')
->where('message.to_id', auth()->user()->id)

and instead of:

->join('users', 'users.id','=', $user->id)

do this after joining:

->where('users.id', $user->id)
0
votes

You need to correct your join clause in messages query.

$messages = DB::table('message')
        ->join('users', function($join) use($user) {
            $join->('users.id', '=', 'messages.user_id')
                ->where('users.id', '=', $user->id)
        })
        ->select('content','name','to_id')
        ->where('to_id','=',auth()->user()->id)
        ->get();