Quinlanlent Quinlanlent - 3 months ago 33
MySQL Question

Joining tables Yii 2 - MariaDB exception

Hello I am using the latest version of xampp which has MariaDB and when I am trying to join tables in yii 2 it says that I have a MariaDB exception. See the screenshot below.
enter image description here

What I am trying to do is to join schead and scstock via TrNo, see the screen of the database tables.

SCHEAD
enter image description here

SCSTOCKenter image description here

I am still very new with JOINING so I would like help in trying to solve the exception.

Here is the code for my join tables.

public static function getFirstYearFirstTerm($currcode){
return Scstock::find()
->select('scstock.*')
->leftJoin('schead', ' `schead.TrNo = scstock.TrNo')
->where(['schead.terms' => '1ST', 'schead.styear' => 1])
->asArray()
->all();
}


This is located in the in the ActiveCurriculum.php with the
tableName
function.

public static function tableName()
{
// return '{{%ccsubject}}';
return '{{%schead}}';
}


But I think I am not using it in this transaction.

Answer

You have a typo in your quoting of the tables. The line

->leftJoin('schead', '  `schead.TrNo = scstock.TrNo')

should read

->leftJoin('schead', '  `schead`.`TrNo` = `scstock`.`TrNo`')

or

->leftJoin('schead', '  schead.TrNo = scstock.TrNo')

It's best to stick to one or the other i.e either you quote all table and column names or you quote none of them.


An even better option is to add schead as a relation in your Scstock model.

public function getSchead() {
    return $this->hasOne(Schead::className(), ['TrNo' => 'TrNo']);
}

This would reduce your query to:

return Scstock::find()
          ->select('scstock.*')
          ->joinWith('schead')
          ->where(['schead.terms' => '1ST', 'schead.styear' => 1])
          ->asArray()
          ->all();
}
Comments