OrderBy computed, related attribute

Look at https://laracasts.com/discuss/channels/eloquent/eloquent-relationships-still-not-understand
and
http://dsmithweb.com/itemized.png
and
http://laravel.io/forum/04-04-2015-looping-through-collection
There a $5000.00 tutorial for free.

@jlrdw not sure I understand. Is there a way to do this without direct SQL queries or Joins? I'd also love to keep the simple pagination features provided by Laravel out of the box.

You can still paginate a one-to-many with out of the box pagination. Take the intermediate tutorial that's in the documentation and you will learn more about relations. Real good tutorial that Taylor included.
But later if you are writing very complex stuff you really need to learn how to write a custom paginaor to handle it. The out-of-the-box paginator can have trouble with some complex joins or group bys.

@jlrdw thanks for your help. I've read the tutorial and have the relationships set up, the challenge is that the value I want to orderBy() is not in a database anywhere, it's a computed property on the model. I'm trying to see if there's a way to get it to determine the average rating within the query and then sort based on those values. Or if there's a way to pass a closure to the orderBy() function I could have it run there?

You're going to have to study up on using Group by and count, you may need to use the raw query. Very hard to look at the big picture without having the actual database data.

A previous answer I gave only another post.
I usually just write a regular sql query. For example, I recently had to write a checkbook / balancing program for a lady, she didn't like the running sum at bottom only, she wanted a running sum column. Taylor provides access directly to PDO, so it's Laravel:

public function getChecks($offset = "", $rowsperpage = "", $checksearch = "")
    {
        $checksearch = $checksearch . "%";
        $pagingQuery = " LIMIT $offset, $rowsperpage";
        $sql = "SELECT OD.checkid, OD.transdate, OD.transdescribe, OD.widthdraw, OD.deposit, OD.isclr,";
        $sql = $sql . " (SELECT (Sum(IFNULL(deposit, 0)) - Sum(IFNULL(widthdraw, 0))) FROM checks";
        $sql = $sql . " WHERE checkid<=OD.checkid) AS RunningSum";
        $sql = $sql . " FROM checks AS OD" . $pagingQuery;
        $sth = \Illuminate\Support\Facades\DB::connection()->getPdo()->prepare($sql);
        $sth->execute();
        $results = $sth->fetchAll(\PDO::FETCH_ASSOC);
        return $results;
    }

This is quick, to the point, fast, and uses what Taylor built it. A way to write a somewhat complex query quickly.
And the:

getPdo()->

Is directly from the laravel documentation.
So in your case you could simply grab your summaries (counts) and whatever in one query and your info to display paginated in another query. I'd write just a simple custom paginator with next previous, and use the ->with construct to pass to view.
The end of previous post.

So the point is you can construct a complex query you need and paginate it. In the long run yes it's harder to write a complex query but at the end this makes everything easier once you get it constructed. If you have thousands of rows of data, the normal eloquent relation query won't work without loading thousands of rows of data into an array which is not a good idea.
The above query is equivalent to Lazy loading only 5 database records at a time. Of course you could paginate more rows at a time.
I also searched and found which might help guide you https://blog.smalldo.gs/2011/03/mysql-select-avg-of-count/

You'll need to manually do the join, group by product id, and order by the average rating.

I believe the resulting SQL ought to look something like this:

SELECT *, AVG(ratings.rating) as average_rating
FROM products
JOIN ratings ON ratings.rateable_id = products.id AND ratings.rating_type = "product"
GROUP BY ratings.rateable_id
ORDER BY average_rating desc

@willvincent nice looking, but you can't order by an average. I had somthing similar, took a few minutes:

SELECT AVG(COALESCE(rating.rating, 0)) as average, rating.prodid 
FROM rating
LEFT JOIN product ON rating.prodid = product.id
GROUP BY product;

Will probably need group by, but good luck.

Hmm.. you certainly ought to be able to order by the average.. and the ability to do so is the entire point of this thread.

@willvincent you may be right but I haven't had a chance to try nested selects yet, but there has to be a way, it will probably be a complex looking query.

Shouldn't require a nested select. If ordering by the computed field 'average_rating' (as I indicated) does not work, then the order by should simply call AVG() on the joined data again. I haven't done anything like this in a while, but I know it used to be possible.

@willvincent tried again with my quick test data, works, I didn't think about order by after group by.

SELECT *, AVG(rating.rating) as average_rating
FROM product
JOIN rating ON rating.prodid = product.id
GROUP BY rating.prodid
ORDER BY average_rating desc;

I have some names wrong sorry, one thing:
I changed ratings.rateable_id to the key that's linked to products, in my quick tables I have (id from products) linked with (prodid in rating table), but works great.
Original poster with a little study should be able to paginate this data a few pages at a time.

Incidentally, if anyone can figure out a nice succinct way to add this to the package so that it's more straightforward to implement an order by average or sum of rated items, I'd welcome that as a pull request :)

These work great for querying only products with ratings, but not those that have yet to be rated. Here's what I have now to include unrated products, but then I end up losing the product->id from the LEFT JOIN:

        $apps = DB::table('products')
            ->select('*')
            ->leftJoin('ratings', 'products.id', '=', 'ratings.rateable_id')
            ->addSelect(DB::raw('AVG(ratings.rating) as average_rating'))
            ->groupBy('products.id')
            ->orderBy('average_rating', 'desc')
            ->paginate(session('posts_per_page'));

Another step I'm trying to do is convert the results back to Eloquent models (gives me the convenience of the attribute accessors and more). This is how I figure I'll do it, but without the ID's for all results it won't work:

       $products = collect($products);
        $products = $products->map(function($item) {
            return Product::with('ratings')->find($item->id);
        });

Don't worry about those eloquent models, those mutators are not needed unless you are changing data within the getter setter. Just do your query paginate your data and send it to The View. You are better off keeping it simple and not making it over complicated. I don't ever use eloquent, I use the query Builder and regular queries. The ORM converts to regular queries at runtime anyway. Don't get me wrong it's great for some stuff but not a combined table like you have that needs probably a custom paginator.
After all this is data already saved to the database that you need to view or perhaps print out a report. Don't forget Taylor included other techniques for handling data, probably for a good reason.

Thanks @jlrdw , good point. I'm still lacking a way to get the Product ids into the left joined table, any ideas? I feel like I'm almost there! :)

Include them in the field list.

Your left join shouldn't be omitting the ID field, it should just make the average rating null for unrated products. What's your result set look like?

It looks as you'd expect, all Product attributes plus the "average_rating" field. But when I left join on products.id = rateable.id the average_rating field is merged and set to null but because there is no corresponding products.id = rateable.id for unrated products the products.id becomes null.

0 => [
    'id' => 5,
    'name' => 'Product With Ratings'
    ...
    'average_rating' => 4.5
],
2 => [
    'id' => 2,      // OrderBy now working great so unrated products are at the bottom
    'name' => 'Other Product with Lower Rating',
    ...
    'average_rating' => 3
],
...
9 => [
    'id' => null        // I'm thinking because products.id is not found in ratings table as rateable.id so left join makes this null
    'name' => 'Unrated Product'
    'average_rating' => null
]

You need COALESCE, see my example.

@jakec729 Try a rightJoin instead of a leftJoin

@willvincent the COALESCE as in my example will still be needed.

Only if unrated items need to be cast to a zero value instead of null. I don't know that that's necessarily a requirement.

@willvincent I believe it's the other way around, there's already some products in the table but no rating, therefore that value is currently null. The COALESCE will just treat as 0 for the sake of the query. I don't think average works well with null values.

Right, there are products without ratings, in those cases, the rating should be null.. average of null would still be null, would it not?

@jakec729 Try this:

$apps = DB::table('products')
            ->select('products.*')
            ->leftJoin('ratings', 'products.id', '=', 'ratings.rateable_id')
            ->addSelect(DB::raw('AVG(ratings.rating) as average_rating'))
            ->groupBy('products.id')
            ->orderBy('average_rating', 'desc')
            ->paginate(session('posts_per_page'));

That might fix your issue of the id going null on you. I suspect what was happening was that it was getting overridden by the id in the ratings table -- in the case of an unrated product, null.

This small change to your otherwise working query explicitly says just grab product table fields and the average rating.. not everything from both.

@willvincent how did you place an image in the post there I've been curious about that.

@jlrdw markdown syntax for an image is pretty much the same as for a link..

![alt-text](image url) it's the preceeding exclamation point that dictates it's an image.

The image has to be hosted somewhere publicly accessible of course. imgur, flickr, et al.

Should you be rating null products? Or use a case statement and set null product.id ratings to 1 or 0 or some relevant value. Either way it will skew the average but not kill it.

@jimmck I think you're confused. The product isn't null.. the rating is null when a product has not yet been rated, that's almost certainly different than a -zero- rating. I guess it depends on whether or not you allow a rating of zero. NULL makes sense to me because that just says "this product has no ratings yet"

Of course if the lowest value rating you allow anyone to give is 1 then casting unrated products to zero would work too, but it seems rather unnecessary to me.

The specific problem of product IDs being null was not because those are actually null products, but that after the join, the id was being nulled out in the query result set. That's unexpected behavior. My last comment re: query structure should rectify that issue.