如何在 Laravel 查询构建器中注入自定义列 [英] How to injecting custom columns in Laravel query builder

问题描述

我收到了一个包含许多连接、位置等的查询.我需要做的是在每个结果集中插入一些数学运算，因为它将提供 csv 导出或显示在页面上.以后甚至可以作为API发回，所以我真正想做的是准备一次数据，然后随时随地使用它.

I got a query with many joins, wheres, ect. And what I need to do is insert some maths into each result set as it will be feeding either a csv export or be displayed on page. Can later on even be sent back as API, so what I really want to do is prepare the data once, and then use it where ever.

$result = DB::table('a')
->join('b')
->where('c')
->orderBy('d')
->select('e');

if ($paginate) {
    $query->paginate();
} else {
    $query->get();
}

所以问题是，我能否以某种方式迭代我的结果并在得到结果时进行一些数学运算?就像对每个结果进行回调一样?

So the question is, can I somehow iterate through my results and do some maths as I get them? Like maybe a callback on each result?

例如获取每行中检索到的某些值之间的差异，或添加表示通过/失败的附加行.基本上我想知道是否有更好的做事方式，然后在结果上执行 foreach() 以通过它们，进行数学运算并添加额外的列，从而破坏分页支持并且不得不将结果转换为一个丑陋的数组?

For example get difference between some values retrieved in each row, or add in additional row signifying pass/fail. Basically I was wondering if there was a better way of doing things then later on doing a foreach() on the results to go through them, do the maths and add in additional columns, thereby destroying the pagination support and having to convert the result into an ugly array?

推荐答案

我能想到三种方法.例如，假设您想获得 c 和 e 列之间的差异.

I can think of three methods. For example, let's say you want to get the difference between the columns c and e.

通过原始表达式，您还可以使用所有可用的 SQL 函数和普通的数学运算符.基本上你可以在普通的 SQL 选择中做任何你能做的事情，因为 Laravel 会直接将字符串插入到查询中.

With raw expressions you can use all available SQL functions and ordinary math operators as well. Basically you can do everything what you could in a normal SQL select because Laravel will insert the string directly into the query.

$result = DB::table('a')
    ->join('b')
    ->where('c')
    ->orderBy('d')
    ->select('e', DB::raw('c - e AS differenceCE'));

现在结果将有一个包含除法结果的 differenceCE 属性.

Now the result will have a differenceCE property containing the result of the division.

这只适用于 Eloquent 模型！

您可以在模型中创建一个新的动态属性，该属性将在您访问它时计算

You can create a new dynamic attribute in your model, that will be calculated the moment you access it

class MyModel extends Eloquent {
    protected $appends = array('difference');

    public function getDifferenceAttribute(){
        return $this->attributes['c'] - $this->attributes['e'];
    }
}

访问属性:

$mymodel->difference;

for(each)

您也可以使用一个简单的循环，例如:

for(each)

You can also use a simple loop like:

foreach($result as $model){
    // do math
}

或者，如果您正在使用 Eloquent，则可以在集合上调用 each 方法

Or if you're using Eloquent there's the each method you can call on a collection

$result->each(function($model){
    // do math
});

请注意，方法 1 和 2 可能会带来(稍微)更好的性能.SQL 只是因为它无论如何都必须遍历每条记录，并且带有属性访问器的方法具有延迟加载的优点.这意味着计算仅在您使用它时发生(当您访问该属性时)

Just be aware that method 1 and 2 may result in (slightly) better performance. SQL just because it has to go through every record anyways and the method with the attribute accessor has the advantage that it will be lazy loaded. Meaning the calculation only happens when you use it (when you access the property)

这篇关于如何在 Laravel 查询构建器中注入自定义列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！