英文:
Set Max value in Where condition in Laravel
问题
我需要从具有某些条件的偏好表中获取role_id，其中包括它不应该属于最大的level列。以下是代码，
$maxLevel = ApprovalPreference::where('company_id', $this->company_id)
->max('level'); // 这一行能否避免???
$role = ApprovalPreference::where('company_id', $this->company_id)
->where('level', '>', $this->approval_level)
->where('level', '<', $maxLevel)
->orderBy('level', 'asc')
->pluck('role_id')
->first();
上述代码有效，但是否有一种方式将获取最大值的行合并到主查询中？
我尝试了原始查询，但它不起作用，
->where('level', '<', DB::raw('select MAX(level) from approval_preferences'))
上述代码不起作用，它引发了一些错误。
编辑
当我将选择语句包装在另一组括号中时，上述代码起作用。
->where('level', '<', DB::raw('(select MAX(level) from approval_preferences)'))
最终，我使用了whereRaw方法，
->whereRaw("level > (SELECT MAX(level) from approval_preferences where company_id = {$this->company_id})")
保留此问题未删除，以考虑其他尝试帮助我的人
英文:
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
答案1
得分: 4
在这里，我使用了一个子查询来筛选数据，试试这个：
$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();
这是您提供的代码的翻译部分。
英文:
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();
答案2
得分: -2
你可以使用 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();
英文:
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();
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论