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 frommessages
whereto
= d3c364bb-0982-46ba-869a-24dbb2c50aea andread
= 0 group byfrom
)
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.
Advertisement
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');