@willvincent just passing through... Same idea null whatever give the rating some default value. Since left or right anything brings in nulls. Not sure why someone would average something not there.

The average is for the things that are there. Since there will inevitably be products not yet rated, there will be average_rating values of null. The only way to avoid that is to cast the output of AVG() to a different value. Which COALESCE will do, but why? What is the point? One can just as easily display something on the frontend indicating the product is not yet rated whether the response is NULL or 0 or -500.. why add the extra operation in if it's not necessary?

@willvincent my example

SELECT AVG(COALESCE(rating.rating, 0))

Just uses 0 instead of null in the query, I still believe somehow null messes things up in this type of query, I've actually got erroneous results before. Seems to only affect complex type query. But again I am not 100% sure doesn't hurt to use it in the query. It does work.

Right, it probably doesn't hurt. I'm simply arguing that it is probably unnecessary :)

@willvincent your solution totally worked! A new hoop to jump through: I can't figure out how to add another select to get the product's rating by the Auth::user(). To the end user, they'll see a paginated, sortable table of products with the (sortable) column headings: "Name", "Price", "Your Rating", "Average Rating".

Any help appreciated!

@jakec729 I don't know that you can accomplish that in a single query, because to pull the average you have to select the computed value of that field. In order to get the rating the current user provided you'd need a where condition that would break the average rating query.

So, your best option is probably to run a second query that fetches all the current user's ratings, then on the frontend if there is an entry in the resultset of current user's ratings for a given product, display it, otherwise display your 'not yet rated' indicator.

To make that easier to handle on the front end you may want to loop through the results in the controller and build your own array of data to pass off to the view... otherwise, regardless if that logic is in the controller or the view, you'll need to look at the results of the second query for each iteration through the average ratings result set, see if the product id is present and if so display the associated rating, else nothing/not rated indication. That logic feels like it belongs within the controller to me.

That second query should be pretty basic though, something like this ought to work:

$user_ratings = DB::table('ratings')
                  ->select('rateable_id as product_id', 'user_id', 'rating')
                  ->where('user_id', '=', Auth::user()->id)
                  ->where('rateable_type', '=', 'Product')
                  ->get();

You could also include a whereIn that limits the results to only those product ids in your first paginated query.

Anyway, I can't think of a way you could really manage this with a single query.