Eloquent query not giving correct result

I have this query:

        $companyId                = Auth::user()->company_id;
        $userId                     = Auth::user()->id;
        $employeeId               = Auth::user()->employee_id;
        $employeeCode               = Auth::user()->employee_code;
        $identities                 = DB::table('appraisal_identity')->select('id')->where('company_id', $companyId)->where('is_current', 1)->first();

This is the query that should yield final result:

$goals                      = Goal::where('employee_id', $employeeId)->where('appraisal_identity_id', $identities->id)->where('is_approved', 3)->orWhere('is_visible', 0)->whereNull('deleted_at')->orderBy('goal_type_id')->get();

I want to display the goals dor the logged in empoyee (current user). In the goals table, for the employee, is_approved = 3 or is_visible =0.

I mean

$goals                      = Goal::where('employee_id', $employeeId)->where('appraisal_identity_id', $identities->id)->where('is_approved', 3)->orWhere('is_visible', 0)->whereNull('deleted_at')->orderBy('goal_type_id')->get();

is not giving the desired result But I see from the result that the query bypassed where('employee_id', $employeeId) and display the result for all the employees and not just the logged in user.

How do I correct this?

Thanks

click

5 years ago

Level 35

I guess this is related to your OR conditions. This is what you want right?

employee_id = A
appraisal_identity_id = B
is_approved = C OR is_visible

It should generate a query that looks like: WHERE "A" AND "B" AND ("C" OR "D")

Your query should represent this as well.

Goal::query()
  ->where('employee_id', 'A')
  ->where('appraisal_identity_id', 'B')
  ->where(function($query) {
      $query->where('is_approved', 'C');
      $query->orWhere('is_visible', 'D');
   });

Sinnbeck

5 years ago

Level 102

Try running to sql to see your actual query. It might be an issue with your orWhere

$sql                      = Goal::where('employee_id', $employeeId)->where('appraisal_identity_id', $identities->id)->where('is_approved', 3)->orWhere('is_visible', 0)->whereNull('deleted_at')->orderBy('goal_type_id')->toSql();
dd($sql);

MichalOravec

5 years ago

Best Answer

Level 75

$goals = Goal::where('employee_id', $employeeId)->where('appraisal_identity_id', $identities->id)->where(function ($query) {
    $query->where('is_approved', 3)->orWhere('is_visible', 0);
})->whereNull('deleted_at')->orderBy('goal_type_id')->get();

Docs: https://laravel.com/docs/8.x/queries#parameter-grouping

noblemfd

5 years ago

Level 9

Thanks so much everyone. Problem solved

Sinnbeck

5 years ago

Level 102

I suggest you study your queries to see where your error lies

noblemfd

5 years ago

Level 9

@sinnbeck - Alright. Thanks

Please or to participate in this conversation.