Trying to use string as foreign key in laravel

I am just a beginner and trying to use string as foreign key in laravel but getting this error while fetching the data:-

SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘read’ in ‘where clause’ (SQL: select from as sender_id, count(from) as messages_count from messages where to = d3c364bb-0982-46ba-869a-24dbb2c50aea and read = 0 group by from)

In here I am fetching users contacts from received and a group of unread message count

public function get(){
        $contacts = DB::table('received')
    ->where('user_id', Auth::user()->uuid)
    ->get();
    
     $unreadIds = Message::select(DB::raw('`from` as sender_id, count(`from`) as messages_count'))
            ->where('to', Auth::user()->uuid)
            ->where('read', false)
            ->groupBy('from')
            ->get();

       
        $contacts = $contacts->map(function($contact) use ($unreadIds) {
            $contactUnread = $unreadIds->where('sender_id', $contact->friends_id)->first();

            $contact->unread = $contactUnread ? $contactUnread->messages_count : 0;

            return $contact;
        });

    return response()->json($contacts);
        }

Received Table:-

 public function up()
    {
        Schema::create('received', function (Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->id();
            $table->string('user_id', 36);
            $table->unsignedBigInteger('friends_id');
            $table->string('list_no')->nullable();
            $table->string('name');
            $table->timestamps();
        });
    }

This is from where i am fetching unread message count:-

public function up()
    {
        Schema::table('messages', function (Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->boolean('read')->after('to', 36)->default(false);
        });
    }

    
    public function down()
    {
        Schema::table('messages', function (Blueprint $table) {
            $table->dropColumn('read');
        });
    }

Here’s Message table

public function up()
    {
        Schema::create('messages', function (Blueprint $table) {
           $table->engine = 'InnoDB';
            $table->id();
            $table->string('from', 36);
            $table->string('to', 36);
            $table->text('text');   
            $table->string('list_no')->nullable(); 
            $table->timestamps();
        });
    }

Can anyone please help me through this.

Answer

Looking closely at the sql error: the query formed is using “from” initially without any table name because you have used “from” as a column name which is SQL keyword for making queries.

Try a different column name may be “from_user” instead of “from” in your message table migrations and refresh the migrations to reflect changes in DB.

Also make the relevant column name changes in the code as well.

Note- Instead of changing old migration, you can also change the column name by creating a new migration and adding $table->renameColumn('from', 'from_user');