Yii2 query give different result with sql query

Question

"Table1":

id	name
1	Ulrich
2	Stern

"Table2":

id	school	tid
1	A	1
2	B	1

I want to join 2 table to get all information. With SQL query like this:

SELECT Table1.id, 
       name, 
       school 
FROM       `Table1`
INNER JOIN `Table2`
        ON Table1.id = Table2.tid

It gives me all information as I expect (I mean 2 rows with name 'Ulrich').

But when I do with Yii2 query:

$query = self::find();
$query -> alias('t1')
       -> innerJoin(['t2'=>'Table2'], 't1.id=t2.tid')

$result = NULL;
if($total = $query->count()) {
    $result = $query
             -> select([t1.*, t2.school])
             ->asArray()
             ->all()
                ;
    $result[0]['count'] = $total;
}

it only gives me 1 row with name 'Ulirch'.

Can anyone help me with this problem. Thank you very much.

Michal Hynčica · Accepted Answer · 2022-08-08 13:37:56Z

1

If you use ActiveRecord::find() method to create query you will get instance of yii\db\ActiveQuery. This is query designed to load ActiveRecord models. Because of that if you do any type of join and your result set contains primary key of your main model (The model which find() method was called to create query) the ActiveQuery will remove any rows it considers duplicate.

The duplicates are recognised based on main model primary key so the rows in resultset will be considered duplicate even if the data from joined table are different. That's exactly what happened in your case.

To avoid that you have to use query builder instead of ActiveQuery. Your query can look for example like this:

$query = (new \yii\db\Query())
    ->from(['t1' => self::tableName()])
    ->innerJoin(['t2'=>'Table2'], 't1.id=t2.tid');

answered Aug 8, 2022 at 13:37

Michal Hynčica

6,2711 gold badge16 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stern Over a year ago

Thank you very much. This helped me with my problem

Collectives™ on Stack Overflow

Yii2 query give different result with sql query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related