+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Libraries & Helpers (https://forum.codeigniter.com/forumdisplay.php?fid=22)
+--- Thread: Datamapper Sub queries wrapped in a function (/showthread.php?tid=55457)
Datamapper Sub queries wrapped in a function - El Forum - 10-27-2012
[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.
Datamapper Sub queries wrapped in a function - El Forum - 10-27-2012
[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')