I want to concatenate two user names in a BelongsToMany relationship

If you're trying to search the user name in the database with concatenation, then it will work.

$builder->where(DB::raw('CONCAT_WS(" ",firstname,lastname)'), 'LIKE', '%Firstname Lastname%');

Noted that firstname and lastname both are different columns.

searching for the name of that channel but I am having problems concatenating two user names

well, unless you have another pivot table, the query in the DB::raw only result john_john not john_maria.

it's just repeated same name.

but would you like to explain more what really this query do ?

why you concat 2 user name in one channel ?

what the condition of this channel ?

I think your pivot table needs another column to hold the channel name it pivots the user to.

For instance:

channels [id, created_at, updated_at]

users [id, name]

channel_user [channel_id, user_id, channel_name]

And that 'channel_name' column would hold the concatenated name and would be inserted with the sync method (or attach) as follows...

The model relationships would need the 'withPivot()' method to point to the additional column added to the pivot table.

// Channel.php
class Channel extends Model
{
    public function users()
    {
        return $this->belongsToMany(User::class, 'channel_user')->withPivot('channel_name');
    }
}

// User.php
class User extends Model
{
    public function channels()
    {
        return $this->belongsToMany(Channel::class, 'channel_user')->withPivot('channel_name');
    }
}

And then to actually sync the channel and user together alongside the channel_name...

// Syncing
$channel = Channel::find($channelId);
$channel->users()->sync([
    $userId => ['channel_name' => $user1->name . '_' . $user2->name],
    // more if you doing multiple
]);

// Updating
$channel->users()->updateExistingPivot($userId, ['channel_name' => 'new_value']);

// Removing
$channel = Channel::find($channelId);
$channel->users()->detach($userId);

I have a list of channels displayed with the name channel (which is the name of two users concatenated together)

What two users are concatenated as the channel name? If the channel has ten users, which two are supposed to be concatenated? And what if a channel only has one user? And how do you determine whether your channel is called john_maria or maria_john? Are your user-to-channel associations inherently ordered? What if several channels have the same two users and end up with the same name?

So far, everything you’ve written indicates that the channel name is not stored in the database. So why are you trying to search for it in the database if it’s not stored there, especially when it’s not even clear how the concatenated value is actually generated?

As others have said, the right way to do what you’re trying to do is to store the channel name in the database. That way, you also don’t have to generate the name on the fly each time you show or fetch the channel – only when you attach or detach users.

I disagree with part of @bogey’s answer, though: the correct place to store the channel name is in the channels table, directly on the channel model – not in the pivot table. Otherwise, each channel will have a (potentially different) name for each of its associated users.

Unfortunately, there doesn’t seem to be an easy, built-in way to automatically update the name whenever a row in the pivot table is added or deleted from either a channel or user model – the easiest way would probably be to use a custom pivot model, and then in that model call an update method on the parent channel when the created or deleted model is fired:

// app/Models/Channel.php
public function users() {
	return $this->belongsToMany(User::class)->using(ChannelUser::class);
}

public function updateName() {
	$this->name = $this->users->pluck('name')->sort()->join('_');
}

// app/Models/User.php
public function channels() {
	return $this->belongsToMany(Channel::class)->using(ChannelUser::class);
}

// app/Models/ChannelUser.php
namespace App\Models;

use Illuminate\Database\Eloquent\Relations\Pivot;

class ChannelUser extends Pivot {
	public $timestamps = FALSE;

	protected static function booted() : void {
		static::created(fn (self $pivot) => $pivot->updateChannelName());
		static::deleted(fn (self $pivot) => $pivot->updateChannelName());
	}

	public function updateChannelName() {
		if ($this->pivotParent instanceof Channel) {
			// The user was added from a channel model with `$channel->users()->attach()`
			$channel = $this->pivotParent;
		} else {
			// The channel was added from a user model with `$user->channels()->attach()`
			$channel = Channel::find($this->channel_id);
		}

		$channel->updateName();
	}
}

Note: The questions I asked above about how to determine the channel name from the users are still pertinent. I’ve simply assumed here that it should be generated based on the same principles Laravel expects pivot tables to adhere to: the name property of all associated users, alphabetised and joined with an underscore. You should of course use whatever logic you already have for this.