在Laravel中设置Where条件的最大值。

huangapple go评论120阅读模式
英文:

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(&#39;company_id&#39;, $this-&gt;company_id)
    -&gt;max(&#39;level&#39;); // Can this line be avoided???
$role = ApprovalPreference::where(&#39;company_id&#39;, $this-&gt;company_id)
    -&gt;where(&#39;level&#39;, &#39;&gt;&#39;, $this-&gt;approval_level)
    -&gt;where(&#39;level&#39;, &#39;&lt;&#39;, $maxLevel)
    -&gt;orderBy(&#39;level&#39;, &#39;asc&#39;)
    -&gt;pluck(&#39;role_id&#39;)
    -&gt;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,

-&gt;where(&#39;level&#39;, &#39;&lt;&#39;, DB::raw(&#39;select MAX(level) from approval_preferences&#39;)

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.

-&gt;where(&#39;level&#39;, &#39;&lt;&#39;, DB::raw(&#39;(select MAX(level) from approval_preferences)&#39;)

END

Eventually I used whereRaw method,

-&gt;whereRaw(&quot;level &gt; (SELECT MAX(level) from approval_preferences where company_id = {$this-&gt;company_id})&quot;)

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(&#39;company_id&#39;, $this-&gt;company_id)
    -&gt;where(&#39;level&#39;, &#39;&gt;&#39;, $this-&gt;approval_level)
    -&gt;where(&#39;level&#39;, &#39;&lt;&#39;, function ($query) {
        $query-&gt;select(DB::raw(&#39;MAX(level)&#39;))
            -&gt;from(&#39;approval_preferences&#39;)
            -&gt;where(&#39;company_id&#39;, $this-&gt;company_id);
    })
    -&gt;orderBy(&#39;level&#39;)
    -&gt;pluck(&#39;role_id&#39;)
    -&gt;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(&#39;company_id&#39;, $this-&gt;company_id)
    -&gt;where(&#39;level&#39;, &#39;&gt;&#39;, $this-&gt;approval_level)
    -&gt;whereRaw(&quot;level &lt; (SELECT MAX(level) from table_approval_preference WHERE company_id = $this-&gt;company_id&quot;))
    -&gt;orderBy(&#39;level&#39;, &#39;asc&#39;)
    -&gt;pluck(&#39;role_id&#39;)
    -&gt;first();

huangapple
  • 本文由 发表于 2023年3月7日 14:58:49
  • 转载请务必保留本文链接:https://go.coder-hub.com/75658833.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定