[eluser]drakeonfire[/eluser]
Hello all,
I'm trying to order items by their rating (highest rated first). In raw SQL I'd do something like:
Code:
COALESCE((SELECT AVG(`rating`) FROM `ratings` WHERE `ratings`.`product_id` = `product`.`id`), 0) AS `avg_rating`
However, as I'm using Datamapper I'm trying to do it with the available functions, so far I have something like:
Code:
$ratings = new Rating();
$ratings->select_func('AVG', '@rating', 'rating')->where_related('product', 'id', '${parent}.id');
$products->select_subquery($ratings, 'avg_rating') ...
This is OK, except for I don't know how to wrap COALESCE around the subquery in datamapper.
So basically, I'm stuck on wrapping COALESCE around a subquery in datamapper in the SELECT part of the statement.
Thank you.