How to injecting custom columns in Laravel query builder

Question

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?

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?

lukasgeiter · Accepted Answer · 2014-12-29 11:42:28Z

6

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

Select with raw expressions

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'));

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

Attribute accessors

This only works with Eloquent Models!

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'];
    }
}

Access the property:

$mymodel->difference;

for(each)

You can also use a simple loop like:

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

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

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

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)

edited Dec 29, 2014 at 11:42

answered Dec 24, 2014 at 13:15

lukasgeiter

154k29 gold badges349 silver badges280 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

OwlPaste Over a year ago

Thanks for the reply, I was hoping for something a little bit more dynamic, difference was just an example and I need to work out % or other things. Not quiet sure doing a db::raw is that efficient? I was kinda hoping for a callback you can call on each result that has access to the values? Does something like that exist?

lukasgeiter Over a year ago

I updated my answer. Also you don't need to worry about DB::raw it's only purpose is to write SQL that get's inserted directly (so you can do the math with SQL)

OwlPaste Over a year ago

I see thanks, that's pretty much what I am doing at the moment. Looping through results and applying whatever else I wanted. Just it always strikes me as somewhat... inefficient :)

lukasgeiter Over a year ago

Well think about it. You wanted a "callback you can call on each result". How do you think would that (internally) work? A loop. And yes it is somewhat inefficient. SQL would be better performance wise ;)

Collectives™ on Stack Overflow

How to injecting custom columns in Laravel query builder

1 Answer 1

Select with raw expressions

Attribute accessors

for(each)

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Select with raw expressions

Attribute accessors

for(each)

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related