Question 16288 in Laravel Question posted in
The official Laravel Documentation can be found here.

How to write below query in Laravel

I have a table and there are two columns.

user_id to_user_id
101 102
102 101
101 105

In the above table, 1 and 2 rows of users are like each other I want to remove 2 rows.

Here is my code.

$query = AppModelsMyMatch::query();
$query->whereHas('users')->whereHas('tousers')
    ->select('my_matches.*')->distinct()
    ->where('user_id', '!=', 'to_user_id');
$records = $query->get();

In the above code, I got total 3 rows

I want only a total of 2 rows 1 and 3

So, how can I write a query for it

Tags:

2

Answers


  1. 0 
    $records = AppModelsMyMatch::whereHas('users')->whereHas('tousers')
->where(function($query) {
    $query->where('user_id', '!=', 'to_user_id')
        ->orWhere(function($query) {
            $query->where('user_id', '<', 'to_user_id');
        });
})
->distinct()
->get();

    or use a subquery :

    $subquery = DB::table('my_matches')
                ->select('user_id', 'to_user_id')
                ->whereColumn('user_id', '>', 'to_user_id')
                ->toSql();

$records = AppModelsMyMatch::whereHas('users')->whereHas('tousers')
    ->whereNotExists(function($query) use ($subquery) {
        $query->select(DB::raw(1))
                ->from(DB::raw("($subquery) as sub"))
                ->whereRaw('my_matches.user_id = sub.to_user_id and my_matches.to_user_id = sub.user_id');
    })
    ->get();
    Login or Signup to reply.
  2. 0

    You can use

    $query = AppModelsMyMatch::query();
$query->whereHas('users')->whereHas('tousers')
    ->select('my_matches.*')
    ->where('user_id', '!=', 'to_user_id')
    ->groupBy('to_user_id')
    ->havingRaw('COUNT(*) = 1');
$records = $query->get();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search