tomspeak
2 weeks ago
134
5
General

"Friendships" - bidirectional many-to-many relationship

Posted 2 weeks ago by tomspeak

I am trying to enable two user's connecting, or becoming "friends", or "connections".

So I made a pivot table connections

Schema::create('connections', function (Blueprint $table) {
    $table->id();

    $table->unsignedBigInteger('from_user_id');
    $table->unsignedBigInteger('to_user_id');
    $table->enum('status', ConnectionStatusEnum::getValues())->default(ConnectionStatusEnum::Pending());
    $table->timestampTz('accepted_at')->nullable();

    $table->foreign('from_user_id')->references('id')->on('users')->onDelete('cascade')->onUpdate('cascade');
    $table->foreign('to_user_id')->references('id')->on('users')->onDelete('cascade')->onUpdate('cascade');

    $table->timestampsTz();
    $table->softDeletes();
});

Initially, it is a one-way relationship, with from_user_id asking to_user_id to accept the request, which would move the status to Enum::Accepted(), and accepted_at to Carbon::now().

Once a connection has been accepted, it now needs to be bi-directional. By that, I mean it does not matter who initiated the connection now, they're connected and that is all that matters.

I have got part of the way there, but cannot get both directions work, below is non-working code

User.php

    public function connections()
    {
        return $this->belongsToMany(User::class, 'connections', 'from_user_id', 'to_user_id')
            ->wherePivot('status', '=', (string) ConnectionStatusEnum::Accepted())
            ->orWhere('connections.from_user_id', $this->id)
            ->orWhere('connections.to_user_id', $this->id)
            ->withPivot('status')
            ->using(Connection::class);
    }

Which creates the SQL

SELECT
	"users".*,
	"connections"."from_user_id" AS "pivot_from_user_id",
	"connections"."to_user_id" AS "pivot_to_user_id",
	"connections"."status" AS "pivot_status"
FROM
	"users"
	INNER JOIN "connections" ON "users"."id" = "connections"."to_user_id"
WHERE ("connections"."from_user_id" = 1
	AND "connections"."status" = '2'
	OR "connections"."from_user_id" = 1
	OR "connections"."to_user_id" = 1)
AND "users"."deleted_at" IS NULL;

This means that only one side of the relationship is joined, who ever is in to_user_id. I need an extra join to check if the current user is in from_user_id.

I have looked at other solutions and ideally, would avoid making two queries that are then merged, or keeping two records per relationship.

Any ideas?

Please sign in or create an account to participate in this conversation.