Active Records - Queries naar DB

Active Records

select

$examen = examen::find()->where(['actief' => '1'])->orderBy(['datum_van' => 'SORT_DESC'])->one();

$examen = examen::find()->where(['actief' => '1'])->orderBy(['datum_van' => 'SORT_DESC'])->all();

$gesprekSoort = gesprekSoort::find()->all();

$gesprekSoort = examenGesprekSoort::findAll(['examen_id' => $id]);

$gesprekken = gesprekSoort::find()->where([])->joinWith('examenGesprekSoorts')->all();

// only two columns and return array (instead of object).
$criterium = Criterium::find()->select('id, omschrijving')
     ->where([ 'werkprocesid' => $model->form->werkproces ])->asArray()->all();

// zoek alle distinct studenten op naam en gebruik like %
$resultaat = resultaat::find()->select(['student_nummer','student_naam'])->distinct()
     ->where(['like', 'student_naam', $model->student_naam])->orderBy(['student_naam' => 'SORT_ASC'])->all();

// andWhere (na de where plaatsen)
->andWhere(['<','Bedrag', 100000])->

Insert update delete

// INSERT (table name, column values)
Yii::$app->db->createCommand()->insert('user', [
    'name' => 'Sam',
    'age' => 30,
])->execute();

// INSERT via raw Query
$sql="insert into examen_gesprek_soort (examen_id, gesprek_soort_id) values(:examenid, :gesprekid)";
$params = array(':examenid'=> $id, ':gesprekid' => $value );
$query->createCommand()->sql;
$result = Yii::$app->db->createCommand($sql)->bindValues($params)->execute();

// debug query 
$query = new Books::find()->where('author=2');
 echo $query->createCommand()->sql;

// UPDATE (table name, column values, condition)
Yii::$app->db->createCommand()->update('user', ['status' => 1], 'age > 30')->execute();

// DELETE (table name, condition)
Yii::$app->db->createCommand()->delete('user', 'status = 0')->execute();

$sql="delete from examen_gesprek_soort where examen_id = :examenid";
$params = array(':examenid'=> $id);
$result = Yii::$app->db->createCommand($sql)->bindValues($params)->execute();

$sql="select * from student";
$result = Yii::$app->db->createCommand($sql)->queryAll(); //execute() returned only a count