CODEWITHSUNDEEP
laravelinner-joinjoin
swm
swm

Reputation: 539

How to do join table by ID as primary key in query -laravel

I want to do a function where filter based on 2 database tables. However, Im not sure how to put the join table in the query. Which means the data will be filtered from two table (user and employee tables) before returning the result to the datatable.

My filter query is 

public function filterQuery(Request $request){

    $age = $request->age;
    $gender= $request->gender;

    $query = user::query();

    if(!empty($request->age)){
        $query->where('age','>=',$age );
    }

    if(!empty($request->gender)){
        $query->where('gender','<=',$gender);
    }

    $data = $query->get();

    return datatables()->of($data)->make(true);
}

The table that I want to join in the query is from table employee (column = income and house_ownership) .and the primary key that connect both tables is IC.

Upvotes: 3

Views: 1158

Answers (3)

TsaiKoga
TsaiKoga

Reputation: 13394

If you have relationship in both model, you can use whereHas:

    if(!empty($request->income) || !empty($request->house_ownership)){
        $query->whereHas('employee', function($q) use ($income, house_ownership) {
             if (!empty($income)) {
                  $q->where('income', $income);
             }
             if (!empty($house_ownership)) {
                  $q->where('house_ownership', $house_ownership);
             }             
        });
    }
...

Or you can just use join or leftjoin to filter another table:

public function filterQuery(Request $request){

    $age = $request->age;
    $gender= $request->gender;
    $house_ownership = $request->house_ownership;
    $income= $request->income;

    $query = user::query();
    $query->leftjoin('employee', 'employee.IC', '=', 'user.IC');

    if(!empty($request->age)){
        $query->where('user.age','>=',$age );
    }

    if(!empty($request->gender)){
        $query->where('user.gender','<=',$gender);
    }

    if(!empty($request->income)){
        $query->where('employee.income', $income);
    }
    if(!empty($request->house_ownership)){
        $query->where('employee.house_ownership', $house_ownership);
    }
    $data = $query->select('user.*')->get();

    return datatables()->of($data)->make(true);
}

Upvotes: 2

nick huang
nick huang

Reputation: 191

You can use Eloquent::when() to reduce if-else for Conditional Queries.

public function filterQuery(Request $request){

    $query = user::query();
    $query->select('user.*')->join('employee', 'employee.IC', '=', 'user.IC');

    $data = $query
        ->when(request('age') != null , function ($q) {
            return $query->where('user.age','>=',request('age'));
        })
        ->when(request('gender') != null , function ($q) {
            return $query->where('user.gender','<=',request('gender'));
        })
        ->when(request('income') != null , function ($q) {
            return $query->where('employee.income','<=',request('income'));
        })
        ->when(request('house_ownership') != null , function ($q) {
            return $query->where('employee.house_ownership','<=',request('house_ownership'));
        })
    ->get();

    return datatables()->of($data)->make(true);
}

Upvotes: 1

VIKAS KATARIYA
VIKAS KATARIYA

Reputation: 6005

Try This

public function filterQuery(Request $request){

    $age = $request->age;
    $gender= $request->gender;
    if(!empty($request->age)){
       $data = User::where('age','>=',$age)->get();
    }

    if(!empty($request->gender)){
      $data =  User::where('gender','<=',$gender)->get();
    }


    return datatables()->of($data)->make(true);
}

Upvotes: 1

Related Questions