you can use scopes https://laravel.com/docs/9.x/eloquent#local-scopes
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.
Please or to participate in this conversation.