How to assign a dynamic property in a model via a SQL query in Laravel 5

Question

I'm using Laravel 5.3 to build an API and I have an model for products. Whenever I retrieve a product, I want to retrieve the product's rating and it's recommended rate. I also have a model for reviews and products have many reviews.

$product = Product::where('slug', $slug)->with('reviews')->first()->toArray();

Rating is computed by looping through $product->reviews in the controller, adding up the score of each review, then dividing it by the total number of reviews.

if (count($product['reviews']) > 0) {
    $i = 0;
    $totalScore = 0;
    foreach ($product['reviews'] as $review) {
        $totalScore = $totalScore + $review['Rating'];
        $i++;
    }
    $product['averageReviewRating'] = $totalScore / $i;
} else {
    $product['averageReviewRating'] = null;
}

Recommended rate is computed with a SQL query.

$product['recommendedRate'] = round(DB::select("
    select ( count ( if (Recommend = 1,1,NULL) ) / count(*)) * 100 as rate 
    from Review where PrintProduct_idPrintProduct = " . $product['idPrintProduct']
)[0]->rate);

This leaves me with $product['averageReviewRating'] and $product['recommendedRate'] with the data I want but seems very sloppy. I would like to just be able to do something similar to this below and have those two values assigned to each object of a collection, than access them via $product->averageReviewRating and $product->recommendedRate or even not include them in with and have those values eagerly assigned.

$product = Product::where('slug', $slug)->with(['Reviews', 'RecommendedRate', 'AverageReviewRating'])->first();

Anyone know a way to do this with ORM? I've looked high and low and have not found anything.

Im on my phone or would leave an example but generally that sort of logic should be on the model. Then you can do a custom attribute on the product model and loop your review records in there. To further improve on that that you could just query the reviews directly using the product model id as the select criteria and perform the average calculation in mysql — Mike Miller
– Mike Miller, Commented Feb 2, 2017 at 21:16
How you are calculating Recommended rate. if you can explain i can give the clean answer using ORM — Vikash
– Vikash, Commented Feb 2, 2017 at 21:30
@Vikash the query is right in the question. An answer can be agnostic to how I'm doing it though. I just want to run a query and assign the value it returns to a dynamic property every time I call the model. — wwwroth
– wwwroth, Commented Feb 2, 2017 at 21:30

Avik Aghajanyan · Accepted Answer · 2017-02-02 21:42:37Z

1

You can do this way

protected $appends = [
 'reviews',
 'recommendedRate',
 'averageReviewRating'
];

public function getReviewsAttribute() {
 return $this->reviews()->get();
}

public function getRecommendedRateAttribute() {
 if (count($this->reviews) > 0) {
    $i = 0;
    $totalScore = 0;
    foreach ($this->reviews as $review) {
        $totalScore = $totalScore + $review->Rating;
        $i++;
    }
    return $totalScore / $i;
  } else {
    return null;
  }
}

public function getAverageReviewRatingAttribute() {
 return round(DB::select("
    select ( count ( if (Recommend = 1,1,NULL) ) / count(*)) * 100 as rate 
    from Review where PrintProduct_idPrintProduct = " . $this->idPrintProduct
)[0]->rate);
}

then simply call Product::where('slug', $slug)->first()->toArray();

P.S. This is just the way you can do, I might miss part of logic or names..

answered Feb 2, 2017 at 21:42

Avik Aghajanyan

1,03310 silver badges14 bronze badges

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

2 Comments

wwwroth Over a year ago

This is working for now but the only issue is those methods only run when you access the properties so if you call the property in the view technically you're running SQL while rendering the view.

Avik Aghajanyan Over a year ago

when you are adding protected $appends = [ 'reviews', 'recommendedRate', 'averageReviewRating' ]; it is appending without accessing to properties

online Thomas · Accepted Answer · 2017-02-03 10:57:35Z

0

The way to get the sum in Laravel Eloquent is using the Aggregate sum and for average avg https://laravel.com/docs/5.4/queries#aggregates

If you want to add a custom property to your model for that, you can use

class Product {
    function __construct() {
         $this->{'sum'} = DB::table('product')->sum();
         $this->{'avg'} = DB::table('product')->avg();
    }

}

edit: to set the attributes, you can use the built in function https://github.com/illuminate/database/blob/v4.2.17/Eloquent/Model.php#L2551

edited Feb 3, 2017 at 10:57

answered Feb 2, 2017 at 21:35

online Thomas

9,4887 gold badges52 silver badges94 bronze badges

2 Comments

wwwroth Over a year ago

Product is a model so you cannot dynamically declare properties in the constructor like that.

online Thomas Over a year ago

@wwwroth why not? I know pretty sure you can

Collectives™ on Stack Overflow

How to assign a dynamic property in a model via a SQL query in Laravel 5

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related