/**
* 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);
}
