Datamapper Sub queries wrapped in a function

[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.

[eluser]drakeonfire[/eluser]
I've found a solution. After reading the documentation more thoroughly I decided to just do something like this:

Code:

select_func('COALESCE', '[(SELECT AVG(`rating`) FROM `ratings` WHERE `ratings`.`product_id` = `products`.`id`)]', 'avg_rating')