/** Return unsatisfied prerequsites. * * Note: code is an sql expression. */ static function query_prerequisites($code, $completed, $taking = [], $values = []) { global $db; // Convert all the courses to strings. foreach ($completed as &$c) { if (is_a($c, 'Course')) { $c = $c->getcode(); } } foreach ($taking as &$c) { if (is_a($c, 'Course')) { $c = $c->getcode(); } } // Warning: Dragons Ahead! This is a complex query. With a lot // of parts. ///// Inner query. // This inner query counts how many courses in $completed are // in the elgible group and not in the excluded group. $haspreq = new Query('coursegroups elg'); $haspreq->select('count(elg.id)'); $haspreq->where('elg.id = prerequisites.eligible'); $haspreq->join('coursegroup_courses elgc', 'elgc.id = elg.id'); // Where the course is in the completed group. // Or it can be taken concurrently and it is in the taking group. $haspreq->where('( elgc.course_code IN ' . Query::valuelistsql($completed) . ' OR ( elgc.course_code IN ' . Query::valuelistsql($taking) . ' AND elgc.concurrent ) )', array_merge($completed, $taking)); // And it is not one of the courses that is excluded. $haspreq->where('NOT EXISTS ( SELECT course_code FROM coursegroup_courses WHERE coursegroup_courses.id = prerequisites.excluded AND coursegroup_courses.course_code = elgc.course_code )', []); ///// Outer Query // This query selects prerequsites where the number of credits // earned is less then the required credits. $q = new Query('prerequisites'); $q->select('course_code'); $q->select('eligible'); $q->select('excluded'); $q->where('course_code = ' . $code, $values); $q->where('(' . $haspreq->sql() . ') < credits', $haspreq->values()); return $q; }
public function testInsertStatement_AddValues_Array() { $query = new Query("INSERT INTO `test` VALUES (NULL, 'abc', 10)"); $query->values(array(null, 'xyz', 12)); $this->assertEquals("INSERT INTO `test` VALUES (NULL, 'abc', 10), (DEFAULT, \"xyz\", 12)", (string) $query); }