calculate average of multiple fields / eloquent

I've seen you on the forum for quite some time, I would surely figure by now you can work some of these things out on your own.

You by now have the experience to realize that many of these queries weather eloquent or regular SQL can take some trial-and-error to workout.

@jlrdw

I'm unsure if it's possible using plain eloquent methods or if I just should loop through all products using collection methods in general.

See how this one was worked up

https://laracasts.com/discuss/channels/eloquent/orderby-computed-related-attribute?page=1

May give you some ideas.

But if the data isn't very large meaning hundreds of thousands you could probably Loop like you said.

There are many ways for example query a query.

Seems you're just totaling three counts then averaging is that correct.

Which you could just get with aggregate functions.

I have that functionality in the app I'm ever building and I've set it up with a books table and a score table, classic one to many relationship.

This is the code I use in my Book model

 public function score()
    {
        return $this->hasMany(Score::class);
    }


    public function getScoreAttribute()
    {
        return $this->score()->average('score');
    }

@tray2 / @jlrdw

Yes, but see my first post, I would need to build an average from multiple fields, not only one single field! Using avg() would be easy, but the feedback has three options, I would need an average from all of these. See my calculation above for details (shippingcan be nulled).

I'm unsure about how to use the basic SQL aggregate functions (sum / average) to calculate the rating "per feedback" and build a sum of them on the product base.

I'm sure it has to be something like:

$bestRated = $userProfile->confirmedProducts()->with(['allFeedbacks' => function ($query) {
            //do calculation here...

        }])->orderBy('feedbacks.XY','desc')->first();

Say you had this:

Spays and neuters over a period of time.

Query looks like:

SELECT sn_sub.spay_neuter, Sum(sn_sub.quantity) AS SumOfquantity, Sum(sn_sub.cu_amount) AS SumOfcu_amount
FROM spayneuter LEFT JOIN sn_sub ON spayneuter.id = sn_sub.pa_id
GROUP BY sn_sub.spay_neuter;

But you want average of all spays neuters combined.

You average the whole above query.

SELECT Sum(SumOfquantity/12) AS tsum
FROM (SELECT sn_sub.spay_neuter, Sum(sn_sub.quantity) AS SumOfquantity, Sum(sn_sub.cu_amount)
AS SumOfcu_amount FROM spayneuter LEFT JOIN sn_sub ON spayneuter.id=sn_sub.pa_id
GROUP BY sn_sub.spay_neuter)  AS myaverage;

Notice

I use same query, but do an entire average over the SumOfquantity

I divided by 12 for monthly average, just quick test data.

Results in

per month all combined

You can query a query in other words

I'll give another maybe better example:

If you had this:

Just a list of owners and count of their pets.

Query

SELECT dc_powners.ownerid, dc_powners.oname,
COUNT(dc_pets.petid)
AS CountOfpetid
FROM dc_powners LEFT JOIN dc_pets ON
dc_powners.ownerid = dc_pets.ownerid
GROUP BY dc_powners.ownerid
ORDER BY dc_powners.oname;

But you want the average number of pets per person.

It would be the

(sum of pets) divided by (number of owners)

So again, query a query.

SELECT  COUNT(ownerid) AS number_of_owners, SUM(CountOfpetid) AS total_pets,
(SUM(CountOfpetid) / COUNT(ownerid)) AS average_pet_per_person FROM
(SELECT dc_powners.ownerid, dc_powners.oname,
COUNT(dc_pets.petid)
AS CountOfpetid
FROM dc_powners LEFT JOIN dc_pets ON dc_powners.ownerid = dc_pets.ownerid
GROUP BY dc_powners.ownerid
ORDER BY dc_powners.oname) AS od;

It gives:

Take note of this part

SELECT  COUNT(ownerid) AS number_of_owners, SUM(CountOfpetid) AS total_pets,
(SUM(CountOfpetid) / COUNT(ownerid)) AS average_pet_per_person FROM

It is placed above original group by query. Again, all this does is "query a query" for greater detail.

Notice the math is done right in the query.

The data here is just test data I use.

@jlrdw

Can I use eloquent to get rid of the join? Like:

Auth::user()-> confirmedProducts()->with(['sub_category.category:id,slug', 'allFeedbacks'])->select(DB::raw(here goes the query...))

Or won't this work, since the with() statement won't be executed before (meaning select won't be able to find the feedbacks)?

I have no idea what

Auth::user()-> confirmedProducts()->with(['sub_category.category:id,slug', 'allFeedbacks'])->select(DB::raw(here goes the query...))

even is.

I know active record is only a shortcut just like linq to sql is a shortcut to sql server, ado.net.

I know that linq and active record at runtime has to convert to normal sql. So in the background it will do a join anyway.

I came from java, where I primarily did normal sql in jdbc, which is similar to pdo.

Why don't you want to just use normal sql. Active record isn't for everything.

Here is another good answer on that subject:

https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder

Ask yourself this: If it was such an easy shortcut language, how could there be so many questions on it.

Active record is powerful yes, but not for all situations.

Also I put example here which is an eloquent query:

https://laracasts.com/discuss/channels/eloquent/groupby-eloquent-query

But I have not tried to "eloquent query" an "eloquent query" yet to get average.

I am now curious..