FindRecords help please :(
When I try filter search a pod by 2 pick columns I am getting the following SQL error. Any help would be really appreciated.
This is in a WP template
<?php
$business = new Pod('business');
$business->findRecords('t.name ASC', 10, "t.name LIKE '%$result%' OR t.details_desc LIKE '%$result%'");
echo $business->getFilters($filters = 'category, sub-category', 'Search', $label = 'business-list/');
$total_members = $business->getTotalRows();
?>
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 LEFT JOIN wp_pod_rel r1 ON r1.field_id = 10 AND r1.pod_id = p.id LEFT JOIN `wp_pod_tbl_business_category` `category` ON `category`.id = r1.tbl_row_id LEFT JOIN wp_pod_rel r2 ON r2.field_id = 11 AND r2.pod_id = p.id LEFT JOIN `wp_pod_tbl_business_subcategory` `sub-category` ON `sub-category`.id = r2.tbl_row_id INNER JOIN `wp_pod_tbl_business` t ON t.id = p.tbl_row_id WHERE p.datatype = 3 AND `category`.id = '13' AND `sub-category`.id = '616' ORDER BY name ASC LIMIT 0,10; Response: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay
With just one in it works fine
echo $business->getFilters($filters = 'category', 'Search', $label = 'business-list/');
Thanks Alan
edited Jul 21 '11 at 4:55
3 Answers
It looks like your hosting provider has your default SQL_MAX_JOIN_SIZE set pretty low. Here is something I found that might get you going again.
There might be an underlying Pods issue here based on this thread I found on Stack Overflow. Matt is one of the original developers of Pods.
edited Jul 21 '11 at 5:52
Thank you Chris, I'll take a look. I have 20 categories and 230 sub-categories relating to those categories. Then about 1500 pod items each with a category and subcategory. Maybe the query is just becoming too large.
edited Jul 22 '11 at 8:57
I'm not seeing why you would get that error with such a simple query but perhaps @chris.pilko is right here, try to get that enabled on your server and see if that helps.
edited Jul 24 '11 at 9:47


