public static function getAllCourses($constraints = array()) { $select['main'] = 'c.id'; $select['has_instances'] = ""; //Must be here, even if empty $select['num_lessons'] = "(select count( * ) from lessons_to_courses cl, lessons l where cl.courses_ID=c.id and l.archive=0 and l.id=cl.lessons_ID) as num_lessons"; $select['num_students'] = EfrontCourse::setCourseUserSelection($constraints); if (G_VERSIONTYPE == 'enterprise') { #cpp#ifdef ENTERPRISE $select['num_skills'] = "(select count( * ) from module_hcd_course_offers_skill s where courses_ID=c.id) as num_skills"; $select['location'] = "(select b.name from module_hcd_branch b, module_hcd_course_to_branch cb where cb.branches_ID=b.branch_ID and cb.courses_ID=c.id limit 1) as location"; } #cpp#endif $select = EfrontCourse::convertCourseConstraintsToRequiredFields($constraints, $select); list($where, $limit, $orderby) = EfrontCourse::convertCourseConstraintsToSqlParameters($constraints); //$result = eF_getTableData("courses c", $select, implode(" and ", $where), $orderby, false, $limit); //WITH THIS NEW QUERY, WE GET THE SLOW 'has_instances' PROPERTY AFTER FILTERING $tables = "courses c"; $from = array("courses.*", "t.*"); if (in_array('has_instances', array_keys($select))) { unset($select['has_instances']); $from[] = "(select count(id) from courses c1 where c1.instance_source=courses.id and c1.archive=0) as has_instances"; $from[] = "(select count(id) from courses c1 where c1.instance_source=courses.id and c1.archive=0 and c1.active=1 and c1.show_catalog=1) as has_instances_show_in_catalog"; } if (isset($constraints['branch_url']) && $_SESSION['s_current_branch']) { $tables .= ' LEFT OUTER JOIN module_hcd_course_to_branch cb on cb.courses_ID=c.id'; } $sql = prepareGetTableData($tables, implode(",", $select), implode(" and ", $where), $orderby, false, $limit); $result = eF_getTableData("courses, ({$sql}) t", implode(",", $from), "courses.id=t.id"); if (!isset($constraints['return_objects']) || $constraints['return_objects'] == true) { return EfrontCourse::convertDatabaseResultToCourseObjects($result); } else { return EfrontCourse::convertDatabaseResultToCourseArray($result); } }