Level 63
I don't really understand why you need a user_chats table with user_id and contact_id fields.
Jan 13, 2022
48
Level 1
I'm getting duplicated rows
I'm working on a small project (Internal Messaging System) using Laravel, I would like to get a list of users with who I'm talking.
I have 2 Models, User, Message and an intermediate Table called user_chats
Schema::create('user_chats', function (Blueprint $table) { $table->unsignedBigInteger('user_id'); // Auth::user()->id $table->unsignedBigInteger('contact_id'); // user who you chatted with either way $table->foreign(user_id')->references('id')->on('users'); $table->foreign('contact_id')->references('id')->on('users'); });
And I have a many to many relationship in my User model:
public function user_chats() { return $this->belongsToMany(User::class, 'user_chats', 'user_id','contact_id'); }
I'm fetching the list of users with who I'm talking like that :
Auth::user()->user_chats;
The problem is I'm getting duplicated rows, for example I contacted you and you contacted me back (same conversation) I should get only one row (your name) . But I'm getting result twice because I contacted you and you contacted me back. I need only one row contain your name. like Facebook inbox, left side you see only people who you contacted or they contacted you.
Thank you
Level 1
@vincent1500 I removed the table user_chats.
My new Schema is : `
Schema::create('messages', function (Blueprint $table) {
$table->increments('id');
$table->unsignedBigInteger('user_id');
$table->unsignedBigInteger('receiver_id');
$table->foreign('user_id')->references('id')->on('users');
$table->foreign('receiver_id')->references('id')->on('users');
$table->text('message')->nullable();
$table->timestamps();
});
`
Level 1
@vincent15000 Can you help me please. now I have just two tables & Models ( User & Messages ) How can I fetch the users with who I'm talking ( I mean who I contacted or they have contacted me )
Level 63
@AmineBHD Do you have the same problem with duplicate rows yet ?
Level 1
@vincent15000 I don't know what should I do now, I want to fetch a list of conversations ( users with who I'm talking ) I'm stack really I need a help Please
Level 1
@vincent15000 I need Just a list of users with who i'm talking already ( they contacted me Or I have contacted them )
Level 63
@AmineBHD If you need to retrieve the list of the users who talked with a specific user, you can try this query.
$user = Auth::user();
$users = User::has(['messages' => function($query) use ($user) {
$query->where('user_id', $user->id);
}])->get();
I have not tested this solution, but it should work.
Level 63
But to work, you have to write a has many relationship for the messages in your user model.
Level 1
@vincent15000 Error Call to a member function getRelationExistenceQuery() on array
Level 122
$messages = Message::where('user_id',Auth::id())->orWhere('receiver_id',Auth::id())->get();
Level 63
Level 1
@Snapey Duplicated resulats again ( because I'm contacting you and you are contacting me back )
Level 1
@vincent15000 Yes brother
Level 63
@AmineBHD Sorry ... here without the array, there was an error in my first proposition.
$user = Auth::user();
$users = User::has('messages', function($query) use ($user) {
$query->where('user_id', $user->id);
})->get();
Level 1
@vincent15000 strtolower() expects parameter 1 to be string, object given
Level 63
@AmineBHD Can you show the entire code of the method where you execute this query ?
Level 1
@vincent15000 User Model :
public function messages() { return $this->hasMany(Message::class); }
Controller :
$user = Auth::user(); $users = User::has('messages', function($query) use ($user) { $query->where('user_id', $user->id); })->get();
Level 63
@AmineBHD Sorry another error. It's not has() but whereHas() that you have to use.
$user = Auth::user();
$users = User::whereHas('messages', function($query) use ($user) {
$query->where('user_id', $user->id);
})->get();
Level 1
@vincent15000 It works but just if user_id ( is me ) what if the user_id ( was you ) if you contacted me first
Level 63
@AmineBHD Here you have the documentation about these methods.
https://laravel.com/docs/8.x/eloquent-relationships#querying-relationship-existence
Level 1
@vincent15000 I checked the documentation, but I couldn't find a solution. any way I thank you very mush for your effort
Level 63
@AmineBHD If it works, can you set the best answer please ?
I think that with the last solution, you can answer to you last question : you can just order your results by date and you will see if the first messages come from you or from another user.
Level 1
@vincent15000 Last answer doesn't works
if i contacted you than the user_id will be (1) and the receiver_id will be (2) which is you ( here it works fine ) but what if you contacted me first user_id (2) which is you and the receiver_id (1) which is me
Level 1
@vincent15000 Please try on your side to understand the situation more :)
Level 63
@AmineBHD You want the list of the users and not the messages, so the list remains the same, whatever user contact the other first.
Level 1
@vincent15000 I will share a screenshot with you now allow me 1 min please
Level 1
@vincent15000 Database : ibb.co/C613WgZ as you can see i'm talking to two persons ( 24 and 29 ) look the result now : ibb.co/TgzpnMV ( i'm getting only one array contain my self not the others )
Level 63
@AmineBHD Oh yes ok now I understand.
You have to retrieve the users having messages with either user_id or receiver_id equal to Auth::user()->id.
$user = Auth::user();
$users = User::whereHas('messages', function($query) use ($user) {
$query->where('user_id', $user->id)->orWhere('receiver_id', $user->id);
})->get();
Level 1
@vincent15000 Doesn't work again ( I'm getting two rows First is me id:1 and the second row is id: 24 ) while I need to get just 24 and 29
Level 63
@AmineBHD With this query, you should retrieve 1, 24 and 29. Can you do a dd($users) and show the result here ?
Level 63
@AmineBHD That's not readable ... what about dd($users->pluck('id')) ?
Level 1
` Illuminate\Support\Collection {#361 ▼ #items: array:2 [▼ 0 => 1 1 => 24 ] #escapeWhenCastingToString: false }
`
Level 1
#items: array:2 [▼ 0 => 1 1 => 24 ]
Level 63
@AmineBHD I have to test by myself. I will test tomorrow. Anyway if you want to exclude your own user, you have to add a where()clause to exclude Auth::user()->id.
$user = Auth::user();
$users = User::whereHas('messages', function($query) use ($user) {
$query
->where('user_id', '<>', $user->id)
->where(function($query) use ($user) {
$query
->where('user_id', $user->id)
->orWhere('receiver_id', $user->id);
});
})->get();
Level 63
@AmineBHD Does the 29 user exist ?
Level 1
@vincent15000 only 24 now
Level 63
@AmineBHD Well ... I mean ... in your users table, does the 29 user exist ?
Level 1
@vincent15000 Yes check here please : ibb.co/C613WgZ ( there is a conversation between user 1 and 29 )
Level 63
@AmineBHD I will test this tomorrow and I tell you something.
Level 122
to get the users;
$ids= Message::query()
->select('user_id', 'receiver_id')
->where('user_id', Auth::id())
->orWhere('receiver_id', Auth::id())
->get()
->map(function($message) {
return $message['user_id'] == Auth::id()
? $message['receiver_id']
: $message['user_id'];
})
->unique();
$users = User::whereIn('id',$ids)->get();
1 like
Level 63
Level 102
@Snapey missing a ->get() :)
2 likes
Level 1
@Snapey Too few arguments to function Illuminate\Database\Query\Builder::whereIn(), 1 passed in C:\laragon\www\chat\vendor\laravel\framework\src\Illuminate\Support\Traits\ForwardsCalls.php on line 23 and at least 2 expected
Level 122
@AmineBHD I updated the example
Level 63
@Snapey I'd like to understand why you have the query() close at the beginning of the query ? Is it absolutely necessary ?
Level 102
@vincent15000 Yup add that to get a query builder instance. It isn't necessary but it can help your IDE with autocomplete.
1 like
Level 63
@Sinnbeck Thank you ;).
Level 1
@vincent1500 @snapey thanks both of you guys God bless
Please or to participate in this conversation.