CODEWITHSUNDEEP
phplaravellaravel-5eloquentlaravel-4
scott
scott

Reputation: 3202

Laravel Relation where not in

I have following table

users 

id | username | password

1 | scott | 98746
2 | mark | 6542
3 | michel | 6589

user_detail

id | user_id | status | mobile_number 

1 | 1 | pending | 987643210

2 | 2 | review | 3216547901

Now i want to retrieve those record where user has no records in user_detail table where status=pending

I have tried using relations in latest version 

$user=User::with('userDetail')

        ->whereDoesntHave('userDetail',function ($query){

             $query->where('status','pending');
        })->get();

Same logic i am looking for without relations in laravel.Since we are using old laravel version which doesn't support.

Upvotes: 0

Views: 170

Answers (2)

leopinzon
leopinzon

Reputation: 712

I believe you can do this by using left joins, something like this: 

$rest = DB::table('users')
        ->leftJoin('userDetail','users.id','=','userDetail.user_id')
        ->whereNull('userDetail.id')
        ->orWhere('userDetail.status','=','pending')
        ->get();

If you need the eloquent collection of Users, you can use the hydrate method like this: 

$rest = User::hydrate(DB::table('users')
        ->leftJoin('userDetail','users.id','=','userDetail.user_id')
        ->whereNull('userDetail.id')
        ->orWhere('userDetail.status','=','pending')
        ->select('users.*')
        ->get()->toArray());

cheers!

Upvotes: 1

Martin Dimitrov
Martin Dimitrov

Reputation: 1304

I do not know what you mean by there are no relations, but if you have to do it with plain SQL, the query will look something like this:

$qry = "SELECT * FROM users WHERE id not in (SELECT u.id FROM users u INNER JOIN user_details d ON (u.id = d.user_id AND d.status = 'pending'));"

Then you can run the query by calling:

$results = DB::select($qry);

PS: Since I was not able to find it in the old docs, DB::select() may require 2nd param. If that is the case, just pass null as the second parameter.

EDIT: I am not sure if this will work, and since it is for an old version, I am unable to test it, but something similar should work:

$rest = User::whereNotIn('id', function($q){
    $q->select('user_id')->from('user_detail')->where('status','pending');
});

Upvotes: 1

Related Questions