Yii Framework Forum: LEFT JOIN relations - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

LEFT JOIN relations

#1 User is offline   Shile 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 5
  • Joined: 19-September 17

Posted 19 September 2017 - 11:39 AM

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

0

#2 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 3,309
  • Joined: 16-February 11
  • Location:Japan

Posted 19 September 2017 - 11:35 PM

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.yiiframew...ery.html#where()-detail
1

#3 User is offline   Shile 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 5
  • Joined: 19-September 17

Posted 20 September 2017 - 10:00 AM

View Postsoftark, on 19 September 2017 - 11:35 PM, said:

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.yiiframew...ery.html#where()-detail


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.
0

#4 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 3,309
  • Joined: 16-February 11
  • Location:Japan

Posted 20 September 2017 - 05:14 PM

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]
0

#5 User is offline   Shile 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 5
  • Joined: 19-September 17

Posted 21 September 2017 - 07:58 AM

View Postsoftark, on 20 September 2017 - 05:14 PM, said:

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]


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

#6 User is offline   Shile 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 5
  • Joined: 19-September 17

Posted 21 September 2017 - 08:28 AM

Solved, thank you for your time and help @softark
0

#7 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 3,309
  • Joined: 16-February 11
  • Location:Japan

Posted 21 September 2017 - 08:41 AM

@Shile That's fine. :)
Would you please share your solution with us? I'm interested.
0

#8 User is offline   Shile 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 5
  • Joined: 19-September 17

Posted 21 September 2017 - 10:14 AM

View Postsoftark, on 21 September 2017 - 08:41 AM, said:

@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

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users