CODEWITHSUNDEEP
phplaraveleloquent
Yannick Dirbé
Yannick Dirbé

Reputation: 72

Laravel relationships many to many with recursive users table

I have two tables users and payments

The users table is as follows:

id
fisrtname
lastname

The payments table is as follows:

id
users_id
receiver_id

NB: users table is also receivers table.

I would like to have the list of users who have made a payment and not received any payment

Upvotes: 0

Views: 61

Answers (1)

Thai Nguyen Hung
Thai Nguyen Hung

Reputation: 1212

Please try my solution:


class User extends Authenticatable {
    ...
    public function payments()
    {
        return $this->hasMany(Payment::class, 'users_id');
    }
}

$users = \App\User::query()->getModel();
$users = $users->whereHas('payments', function ($query) {
    /** @var Builder $query */
    $query->havingRaw('COUNT(*) = 1');
})->whereNotExists(function ($query) {
    /** @var Builder $query */
    $query->select(['receiver_id'])
      ->from('payments')
      ->whereRaw('users.id = payments.receiver_id');
})->get();

Upvotes: 1

Related Questions