Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

revilcv's avatar

What is best way to shorten my eloquent query?

I really need help with this ugly, long eloquent query I have written, which includes too many WHERE clauses. At this point, to be honest, I'm so confused that I don't even know what this code suppose to do! But I try my best to describe it as clear as possible!

There are two tables named Users and Contacts. Every user can send contact request to other users AND "SEND Request" button will be visible and active, under specific conditions:

1 - USER_1 must not be in the USER_2 contacts list OR VICE VERSA.

2 - USER_1 can send contact request TO USER_2 IF there is no previous/pending contact request from USER_2 or vice versa (USER_2 can send request TO USER_1 IF there is no pending contact request from USER_1)

3 - IF USER_1 has sent 3 contact requests to USER_2 but all of them got rejected by USER_2, THEN USER_1 IS unable to send anymore contact requests OR VICE VERSA!

At this stage, I did not cover conditions for block list to make it simpler for myself but instead it got even more complicated!

This is my USER_Table structure:

Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('uid')->unique();
            $table->string('fname');
            $table->string('sname');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->string('phone')->nullable();
            $table->timestamp('phone_verified_at')->nullable();
            $table->string('gender');
            $table->date('bdate');
            $table->string('avatar')->default('default.png');
            $table->string('descriptions', 500)->nullable();
            $table->rememberToken();
            $table->timestamps();
});

And this is the CONTACTS_Table structure:

Schema::create('contacts', function (Blueprint $table) {
            $table->id();
            $table->string('public_id')->unique();
            $table->foreignId('user_id')->constrained("users")->onUpdate('cascade')->onDelete('cascade');
            $table->foreignId('target_id')->references("id")->on('users')->onDelete('cascade');
            $table->integer('attempts')->default(0);
            $table->string('status', 10)->default('pending');
            $table->timestamps();
});

And this is eloquent query I used:

User::with('incomingContactRequest')
                ->where('user_id', auth()->user()->id)
                ->where('target_id', $target->id)
                ->where('status', '!=',['accepted', 'pending'])
                ->where('attempts', '<', '2')
                ->orWhere('status', 'rejected')
                ->where('user_id', $target->id)
                ->orWhere('target_id', auth()->user()->id)
                ->where('status', '!=', ['accepted', 'pending'])
                ->where('attempts', '<', '2')
                ->orWhere('status', 'rejected')
                ->exists() || !User::with('incomingContactRequest')
                ->where('user_id', auth()->user()->id)
                ->where('target_id', $target->id)
                ->orWhere('user_id', $target->id)
                ->where('target_id', auth()->user()->id)
                ->exists();

I also have these two eloquent relationships there in the Users model:

public function outgoingContactRequest () {
        return $this->hasMany(Contact::class, 'user_id', 'id','target_id');
}

public function incomingContactRequest () {
        return $this->hasMany(Contact::class, 'target_id', 'id', 'user_id');
}

I also tried to use this method but I failed since there is not EXISTS method in this way:

$user = Auth::user();
$user->incomingContactRequest->where('status', '=', ['accepted', 'pending'])->exists();

If anybody can, please help me. Thank you.

0 likes
3 replies
furqanDev's avatar

You can leverage Query Scopes for this.

For example if you are frequently using some clauses then you can extract it to query scope in your model.

public function scopeTarget($query)
{
	$query->where('user_id', auth()->user()->id)
		->where('target_id', $target->id)
		->where('user_id', $target->id);
}

Then you can use it like

User::target()->get();

In your case query will be reduced to from this

User::with('incomingContactRequest')
                ->where('user_id', auth()->user()->id)
                ->where('target_id', $target->id)
                ->where('status', '!=',['accepted', 'pending'])
                ->where('attempts', '<', '2')
                ->orWhere('status', 'rejected')
                ->where('user_id', $target->id)
                ->orWhere('target_id', auth()->user()->id)
                ->where('status', '!=', ['accepted', 'pending'])
                ->where('attempts', '<', '2')
                ->orWhere('status', 'rejected')
                ->exists()

To this

User::with('incomingContactRequest')
				->target()
                ->where('status', '!=',['accepted', 'pending'])
                ->where('attempts', '<', '2')
                ->orWhere('status', 'rejected')
                ->orWhere('target_id', auth()->user()->id)
                ->where('status', '!=', ['accepted', 'pending'])
                ->where('attempts', '<', '2')
                ->orWhere('status', 'rejected')
                ->exists()

If you want to add other clause then you can do it as well. You can check the documentation as well.

https://laravel.com/docs/9.x/eloquent#local-scopes

revilcv's avatar

@furqanDev Thank you for your help but can I access Contacts table from User model in scopes?

Please or to participate in this conversation.