I'm getting messages twice, duplicated-CodePudding

I'm working on a Laravel Chat application, I have a problem, I'm getting each message twice (duplicated)

this is my code:

return Chat::join('users',  function ($join) {
                $join->on('chats.from_id', '=', 'users.id')
                    ->orOn('chats.to_id', '=', 'users.id');
            })->where(function ($q) {
                $q->where('chats.from_id', Auth::id())->orWhere('chats.to_id', Auth::id());
            })
            ->orderBy('chats.created_at', 'desc')->select('users.id as user_id', 'chats.id as chat_id', 'message')->get();

CodePudding user response：

As @mohammad-mirsafaei stated in his answer:

It's because you're joining chats and users table twice.

To resolve this:

Instead of...

->orOn('chats.to_id', '=', 'users.id');

Use this...

->orWhere('chats.to_id', '=', 'users.id');

A more complete answer would be:

2a). Add these two "relationship" methods to your Chat.php model.

// ...
    public function from() {
        return $this->belongsTo(User::class, "from_id");
    }
    public function to() {
        return $this->belongsTo(User::class, "to_id");
    }
// ...

2b). Add the method responsible for executing your query to your Chat.php model.

// ...
    public static function chats() {
        return Chat::join('users',  function ($join) {
            $join->on('chats.from_id', '=', 'users.id')
                ->orWhere('chats.to_id', '=', 'users.id');
        })->where(function ($q) {
            $q->where('chats.from_id', Auth::id())->orWhere('chats.to_id', Auth::id());
        })
            ->with(["from" => function($query) {
                return $query->select(["id", "name"]);
            }])
            ->with(["to" => function($query) {
                return $query->select(["id", "name"]);
            }])
            ->orderBy('chats.created_at', 'desc')->select('from_id', 'to_id', 'chats.id as chat_id', 'message')->get();
    }
// ...

Sample output:

Ivan@DESKTOP-LQIK691 MINGW64 /c/xampp/htdocs/breezep (master)
$ php artisan tinker
Psy Shell v0.10.6 (PHP 8.0.2 — cli) by Justin Hileman
>>> App\Models\Chat::chats()
=> Illuminate\Database\Eloquent\Collection {#4387
     all: [
       App\Models\Chat {#4381
         from_id: 2,
         to_id: 3,
         chat_id: 5,
         message: "No, I didn't!",
         from: App\Models\User {#4388
           id: 2,
           name: "stevenmwesigwa",
         },
         to: App\Models\User {#4393
           id: 3,
           name: "john ripper",
         },
       },
       App\Models\Chat {#4382
         from_id: 3,
         to_id: 2,
         chat_id: 4,
         message: "The day went well. Did you go to the supermarket today?",
         from: App\Models\User {#4390
           id: 3,
           name: "john ripper",
         },
         to: App\Models\User {#4391
           id: 2,
           name: "stevenmwesigwa",
         },
       },
       App\Models\Chat {#4383
         from_id: 3,
         to_id: 2,
         chat_id: 3,
         message: "I'm fine  how're you?",
         from: App\Models\User {#4390},
         to: App\Models\User {#4391},
       },
       App\Models\Chat {#4384
         from_id: 2,
         to_id: 3,
         chat_id: 2,
         message: "How was your day?",
         from: App\Models\User {#4388},
         to: App\Models\User {#4393},
       },
       App\Models\Chat {#4385
         from_id: 2,
         to_id: 3,
         chat_id: 1,
         message: "Hello Johny",
         from: App\Models\User {#4388},
         to: App\Models\User {#4393},
       },
     ],
   }
>>>

CodePudding user response：

It's because you're joining chats and users table twice. Once on from_id and once on to_id so it will result in duplicate results. To fix it you get unique rows or union two separate queries either by php code or mysql query.