findRecords using GROUP BY now breaks in Pods v1.9.3
The following worked fine in Pods version 1.9.1 but since upgrading to v1.9.3 I receive a SQL error
$where = "(tariffname NOT LIKE '%Broadband%') GROUP BY model"; $RcProducts->findRecords('name ASC', 99, $where);
I see that find records has been updated in v1.9.2 and 1.9.3 but can't see why the group by should now break. Thanks for any ideas!
For info the error is:
SQL failed; SQL: SELECT SQL_CALC_FOUND_ROWS DISTINCT t.*, p.id AS pod_id, p.created, p.modified FROM wp_pod p INNER JOIN wp\_pod\_tbl\_3products t ON t.id = p.tbl_row_id WHERE p.datatype = 11 AND ( (tariffname NOT LIKE '%Broadband%') GROUP BY model ) ORDER BY name ASC LIMIT 0,99; Response: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY model ) ORDER BY name ASC LI' at line 119595
edited Oct 2 '10 at 4:53
4 Answers
I see what you mean. I'll make a very special exception for you! In the very next bug fix release, we will add a GROUP BY parameter to the new $params array format. See the second paragraph in the user guide page for findRecords to see the format you'll want to use. In the next release, we'll add a 'groupby' option to that parameter which will function like you're wanting to. For now, take off the group by in your $where, run the findRecords, then output the $Record->sql to get the generated SQL -- then copy and paste that, go back to your findRecords code and place that SQL into the fourth parameter in the function itself $sql and append your GROUP BY there until the next release.
Alternatively, you can replace line 550 in /pods/classes/Pod.php to make your existing code work. Just be aware that when the next release is out, you'll need to adjust your code for the new functionality.
Replace line 550:
$where = empty($where) ? '' : " AND ( $where )";
With:
$where = empty($where) ? '' : " AND $where ";
edited Oct 3 '10 at 1:06
@sc0ttkclark - Thanks for those two tips, both worked a treat. I've gone with your first solution using the following SQL:
$sql = "SELECT SQL_CALC_FOUND_ROWS DISTINCT t.*, p.id AS pod_id, p.created, p.modified FROM @wp_pod p INNER JOIN `@wp_pod_tbl_products` t ON t.id = p.tbl_row_id WHERE p.datatype = 11 AND (tariffname NOT LIKE '%Broadband%') GROUP BY model ORDER BY name ASC LIMIT 0,99";
Along with:
$Rcproducts->findRecords('', '', '', $sql);
Bug fix release coming out soon, I've included the new 'groupby' parameter for you to utilize.
