LEFT JOIN relations

Cannot get a solution for my problem.


class MainTableSearch extends MainTable


    public $table_two;

    public $table_three

    public function search($params)


        $query = MainTable::find();



        $dataProvider = new ActiveDataProvider([

            'query' => $query,




class MainTable extends \yii\db\ActiveRecord


    public static function tableName()


        return 'main_table';


    public function getTableTwo()


        return $this->hasOne(TableTwo::className(), ['main_id' => 'id']);


    public function getTableThree()


        return $this->hasOne(TableThree::className(), ['main_id' => 'id']);



I want to see all data from main table in grid view and if table_two.something or table_three.something doesn’t meet condition to return null in that field.

I tried:

public function getTableTwo()


        return $this->hasOne(TableTwo::className(), ['main_id' => 'id'])

             ->andWhere(['table_two.something' => 1])

             ->orWhere(['table_two.something' => NULL);


but that will just remove rows that doesn’t have correlations between main_table and table_two, also tried:


'main_table.id = table_two.home_id AND 

(table_two.something=1 OR table_two.something = NULL)');

with no luck.

To help you understand this sql works fine:

SELECT * FROM `main_table` 

LEFT JOIN table_two ON main_table.id = table_two.home_id 

AND (table_two.something=1 OR table_two.something=NULL) 

LEFT JOIN table_three ON main_table.id = table_three.home_id 

AND (table_three.something=1 OR table_three.something=NULL) 

WHERE main_table.user_id = 1

Would you please try the following?

public function getTableTwo()


        return $this->hasOne(TableTwo::className(), ['main_id' => 'id'])

             ->andWhere('or', ['table_two.something' => 1], ['table_two.something' => NULL]);


API > yii\db\Query::where()


Tried, that will just remove data row from main_table that doesn’t have relation with table_two… I need all data’s from main table.

Probably you have to use "with" instead of "joinWith".


When you use ‘joinWith’ in the ‘search’ method, it will produce the query that uses ‘JOIN’. And if you set some conditions based on the joined tables, something like ‘table_two.other = xxx’, it may give a result set of records that matches the criteria.

Would you please show us your ‘search’ method as a whole?


Yes that solved that problem, but now search and ordering doesn’t work:

$dataProvider->sort->attributes['tableTwo'] = [

    'asc' => ['table_two.number' => SORT_ASC],

    'desc' => ['table_two.number' => SORT_DESC],


SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘table_two.number’ in ‘order clause’

Solved, thank you for your time and help @softark

@Shile That’s fine. :)

Would you please share your solution with us? I’m interested.

Instead joinWith used leftJoin like this:

$query->leftJoin('table_two','main_table.id = table_two.main_id AND (table_two.something=1 OR table_two.something IS NULL)');

I could swear I tried it and it did not work as I expected :)

1 Like