How to get only unique data from DB in Laravel?

Question

PostgreSQL

The bottom line is that there is a table in the database, there are two important columns, these are "traiff_id" and "status"

Model::where([
                ['status', '=', 'CLOSED'],
                ['user_id', '=', $user->id]
            ])
            ->get()
            ->groupBy('id')
            ->toArray();

I make a selection, everything is okay with that. But the bottom line is that I only need to get those rows where "status = CLOSED", given the fact that the table might look like this

id     tariff_id           status

1	        1 	        	CLOSED
2	        1		        ACTIVE
3	        2		        CLOSED

And in this case, I need to get only the 3rd record, because the first and second records have the same "tariff_id", but one of these records has the status not CLOSED
In short, I need to get only those rows where, with the same "tariff_id", status is CLOSED

Answer 1

I don't know how to pull out unique requests to the database. Probably easy and googling. In a collection, you can use the unique() method . If Model is of type Payment, then in User you need to make a hasMany relation

public function payments() {
   return $this->hasMany('App\Models\Payment','user_id', 'id');
}

And the request itself in the controller

$status = 'CLOSED';
$payments = $user->payments()
                 ->where('status', $status)
                 ->get()
                 ->unique('tariff_id');

Answer 2

select * from name_tables as models1  where not exists((SELECT * FROM name_tables as models2 where models2.tariff_id = models1.tariff_id and models2.status != CLOSED))