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

Set Max value in Where condition in Laravel

I need to get role_id from a preference table with some conditions, including it should not belong to the maximum level column. Here is the code,

$maxLevel = ApprovalPreference::where('company_id', $this->company_id)
    ->max('level'); // Can this line be avoided???
$role = ApprovalPreference::where('company_id', $this->company_id)
    ->where('level', '>', $this->approval_level)
    ->where('level', '<', $maxLevel)
    ->orderBy('level', 'asc')
    ->pluck('role_id')
    ->first();

The above code works, but is there a way to incorporate the line which fetches the maximum value to the main query?
I tried the raw query but it did not work,

->where('level', '<', DB::raw('select MAX(level) from approval_preferences')

The above code did not work, it threw some errors.

EDIT

The above code worked when I wrapped the select statement in another set of paranthesis.

->where('level', '<', DB::raw('(select MAX(level) from approval_preferences)')

END

Eventually I used whereRaw method,

->whereRaw("level > (SELECT MAX(level) from approval_preferences where company_id = {$this->company_id})")

keeping the question undeleted considering the others who tried to help me out

Tags:

2

Answers


  1. 0

    Here I have used a subquery to filter data, try this:

    $role = ApprovalPreference::where('company_id', $this->company_id)
    ->where('level', '>', $this->approval_level)
    ->where('level', '<', function ($query) {
        $query->select(DB::raw('MAX(level)'))
            ->from('approval_preferences')
            ->where('company_id', $this->company_id);
    })
    ->orderBy('level')
    ->pluck('role_id')
    ->first();
    Login or Signup to reply.
  2. 0

    The solution for you is to use whereRaw

    $role = ApprovalPreference::where('company_id', $this->company_id)
    ->where('level', '>', $this->approval_level)
    ->whereRaw("level < (SELECT MAX(level) from table_approval_preference WHERE company_id = $this->company_id"))
    ->orderBy('level', 'asc')
    ->pluck('role_id')
    ->first();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search