Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – Trouble fetching unread messages from table with php query, lost a bit

So having trouble fetching the unread messages and counting them from the authenticated user in a Laravel project.

I went the traditional PHP route to try and get this working but to no avail have been working on it for a couple days and only failing.

I am calling it from a blade file.

  <?php
      $messages = DB::table('messages')
              ->where('read', false)
              ->where('receiver_id' ==  Auth::id())
              ->get();
      $counter = 0;
      foreach ($messages as $message) {

        $counter++;
      }
      echo $counter;
      ?>

I cant get it working in anyway and as of now i am getting this error
:

local.ERROR: SQLSTATE[42S22]: Column not found: 1054 Unknown column ”
in ‘where clause’ (SQL: select * from messages where read = 0 and
“ is null)

My messages table structure:

   public function up()
{
    Schema::create('messages', function (Blueprint $table) {
        $table->uuid('id');
        $table -> longText('content_sender');
        $table -> longText('nonce_sender');
        $table -> longText('content_receiver');
        $table -> longText('nonce_receiver');
        $table -> uuid('conversation_id');
        $table -> uuid('sender_id') -> nullable();
        $table -> uuid('receiver_id');
        $table -> boolean('read') ->default(false);
        $table->timestamps();

        $table -> primary('id');
        $table -> foreign('conversation_id') -> references('id') -> on('conversations') -> onDelete('cascade');
        $table -> foreign('sender_id') -> references('id') -> on('users') -> onDelete('cascade');
        $table -> foreign('receiver_id') -> references('id') -> on('users') -> onDelete('cascade');
    });
}

Would really appreciate any pointer to get this working fast as its really holding me up.

Thanks folks.

Tags:

2

Answers


  1. 0

    I think the issue is here : ->where('receiver_id' == Auth::id())
    Where takes the column name and the value read more, here you are passing the result of 'receiver_id' === Auth::id() which is false, the code should be like this :

     <?php
      $messages = DB::table('messages')
              ->where('read', false)
              ->where('receiver_id',  Auth::id())
              ->get();
      $counter = 0;
      foreach ($messages as $message) {

        $counter++;
      }
      echo $counter;
      ?>
    Login or Signup to reply.
  2. 0

    If you only want to get the unread messages this can be get with eloquent like:

    $unread_messages = Message::where([
['receiver_id', Auth::id()],
['read',false]
])
->withCount('messages')
->get();

    or

    $unread_messages = Message::where('receiver_id', Auth::id())->withCount('messages',function($query){
   $query->where('read',false);
})->get();

echo $unread->count_messages
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search