LEFT JOIN relations

Yii 2.0 General Discussions
1

Cannot get a solution for my problem.

MainTableSearch.php


class MainTableSearch extends MainTable

{

    public $table_two;

    public $table_three


    public function search($params)

    {

        $query = MainTable::find();


        $query->joinWith(['table_two']);

        $query->joinWith(['table_three']);


        $dataProvider = new ActiveDataProvider([

            'query' => $query,

        ]);

....

MainTable.php


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:


$query->leftJoin('table_two', 

'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
2

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()

http://www.yiiframework.com/doc-2.0/yii-db-query.html#where()-detail

3

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.

4

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

[edit]

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?

[/edit]

5

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’

6

Solved, thank you for your time and help @softark

7

@Shile That’s fine. :)

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

8

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