/** * generateSql * * @return string */ public function generateSql($mode = KVDdb_Criteria::MODE_FILLED, $dbType = KVDdb_Criteria::DB_MYSQL) { $sql = sprintf('SELECT%s %s FROM %s', $this->distinct ? ' DISTINCT' : '', implode($this->fields, ', '), $this->table); if ($this->hasJoins()) { foreach ($this->joins as $join) { $sql .= ' ' . $join->generateSql($mode, $dbType); } } $where = $this->criteria->generateSql($mode, $dbType); if ($where != '') { $sql .= ' ' . $where; } return $sql; }
public function testComplex() { $criteria = new KVDdb_Criteria(); $criteria->add(KVDdb_Criterion::equals('provincie_id', 20001)); $query = new KVDdb_SimpleQuery(array('gemeente_id'), 'gemeente', $criteria); $this->assertEquals($query->generateSql(), 'SELECT gemeente_id FROM gemeente WHERE ( provincie_id = 20001 )'); $criterion = KVDdb_Criterion::inSubselect('gemeente_id', $query); $this->assertEquals($criterion->generateSql(), '( gemeente_id IN ( SELECT gemeente_id FROM gemeente WHERE ( provincie_id = 20001 ) ) )'); $criteria2 = new KVDdb_Criteria(); $criteria2->add($criterion); $this->assertEquals($criteria2->generateSql(), 'WHERE ( gemeente_id IN ( SELECT gemeente_id FROM gemeente WHERE ( provincie_id = 20001 ) ) )'); $this->assertFalse($query->hasJoins()); }
/** * findByCriteria * * @param KVDdb_Criteria $c * @return KVDdom_DomainObjectCollection */ public function findByCriteria(KVDdb_Criteria $c) { if ($c->count() == 0) { return $this->findAll(); } // criterion toevoegen dat enkel termen uit deze thesaurus toont. $c->add(KVDdb_Criterion::equals('thesaurus_id', $this->parameters['thesaurus_id'])); $c->addAscendingOrder('term'); $c->addAscendingOrder('qualifier'); $sql = sprintf('SELECT t.id, term, tt.id AS type_id, tt.name AS type_naam, qualifier, language, sort_key FROM %s.term t LEFT JOIN %s.term_type_code tt ON ( t.type = tt.id )', $this->parameters['schema'], $this->parameters['schema']); $sql .= $c->generateSql(KVDdb_Criteria::MODE_PARAMETERIZED); $stmt = $this->conn->prepare($sql); $values = $c->getValues(); for ($i = 0; $i < count($values); $i++) { $stmt->bindValue($i + 1, $values[$i]); } $stmt->execute(); $termen = array(); while ($row = $stmt->fetch(PDO::FETCH_OBJ)) { $termen[$row->id] = $this->doLoad($row->id, $row); } return new KVDdom_DomainObjectCollection($termen); }
/** * findByCriteria * * @since 30 okt 2007 * @param KVDdb_Criteria $criteria * @return void */ public function findByCriteria(KVDdb_Criteria $criteria) { $sql = $this->getSelectStatement() . ' ' . $criteria->generateSql(KVDdb_Criteria::MODE_PARAMETERIZED); $stmt = $this->_conn->prepare($sql); $values = $criteria->getValues(); for ($i = 0; $i < count($values); $i++) { $stmt->bindValue($i + 1, $values[$i]); } return $this->executeFindMany($stmt); }